Solved

Fill date field with last date

Posted on 2014-12-09
7
162 Views
Last Modified: 2014-12-11
I have a form which has a date field named "Date of Previous Visit".  For the control source I'm trying to use:

=[qryLastPatientVisit]![Start Date]

But it doesn't like that.  Note that the query is a query that returns just the last record, not using DLast because I've learned that won't work.  Instead it is a select query returning just one record in descending order.

What is wrong with my control source?

I also tried:

Me.txtDateOfPreviousVisit = Queries!qryLastPatientVisit.[Start Date]

in the oncurrent event of the form but it didn't like that either.
0
Comment
Question by:SteveL13
7 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40490012
Something like this should work if you have the PatientID on the form (and the query really does return the last date for each patient...)

Dlookup("[Start Date]","qryLastPatientVisit","PatientID=" & me.PatientID)
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 40490017
If I understand what you are doing;

The problem is that just because you reference it does not mean that it is running.  Your form is open and has an active connection to the table or query, but has not opened the query qryLastPatientVisit. So your form will either have to reference it as part of your data source or perhaps you will need to run a macro to run it and retrieve the value.

Essentually you are trying to call a source that is completely unrelated to the form you have open.

You could resolve this by using the on current event to run the query and store the value into a variable and then use the variable to populate the field.
0
 

Author Comment

by:SteveL13
ID: 40490058
Trying this in the oncurrent event of the form but Getting #Name in the field on the form

Me.txtDateOfPreviousVisit = DLookup("[Start Date]", "qryLastPatientVisit", "PatientID =" & Me.cboPatientID)

Maybe because PatientID is a text field?
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40490476
Perhaps, ...
Me.txtDateOfPreviousVisit = DLookup("[Start Date]", "qryLastPatientVisit", "PatientID =" & "'" & Me.cboPatientID & "'")

But basic troubleshooting will tell you to try a hard coded value to test this  (Before ever adding it to a form current event;)

something like this :
msgbox DLookup("[Start Date]", "qryLastPatientVisit", "PatientID =" & "'" & 57 & "'")
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40490493
Or this:
msgbox DLookup("[Start Date]", "qryLastPatientVisit", "PatientID ='57'")
0
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 40490676
You could use DMax and skip the query:

=DMax("[Start Date]","tblPatientVisit","PatientID = '" & [cboPatientID] & "'")

or:

=DMax("[Start Date]","tblPatientVisit","PatientID = '" & Forms!frmYourFormName![cboPatientID] & "'")

or you could modify the rowsource for cboPatientID to include  StartDate (create a join to qryLastPatientVisit) and have a column for this. Then, if that column is the second, it would be:

=[cboPatientID].Column(1)

/gustav
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40491914
Please investigate Gustav's post above...
My post just used what you had already

Dmax is typically better at getting the "Last" value
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question