Display "Today's Date" programmatically

[Antonio]

Is there a way to enter code into a forms "on open" event that will display a
record whose "Date" field maches the current date?
I have a table with several records covering the currrent year. I want to
build a form that will act as a "dashboard" that, when opened, will display
the current days information based on what has been entered up to that point.
Right now, the form opens on the last record in the table (December 31,
2005).

Any help would be greatly appreciated.

Thank you in advance.

 

[Dirk Goldgar]

Is there a way to enter code into a forms "on open" event that will
display a record whose "Date" field maches the current date?
I have a table with several records covering the currrent year. I
want to build a form that will act as a "dashboard" that, when
opened, will display the current days information based on what has
been entered up to that point. Right now, the form opens on the last
record in the table (December 31, 2005).

Any help would be greatly appreciated.

Thank you in advance.

Do want to show *only* the record that matches today's date, with no
other records on the form? If so, then you can base the form on a query
that selects that record; something like

SELECT * FROM MyTable
WHERE MyTable.Date = Date();

Note: I *think* that should correctly distinguish between a field named
"Date" and the Date() function. However, I strongly urge you not to use
"Date" or any other reserved word as the name of a field or other
object.

 

[Antonio]

Dirk, thanks for the reply. Actually, no, I want the form to open on the
record that contains todays date, but allow the user to scroll left or right
(to yesterday or tomorrow). Basically, I want it to show all records, but
when it opens to display today's record.

 

[Dirk Goldgar]

Dirk, thanks for the reply. Actually, no, I want the form to open on
the record that contains todays date, but allow the user to scroll
left or right (to yesterday or tomorrow). Basically, I want it to
show all records, but when it opens to display today's record.

That's not so hard. You can put code in the form's Load event like
this:

'----- start of example code -----
Private Sub Form_Load()

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
Else
Me.Bookmark = .Bookmark
End If
End If

End Sub
'----- end of example code -----

The above is untested, but something along those lines should work.

 

[Antonio]

Dirk, I pasted the code into the load event...but nothing is happening.
Is there a way for me to troubleshoot what I might be doing wrong?

Antonio

Dirk, thanks for the reply. Actually, no, I want the form to open on
the record that contains todays date, but allow the user to scroll
left or right (to yesterday or tomorrow). Basically, I want it to
show all records, but when it opens to display today's record.

That's not so hard. You can put code in the form's Load event like
this:

'----- start of example code -----
Private Sub Form_Load()

With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
Else
Me.Bookmark = .Bookmark
End If
End If

End Sub
'----- end of example code -----

The above is untested, but something along those lines should work.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk, I pasted the code into the load event...but nothing is
happening. Is there a way for me to troubleshoot what I might be
doing wrong?

Hmm, I see one small error. Before debugging, change this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
Else
Me.Bookmark = .Bookmark
End If

to this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
End If
Me.Bookmark = .Bookmark

Also, is "Date" really the name of the field, or did you change it? It
would have been better to change it (and therefore the code, too); that
would have eliminated several possible errors.

Now, set a breakpoint on the first line of the event procedure, and
switch the form from design view to form view. You should get a code
break on the line where you set the breakpoint -- if you don't, the
event procedure isn't being called. Assuming the code *is* being
called, press F8 to step through the code a line at a time to see what
logic path is taken.

If the line ...

Me.Bookmark = .Bookmark

.... is executed, then *something* is happening. If the form doesn't
move from the first record, then presumably the problem is in the
evaluation of the search condition, such that it is always interpreted
as True. My guess then would be that the problem is with the name of
the field. If that's the case, there are ways around it, but the best
solution is to change the name of the field.

 

[Antonio]

I made the changes and now, when the form loads it goes to the last record.
(actually, the first...it goes to January 1, 2005...Right now the table has
dates from January 1 2005 to March 30 2005) I changed the name of the field
to "Duty Day" so that the code looks like this:

Private Sub Form_Load()
With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "[DutyDate] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
End If
Me.Bookmark = .Bookmark
End If
End With
End Sub

Antonio

Dirk, I pasted the code into the load event...but nothing is
happening. Is there a way for me to troubleshoot what I might be
doing wrong?

Hmm, I see one small error. Before debugging, change this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
Else
Me.Bookmark = .Bookmark
End If

to this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
End If
Me.Bookmark = .Bookmark

