Help Me please - Code is attached

[DJ_DEF_CON_1]

Good day,

The following code is filling in my text boxes with 12/01/1898 and 12/31/1898. It is only supposed to give me, when I select the month from my combo box the dates of the month from last year. Does any one know how to re-work the code.

Thanks

Private Sub Combo2_AfterUpdate()
Dim aDate As Date
If Me.Combo2 > Month(Date) Then
'now the year needs to be corrected
Me.START_DATE = DateSerial(Year(aDate) - 1, Month(aDate), "01")
'the "00" will force the month's last day, but the month must be set to +1 !
Me.END_DATE = DateSerial(Year(aDate) - 1, Month(aDate) + 1, "00")
Else
Me.START_DATE = DateSerial(Year(aDate), Month(aDate), "01")
'the "00" will force the month's last day, but the month must be set to +1 !
Me.END_DATE = DateSerial(Year(aDate), Month(aDate) + 1, "00")
End If

End Sub

 

[Fornatian]

If this is related to the other post you made in the tables forum I have been working on it think I have cracked it - famous last words.

On my sample form I have three objects - a combo box and two text boxes named cboMonths, txtStartOfMonth and txtEndOfMonth respectively.

The row source for the combo box is set to:

January;February;March;April;May;June;July;August;September;October;November;December

Private Sub cboMonths_AfterUpdate()
Dim aDate As Date
Dim AddMonths As Byte
Dim d As Date


'add two months and take a year off to give the right start
aDate = DateSerial(Year(Date) - 1, Month(Date) + 2, Day(Date))

If Month(Date) = 12 And Me.cboMonths = "January" Then

    AddMonths = 13

Else
    Do Until Format(DateAdd("m", AddMonths, aDate), "mmmm") = Me.cboMonths
    AddMonths = AddMonths + 1
    Loop

End If

Me.txtEndOfMonth = EndOfMonth(DateAdd("m", AddMonths, aDate))
Me.txtStartOfMonth = StartOfMonth(Me.txtEndOfMonth)

End Sub

You will need these functions in a general module.

Function EndOfMonth(d As Variant) As Variant
'
' Returns the date representing the last day of the current month.
'
' Arguments:
' D            = Date
  
  EndOfMonth = DateSerial(Year(d), Month(d) + 1, 0)
  
End Function

Function StartOfMonth(d As Variant) As Variant
'
' Returns the date representing the first day of the current month.
'
' Arguments:
' D            = Date
  
  StartOfMonth = DateSerial(Year(d), Month(d), 1)
  
End Function

The above event procedure when triggered today provides end of month dates for the clicked month for ten months previous, the current month and one month in the future ONLY.

I believe this is what you needed?

 

[DJ_DEF_CON_1]

THANK YOU!!!!

I would like to personally thank you Ian for all your help.

I have just started using Access in the last year or so on a very part time basis. This forum is very very helpful, and for you to spend some time helping us "Virgins" out is unbelievable.

I find my frustration level getting high some times and this area of the internet has calmed me down.

When I saw your code solve my challenge, I could've flown across the pond and bought you a lager. I, however, cannot afford to. (Fly that is, us Canadians always have money for beer.)

Again, I cannot thank you enough.

Kyle Deluca

 

[Fornatian]

Always welcome - quite a tricky one, but that's what intrigued me in the first place.