By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Open a form on a record closest to the current date

P: 8
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
Feb 29 '08 #1
Share this Question
Share on Google+
7 Replies


Delerna
Expert 100+
P: 1,134
In vba
query the table to find the date that is closest to todays date
ie
Expand|Select|Wrap|Line Numbers
  1. SELECT max(Dte) as Dte FROM theTable WHERE Dte<=now()
  2.  
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.
Feb 29 '08 #2

P: 8
In vba
query the table to find the date that is closest to todays date
ie
Expand|Select|Wrap|Line Numbers
  1. SELECT max(Dte) as Dte FROM theTable WHERE Dte<=now()
  2.  
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.
Feb 29 '08 #3

Delerna
Expert 100+
P: 1,134
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
Feb 29 '08 #4

P: 8
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.
Mar 1 '08 #5

Delerna
Expert 100+
P: 1,134
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Dim db As DAO.Database
  3.     Dim rst As DAO.Recordset
  4.     Dim strSQL As String
  5.     Dim TheDate As Date
  6.  
  7.     Set db = CurrentDb 
  8.     strSQL = "SELECT max(Dte) as Dte FROM theTable WHERE Dte<=now()"
  9.     Set rst = db.OpenRecordset(strSQL)
  10.     TheDate = rst.Fields(0)
  11.     TheControlForTheDateField.SetFocus
  12.     DoCmd.FindRecord TheDate, , True, , True
  13.     Set rst = Nothing
  14.     Set db = Nothing
  15. End Sub
  16.  
Mar 1 '08 #6

P: 8
Thank you very much. That works perfectly.

Regards

Graeme
Mar 1 '08 #7

Delerna
Expert 100+
P: 1,134
You are very welcome
Mar 2 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.