Opening a form at a specific date

[Paulcliman]

My "Weather" database is working well, thanks to help & advice here.
The data entry form poses a problem for me.
Since the last record will be 4 days in advance (it being forecasts) and the first record becomes increasingly distant in the past as days go by, is there ANY way of making a form open automatcally at the currentdate?

 

[DCrake]

Off the top of my head (only 5' 2") if your form is based on a recordset then on the on load event get the recordset to do a .MoveLast then use a .MovePrevious in For x = 1 to 4 Loop

David

 

[Paulcliman]

Maybe I've misled you - in fact, the data entry form I want to adjust actually loads from a switchboard form. Does that make a diiference to your suggestion?

 

[DCrake]

From what I gleanded from #1 was that you open a form that has an underlying recordsource, be it table/query/recordset. Within that source are many records, with dates ranging from day 1 to today + 4 days. You question was can I get it to open the form with the record pointer being placed at the first record with today's date. Is this correct?


David

 

[Paulcliman]

Indeed - it occured to me later that my question must have been an irrelevance !
This is what I came up with as a procedure on the OnLoad event of the form :
Private Sub Form_Load()
On Error Goto Err_MyProc
Dim db as Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("weather")
rst.MoveLast
For x=1 to 4
rst.MovePrevious
Next x
Exit_MyProc:
Set rst = Nothing
Set db = Nothing
Exit Sub
Err_MyProc:
Resume Exit_MyProc
End Sub

................. but it doesn't work, form still opens at record 1.

 

[DCrake]

You have done the code but you have not told the form to use this recordsource.

Just tried this and it worked

If the form is a bound form then sort the underlying query by your date field

Next you need to count how many records there are that have a date less than today

Dim I as interger

I = Nz(DCount(*","YourQuery","[YourDate] < #" & Date() &"#"),0)

Then on the OnLoad event get the table to move to the first record that has today's date in the field

DoCmd.GoToRecord , , acGoTo, I+1

You may need to include error checking ensure that there are records that have todays date in them.

David

 

[Paulcliman]

Well, I've just given this my best shot, but the debugger doesn't like Date() and insists on removing the brackets??
Also, can I (dare I) question the first bit of your line:

I = Nz(DCount(*","YourQuery","[YourDate] < #" & Date() &"#"),0)

is there really an asterisk in there or should it be opening quotes?

 

[DCrake]

Sorry Paul it was a typo there should be double quotes around the *

And if Access wants to take off the brackets then let it.

Press Ctrl+G to go to the immediate window and enter

? Nz(DCount("*","YourQuery","[YourDate] < #" & Date() &"#"),0)

This should return a value

Remember to use your query and field names not the ones offered.

David

 

[Paulcliman]

Corrected the typo - did the Ctrl-G thing and got the value 0
Saved everything and went back to the db, but the form still opens at record 1

 

[DCrake]

Can you post your mdb? or a smnippet of it to look at.

 

[Paulcliman]

here it is .....

 

[DCrake]

Just fired up this demo

take a look at the code

 

[missinglinq]

...is there ANY way of making a form open automatcally at the currentdate?

I think you've maybe over explained your situation, leading to some confusion. If all you're looking to do is stated in the quote above, then all you need is:

Private Sub Form_Load()
 YourDateField.SetFocus
 DoCmd.FindRecord Date
End Sub

Just replace YourDateField with the actual name of your date field.

 

[DCrake]

missingling:

May well be doing as this is something never attempted so suggestions were off the top of my head.

 

[Paulcliman]

Once again - many thanks for such speedy responses.
Fascinating results.
I made two copies of my database and used each to try out each of your ideas.
Missinglinq - yours worked & sent me straight to today's record
DCrake - yours worked (sort of) sending me directly to YESTERDAY'S record ! Maybe I need to change "I" to "I+1" which is what it was originally?
Thank you both for the time & trouble.

 

[Paulcliman]

Yes .................. that worked !