How to open a Form to a record based on Today's Date?

[Guest]

I have a Timesheet that consists of a list of activities for a single day.
I'd like to open the timesheet from another form, so that the sheet opens to
today's date. Currently, the timesheet opens to the first date, and I have
to scroll to the current date. I still want the ability to scan backwards to
previous dates. Is this possible?

 

[Dirk Goldgar]

I have a Timesheet that consists of a list of activities for a single
day. I'd like to open the timesheet from another form, so that the
sheet opens to today's date. Currently, the timesheet opens to the
first date, and I have to scroll to the current date. I still want
the ability to scan backwards to previous dates. Is this possible?

Something along the lines of:

'----- start of example code -----
Dim frm As Access.Form
Dim strToday As String

strToday = Format(Date, "\#mm/dd/yyyy\#")

DoCmd.OpenForm "TimeSheet"

Set frm = Forms("TimeSheet")
With frm.RecordsetClone
.FindFirst "WorkDate = " & strToday
' If no work today, find latest work.
If .NoMatch Then
.FindLast "WorkDate < " & strToday
End If
If .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With
Set frm = Nothing
'----- end of example code -----

 

[Guest]

Dirk,
Thank you. I have not learned how to write code as you have listed, but this
will be a good opportunity to learn. This will give me an idea what commands
to start with.

 

[Dirk Goldgar]

Dirk,
Thank you. I have not learned how to write code as you have listed,
but this will be a good opportunity to learn. This will give me an
idea what commands to start with.

Great! That code would go in the Click event procedure for the button
that opens the TimeSheet form. In the code, you'll need to substitute
the real values for these names that I guessed at:

TimeSheet - the name of the timesheet form you want to open

WorkDate - the name of the field, in that form's recordsource
table, that holds the date of work

Note the following:

1. If either of those names contains a space or other nonstandard
character, you'll have to surround the name with square brackets: the
characters [ and ].

2. If the WorkDate field actually contains both date and time (as it
will if you set its value to the result of the Now() function), the code
will have to be adapted somewhat to allow for that. Post a reply here
if that's the case, and I'll help you with the adaptation.

 

[Guest]

Dirk,
Thanks for the explanation. I'm going to start with the structure of how to
create a module, and the definitions of the terms & commands you listed in
your response. The timesheet date uses the "=date()" function and not the
"=now()" function. It sounds like I will want to keep it that way for
simplicity. I'll check back with you if I have any problems. Thanks again.

Dirk,
Thank you. I have not learned how to write code as you have listed,
but this will be a good opportunity to learn. This will give me an
idea what commands to start with.

Great! That code would go in the Click event procedure for the button
that opens the TimeSheet form. In the code, you'll need to substitute
the real values for these names that I guessed at:

TimeSheet - the name of the timesheet form you want to open

WorkDate - the name of the field, in that form's recordsource
table, that holds the date of work

Note the following:

1. If either of those names contains a space or other nonstandard
character, you'll have to surround the name with square brackets: the
characters [ and ].

2. If the WorkDate field actually contains both date and time (as it
will if you set its value to the result of the Now() function), the code
will have to be adapted somewhat to allow for that. Post a reply here
if that's the case, and I'll help you with the adaptation.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)