473,287 Members | 1,834 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Open a form on a record closest to the current date

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
7 6475
Delerna
1,134 Expert 1GB
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
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
1,134 Expert 1GB
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
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
1,134 Expert 1GB
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
Thank you very much. That works perfectly.

Regards

Graeme
Mar 1 '08 #7
Delerna
1,134 Expert 1GB
You are very welcome
Mar 2 '08 #8

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

Similar topics

18
by: Darryl Kerkeslager | last post by:
I don't do much with subforms - in fact I've deliberately avoided them - but .... I have a tab control that will contain several subforms, each bound to a separate table, and each table/subform...
1
by: SorboPos | last post by:
Hi. I have a form with continuous forms and a data entry section in the header of the form. (I.e. all transactions show in the main form area like a data sheet and the data for the highlighed...
1
by: 4004 | last post by:
I would like to open a columnar form (so I can see all the details) from a datasheet form (so I can see what is there) but keep the same recordset and current record. I can do the recordset set...
5
by: ortaias | last post by:
I have a form which calls up a second form for purposes of data entry. When closing the data entry form and returning to the main form, things don't work as expected. When I return to the main...
3
by: cyber0ne | last post by:
I'm designing a basic form for data entry into one main table. There are two fields in the table that I would like to be automatically populated, not user-entered, when the record is posted. ...
14
by: keri | last post by:
Hi, Simple version of the question..... How do I use the where clause of the open form command to show an account with a matching ID to be displayed when the form is opened? Eg. I select a...
1
by: cbanks | last post by:
Hello Ladies/Gents, im in need of some serious help here.. I have a tool that allows users to view information on a form and make changes to recordsets. This tool has buttons that allow a user to...
0
by: trixxnixon | last post by:
i have a form that is being designed to pend requests in a requests database. the pend form is opened from an update form used by an employee to enter updates. when the pend form is updated, the...
16
by: GLEberts | last post by:
Subject: Trying to open up a record in a form called "fmappointment" when clicking on a "looks like hyperlink" text box called "ContactID" I have made many instances of this in my database but in...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.