Also, is "Date" really the name of the field, or did you change it? It
would have been better to change it (and therefore the code, too); that
would have eliminated several possible errors.

Now, set a breakpoint on the first line of the event procedure, and
switch the form from design view to form view. You should get a code
break on the line where you set the breakpoint -- if you don't, the
event procedure isn't being called. Assuming the code *is* being
called, press F8 to step through the code a line at a time to see what
logic path is taken.

If the line ...

Me.Bookmark = .Bookmark

.... is executed, then *something* is happening. If the form doesn't
move from the first record, then presumably the problem is in the
evaluation of the search condition, such that it is always interpreted
as True. My guess then would be that the problem is with the name of
the field. If that's the case, there are ways around it, but the best
solution is to change the name of the field.

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

(please reply to the newsgroup)

 

[Antonio]

Sorry...the field is not called "DutyDate" not "Duty Day".

I made the changes and now, when the form loads it goes to the last record.
(actually, the first...it goes to January 1, 2005...Right now the table has
dates from January 1 2005 to March 30 2005) I changed the name of the field
to "Duty Day" so that the code looks like this:

Private Sub Form_Load()
With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "[DutyDate] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
End If
Me.Bookmark = .Bookmark
End If
End With
End Sub

Antonio

Dirk, I pasted the code into the load event...but nothing is
happening. Is there a way for me to troubleshoot what I might be
doing wrong?

Hmm, I see one small error. Before debugging, change this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
Else
Me.Bookmark = .Bookmark
End If

to this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
End If
Me.Bookmark = .Bookmark

Also, is "Date" really the name of the field, or did you change it? It
would have been better to change it (and therefore the code, too); that
would have eliminated several possible errors.

Now, set a breakpoint on the first line of the event procedure, and
switch the form from design view to form view. You should get a code
break on the line where you set the breakpoint -- if you don't, the
event procedure isn't being called. Assuming the code *is* being
called, press F8 to step through the code a line at a time to see what
logic path is taken.

If the line ...

Me.Bookmark = .Bookmark

.... is executed, then *something* is happening. If the form doesn't
move from the first record, then presumably the problem is in the
evaluation of the search condition, such that it is always interpreted
as True. My guess then would be that the problem is with the name of
the field. If that's the case, there are ways around it, but the best
solution is to change the name of the field.

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

(please reply to the newsgroup)

 

[Antonio]

Dirk! It works!!! Thank you so much for your help. I really appreciate it.

Antonio

Sorry...the field is not called "DutyDate" not "Duty Day".

I made the changes and now, when the form loads it goes to the last record.
(actually, the first...it goes to January 1, 2005...Right now the table has
dates from January 1 2005 to March 30 2005) I changed the name of the field
to "Duty Day" so that the code looks like this:

Private Sub Form_Load()
With Me.RecordsetClone
If .RecordCount > 0 Then
.FindFirst "[DutyDate] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
End If
Me.Bookmark = .Bookmark
End If
End With
End Sub

Antonio

Dirk, I pasted the code into the load event...but nothing is
happening. Is there a way for me to troubleshoot what I might be
doing wrong?

Hmm, I see one small error. Before debugging, change this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
Else
Me.Bookmark = .Bookmark
End If

to this:

.FindFirst "[Date] = " & Format(Date, "\#mm/dd/yyyy\#")
If .NoMatch Then
.MoveLast
End If
Me.Bookmark = .Bookmark

Also, is "Date" really the name of the field, or did you change it? It
would have been better to change it (and therefore the code, too); that
would have eliminated several possible errors.

Now, set a breakpoint on the first line of the event procedure, and
switch the form from design view to form view. You should get a code
break on the line where you set the breakpoint -- if you don't, the
event procedure isn't being called. Assuming the code *is* being
called, press F8 to step through the code a line at a time to see what
logic path is taken.

If the line ...

Me.Bookmark = .Bookmark

.... is executed, then *something* is happening. If the form doesn't
move from the first record, then presumably the problem is in the
evaluation of the search condition, such that it is always interpreted
as True. My guess then would be that the problem is with the name of
the field. If that's the case, there are ways around it, but the best
solution is to change the name of the field.

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

(please reply to the newsgroup)

 

[Dirk Goldgar]

Dirk! It works!!! Thank you so much for your help. I really
appreciate it.

I guess you fixed whatever was wrong while I was sleeping. <g> You're
welcome.