I'm trying to populate a field on a form using the Calendar Control 9 object, which i've got on a separate form. The calendar pops up when the user presses a button on the main form. What I need to do is either only extract the month/year from the date chosen, or freese the day to be the last day of the month for each month on the calendar. Is this possible?

Hi,

   If you are using a pop up form from the main form, then the date needs to be in a seperate table that looks up to the main table/form for the PK. Then store this date in a seperate table associated to the main table.

   You can also put the calendar control on the main form if you want to store the date in the main table.

   Use the control's properties dialog box to associate the date with the proper field.

   Then to set the date to default to the last day of the current month, you would have to set a default value in the field property in the table, or on the form control.

   I wrote this function that gets you the last day of the month, but I am not sure where to put it to get it to work.

'====================================
Function EndMonth()

Dim strDate As Date
Dim strNxMonth As Date
Dim strEndMonth As Date

strDate = Date
strNxMonth = DateAdd("m", 1, strDate)
strEndMonth = strNxMonth - DatePart("d", strNxMonth)

EndMonth = strEndMonth
End Function
'==============================================

Dang, that's the problem with VBA. I can do this stuff in VBScript all day, but I am not sure where it goes in VBA to get it store the value in the table. The field's default value property won't recognize the function.

I hope this gets you started. Please let me know if and how you resolve this.


mmcdonal

Here's a real sledgehammer approach to your problem.

Suppose your calendar is called CalendarX on Form1
and you want to populate Textbox called Text1 on Form2

When you change the date on the calendar, click back in the textbox on form2 and in the textbox's OnClick Event put

Sub Text1_ONCLick()

dim newdate as date
dim m as integer, y as integer, d as integer

newdate = Forms!Form1!CalendarX.value
y = year(newdate)
m = month(newdate)
d = day(newdate) '------- not really needed

me.text1.value = dateserial(y,m+1,0)
me.repaint

end sub

This will give you the last date of the month you have chosen as day 0 of month +1 is the last day of the previous month. You can format Text1 to display the date in any format you like e.g. "mm/yyyy" will only display month and year as 10/2004

Hope this helps