Go to record where date field = today

[Alan Forman]

Hi Guys

I've got a subform (frm_Date_Worked) with lots of records with consecutive dates in one field [Date_Worked]. I need a button on the subform that will go to the record with today's date.

I've been looking at 'DoCmd.GoToRecord', but I can't get it right.

Thanks for your help.

Alan

 

[vbaInet]

You can use the FindFirst method of a recordset. Here's how:

Dim rs as DAO.recordset

Set rs = Me.RecordsetClone
rs.FindFirst "[DateField] = " & Date()

If not rs.NoMatch Then
     Me.Bookmark = rs.Bookmark
end if

 

[Alan Forman]

Hi

Thanks for your reply, I've put the code into the 'On Click' event of a button and changed the [DateField] with my field name [Date_Worked] but when I click it doesn't move to today in my records.

Thanks for your help

Alan

 

[vbaInet]

Are you sure there's a record with today's date on there? And is the button on your subform?

Sustitute:

rs.FindFirst "[DateField] = #" & Date() & "#"

 

[Alan Forman]

No luck, if it's a clue, if I do a normal 'Find' on the date field with 06/10/10 the record is found, if I put date() in the same 'Find' it reports it's not found.

Thanks again

 

[vbaInet]

What about:

rs.FindFirst "[DateField] = #6/10/10#"

Or

rs.FindFirst "[DateField] = " & "6/10/10"

 

[Alan Forman]

I've obviously got something wrong because now when I do a normal 'Find' it doesn't find the record.

I did substitute the code, but no luck.

Thanks

 

[Alan Forman]

Another clue, if I find 06/10/10 it won't find the record, but if i change it to 06/10/2010 it does

 

[vbaInet]

The Date function should return a date in that format but just for brevity:

rs.FindFirst "[DateField] = " & Format(Date(), "dd/mm/yyyy")

 

[Alan Forman]

No Luck, Im doing something wrong, the code under the button ......

Private Sub Find_Today_Click()

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

rs.FindFirst "[Find_Date] = " & Format(Date, "dd/mm/yyyy")

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

End Sub


Where [Find_Date] is the field with the date in ##/##/#### format

Is that correct ?

 

[vbaInet]

Post a stripped version of your db and I'll take a quick look.

 

[Alan Forman]

Thanks for this, there's no confidential data here. If you use the 4th record in the list (JOHN SIMPER) click the button at the end to get the full details.

Then the sub form at the bottom is what i'm working on.

Thanks again

Alan


Bummer, now the 'attachment' button doesn't work. I'll re-log in and try.

 

[Alan Forman]

Nope, can't attach the file.

 

[Alan Forman]

IE8 problem, used firefox

 

[Alan Forman]

I'll hide the pink [Find_Date] field when it's working, thanks for your time

 

[vbaInet]

It finally came through. I will have a look in a few minutes.

 

[boblarson]

My guess (without looking at the database) is that this line:

rs.FindFirst "[Find_Date] = " & Format(Date, "dd/mm/yyyy")

might need to be this:

rs.FindFirst "[Find_Date] = #" & Format(Date, "dd/mm/yyyy") & "#"

 

[Alan Forman]

Thanks Bob, gave it a try, no luck.

 

[vbaInet]

Right, just downloaded your db. Which form should I be looking at?

 

[vbaInet]

I figured out which form myself

This line should work:

rs.FindFirst "[Find_Date] = #" & Format(Date, "mm/dd/yyyy") & "#"