Open a form on a record with the current month Access

Question

I have been looking around and messing about with it for a bit but I can not work out how to make an MS Access form open to a record with the current month.

What I want to happen is that when the form is opened the user is sent straight to the current month of records, but without filtering only the current month.

Can anyone help? This is beyond me.

Answer 1

One approach uses SearchForRecord method:

Private Sub Form_Open(Cancel As Integer)
DoCmd.SearchForRecord , , acFirst, "Year([YourDateField]) = Year(Date()) And 
Month([YourDateField]) = Month(Date())"
End Sub

Alternate criteria structures:

DoCmd.SearchForRecord , , acFirst, "Val(Year([YourDateField]) & Month([YourDateField])) = Val(Year(Date()) & Month(Date()))"

DoCmd.SearchForRecord , , acFirst, "Val(Format([YourDateField], 'yyyymm')) = Val(Format(Date(), 'yyyymm'))"