Solved

Fill date field with last date

Posted on 2014-12-09
7
139 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
Comment Utility
Or this:
msgbox DLookup("[Start Date]", "qryLastPatientVisit", "PatientID ='57'")
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
Comment Utility
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
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now