Open Form based on date

[MARKYB0Y]

Hi All,

I have a form that lists scoresheets, dates are predefined on these scoresheets, what I would like is for when this form is called the row displayed is not row 1 but whatever date is nearest to todays date but not in the future

how can I do this?

 

[pbaldy]

Try basing it on a query:

SELECT Blah
FROM TableName
WHERE DateField = (SELECT MAX(DateField) FROM TableName WHERE DateField <= Date())

 

[MARKYB0Y]

Hi PBaldy,

I have tried that but it does not seem to work

the table is called Scores and the date field is called date

I tried this

SELECT *
FROM Scores
WHERE Date = (SELECT MAX(Date) FROM Scores WHERE Date <= Date());

and the query ran correctly

but when I change the record source of the form it still opens on the first record?

any ideas?

 

[georgedwilkinson]

Problem: Date is a reserved word in Access and you have a field named Date. Rename it!

 

[pbaldy]

I agree with George, but to test bracket the field name:

[Date]

 

[MARKYB0Y]

Bracketing the Date has made the query run, but it isnt going to the correct row, it is only listing the one row, from there I am unable to select previous records.

 

[pbaldy]

Oh, I misunderstood. You want all records available, just start out at that record? One way would be a using a bookmark, either from the calling form, or perhaps in the load event of the form. Use a recordset or DMax to get the relevant date, then use that in the bookmark code to go to that record.

 

[MARKYB0Y]

Hi Pbaldy,

apologies fro not making myself clearer, but yes I want all records available, I simply want the first records visible to be the most recent record.

I am not familiar with a bookmark code, can you elaborate please?

thanks

 

[MARKYB0Y]

I have been trying using the query in the OnOpen and OnClick events but to no avail. So I want all 30 rows of the table to be available but when the form opens I need it to open to the record with the most recent date.

thanks for all your help

 

[pbaldy]

Sorry, I was busy over the holidays. Did you sort this out, or still need the bookmark code?

 

[MARKYB0Y]

Hi Pbaldy,

I have not been able to find anything based on bookmark code, so anythin gyou could provide would be greatfully received

thanks

 

[pbaldy]

If you can post a sample db, I can do it to your form, which would make it most relevant to you.

 

[MARKYB0Y]

My File is too big to upload (5.5mb)

 

[pbaldy]

Okay, then you're stuck with what I have laying around.

Here's a sample of what I meant.