Software

Ask something
Discussion Locked

MS Access

0 helpful
3 replies

by cp03246

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I had to create a db with a 1:many relationship of date:on call personnel. (short date format)
I used a wizard to create the form that is to be published for use throughout the building.
On Open, the date is always 1/1/2005; I want the form to consistently open to today's date. I tried Control Source=Date and table.date() and it still starts at 1/1/2005. Without using filter by form or filter on date data element, how do I get the form to open on the correct recordset of today's date?

3 replies

by dryflies

the correct function in VBA is Now() to return todays date. I think what you want to do is use a filter where table.date = now(). if table.date is other than a date, perhaps a text, then make sure the date format in the field is consitant and format your date before you make the compare.

by cp03246

Thank you for your quick response.
The Now() function has the time hh:mm:ss in the format as a default. When you use the 'advance to next' arrows on the bottom of the screen, the Now() date advances in seconds, not days. The end users sometimes need to be able to check ahead a few days occassionally.
Any other ideas? Appreciate your help.

by desktroll

Date() returns todays date.
Now() returns todays date and the current time so don't use NOW()

Assuming that your form opens immediately when you open the data base you will need to add code to the on open event of the form

eg

Private Sub Form_Open(Cancel As Integer)

Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "MyDate= #" & Format(Date, "mm/dd/yyyy") & "#"
If Not rst.NoMatch Then

Me.Bookmark = rst.Bookmark

Else

MsgBox "No data for today found!!!", vbCritical, Date

End If

Set rst = Nothing

End Sub

use your fieldname for the date field in place of MyDate

This discussion is closed to new replies.