[SOLVED] Need form to gotorecord =Date() on load

Need form to gotorecord =Date() on load

I have a table labeled 'RouteCard_A' with the primary key "ID" then I have a field named "AMaidDate" set to date/time, format long date, required yes, indexed yes (no duplicates). I have a form labeled 'RouteCard_A_Form'.

I have been trying for weeks now to default my form to open on load to the record where "AMaidDate" matches today's date [=Date()].

It is currently set as:

Private Sub Form_Load()
 DoCmd.RunCommand acCmdRecordsGoToLast
End Sub

I have tried combinations with Dim as string, dim as date, docmd.openreport, and docmd.gotorecord with no luck (I'm probably using them wrong)

I'm thinking I need to set AMaidDate to a secondary primary key and/or make the AMaidDate match the ID field so I can use something like:

Private Sub Form_Load()
Dim stToday as date
Dim stOpenRec as string
stToday = date
stOpenRec = (I need a way to get the ID from the AMaidDate that matches today)

DoCmd.GoToRecord acDataForm, "AMaidDate", acGoTo, stOpenRec
End sub

Please help me complete this.

Thanks

Solution

This should work:

Me!AMaidDate.SetFocus
DoCmd.FindRecord Date