Open a form on a record closest to the current date

[GraemeC]

I have a form (single record form) that loads records from a query that are sorted date order as the records are viewed in date order. Some dates are in the past and some are in the future. Currently the form opens on the last record and the date of that record can be 6 months out. The users would like the form to open on the record closest to the current date. Does anyone know how I can achieve this?

Thanks

[Delerna]

In vba
query the table to find the date that is closest to todays date
ie

SELECT max(Dte) as Dte FROM theTable WHERE Dte<=now()
 

Put the date retrieved into a variable.
Now just do a find record for the date that was returned

I haven't put any specific code here, just the general idea.
Is that enough for you to do it?
If not, ask and I will post a fuller example.

[GraemeC]

In vba
query the table to find the date that is closest to todays date
ie

SELECT max(Dte) as Dte FROM theTable WHERE Dte<=now()
 

Put the date retrieved into a variable.
Now just do a find record for the date that was returned

I haven't put any specific code here, just the general idea.
Is that enough for you to do it?
If not, ask and I will post a fuller example.

Thanks for this but yes a fuller example will be much appreciated.

[Delerna]

OK
Two questions and once I have the answers I will do a mockup and post back
1) What version of access are you using
2) Do you have much experience with VBA

[GraemeC]

Hi

We're using Access 2003.

I use VBA in Excel a lot but my experience of using VBA in Access is a slightly less.

As an aside I have got a query to determine the appropriate date in my table but now just help putting that into the form so that the form opens on that date. My question now is where do I put the select statement you gave me or what os the best way to call it when the form opens?

Thanks again.

[Delerna]

Put the following into the forms code page and adjust to suit your database
you will probably need to add a reference to the Microsoft DAO 3.6 Object library.
ADO is the better way to go but I chose DAO for you because I think its a little easier to learn. Others may disagree.
If you want to use ADO I suggest you check the help file for access.

Private Sub Form_Load()
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim TheDate As Date
 
    Set db = CurrentDb 
    strSQL = "SELECT max(Dte) as Dte FROM theTable WHERE Dte<=now()"
    Set rst = db.OpenRecordset(strSQL)
    TheDate = rst.Fields(0)
    TheControlForTheDateField.SetFocus
    DoCmd.FindRecord TheDate, , True, , True
    Set rst = Nothing
    Set db = Nothing
End Sub
 

[GraemeC]

Thank you very much. That works perfectly.

Regards

Graeme

[Delerna]

You are very welcome