Solved

Fill date field with last date

Posted on 2014-12-09
7
148 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

816 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