Solved

Fill date field with last date

Posted on 2014-12-09
7
144 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 49

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

861 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

29 Experts available now in Live!

Get 1:1 Help Now