Solved

Is it possible to auto populate an Access form control using a nested DLookup?

Posted on 2016-09-13
9
50 Views
Last Modified: 2016-09-15
I have a control on a form that needs to auto-update/lookup the "rate" after a selection has been made in two previous combo boxes, "location" and "specialty", on the same form.

If I were to use a query for data already stored in the Assignments table, then the following works great.

SELECT A.AssignmentID, R.Rate
FROM (tblMalpracticeLocations L 
INNER JOIN tblAssignments A ON L.LocationID = A.MPLocation)
INNER JOIN tblMalpracticeRates R 
ON (L.TerritoryID = R.TerritoryID) AND (A.MPSpecialty = R.SpecialtyClassID)
ORDER BY A.AssignmentID;

Open in new window


.........................

However, the above only works when the data is already stored in the table.  I'm working on a form used to enter data. I have 2 combo boxes to select Location and Specialty (the IDs of which are stored in the table fields once the record is saved), and I have a text box that I would like to auto-update with the Rate based on the selections in the combo boxes (this will not be stored in the table but simply auto-updated anytime the data in the combo boxes changes.)

So, in my text box, I'm trying the following code --- and of course, I'm getting an error.  I'm not sure if the code is wrong or if it's that I'm not capturing the data selected in the combo boxes so the code knows what to use.  

DLookup("Rate", "tblMalpracticeRates", 
"(SpecialtyClassID = " & Forms![frmAssignments]!MPSpecialty)
 AND (TerritoryID = DLookup("TerritoryID", "tblMalpracticeLocations", 
"LocationID = & Forms![frmAssignments]!MPLocation")))

Open in new window


Does that make sense?  Can anyone send me in the right direction with this?  Sometimes it seems like writing the question is almost as hard as making the code work. :-/
0
Comment
Question by:fabi2004
9 Comments
 
LVL 4

Expert Comment

by:bfuchs
ID: 41797231
try

DLookup("Rate", "tblMalpracticeRates",
"SpecialtyClassID = " & Forms![frmAssignments]!MPSpecialty
  & " AND TerritoryID = " & DLookup("TerritoryID", "tblMalpracticeLocations",
"LocationID ="  & Forms![frmAssignments]!MPLocation))

or have another text box = the second dlookup and put that name in criteria
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41797570
You can use SQL for this:

DLookup("Rate", "tblMalpracticeRates",
"SpecialtyClassID = " & Forms![frmAssignments]!MPSpecialty) & " AND TerritoryID =
(Select First(TerritoryID) From tblMalpracticeLocations Where LocationID = " & Forms![frmAssignments]!MPLocation & ")")

/gustav
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41797590
Hi.

Also, you can use a recordset to load a query, and then populate the textbox.

For example:

Private Sub MPSpecialty_Click()
    
    If Not IsNull(Me.MPSpecialty) Then
        Call UpdateTextBox
    End If

End Sub

Private Sub MPLocation_Click()

    If Not IsNull(Me.MPLocation) Then
        Call UpdateTextBox
    End If

End Sub

Private Sub UpdateTextBox()

Dim rs As DAO.Recordset

    [Forms]![frmAssignments]!ResultTexBox = vbNullString
    
    rs = CurrentDb.OpenRecordset("SELECT A.AssignmentID, R.Rate FROM (tblMalpracticeLocations AS L INNER JOIN tblAssignments AS A ON L.LocationID = A.MPLocation) INNER JOIN tblMalpracticeRates AS R ON (L.TerritoryID = R.TerritoryID) AND (A.MPSpecialty = R.SpecialtyClassID) " & _
                                "WHERE (((A.MPSpecialty) = " & [Forms]![frmAssignments]![MPSpecialty]) & " And ((L.TerritoryID) = " & [Forms]![frmAssignments]![MPLocation] & " )) ORDER BY A.AssignmentID;"
                                
    If Not (rs.BOF And rs.EOF) Then
        [Forms]![frmAssignments]!ResultTexBox = rs.Fields("AssignmentID")
    End If

    rs.Close
    
    Set rs = Nothing
    
End Sub

Open in new window

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 36

Expert Comment

by:PatHartman
ID: 41798803
Or you can use no code at all.  Simply include the lookup tables in your form's RecordSource query.  Simply use a left join so you will get the main record even when the lookup value is not yet populated.

Automagically, once the lookup values are populated, the results will appear on the form and you do not have to do a thing to make this happen.  It is all smoke and mirrors - but in a good way:)

Just one word of warning.  When using this technique (called auto lookup), it is best to lock the lookup controls so the user doesn't accidentally change them.  For example, if I wanted to show the population of Stratford, ct, I would choose CT from the state list, then choose Stratford from the city list and immediately (since the foreign key values are now populated), the population field would display.  However, I almost certainly would not want to modify population on this form so I would set its locked property to yes to avoid an accident.
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41800395
Thank you all for your recommendations!  I appreciate you helping me with this.

Sorry it's taken me so long to reply.  I've been trying your suggestions and have ended up scratching my head even more than before.  I'm beginning to think the problem is deeper than simply setting up a complicated search.

bfuchs and Gustav, I get Type errors for both of your suggestions.

Antonio, reading through your code I have a couple of concerns.  1.  I need the result based on the information from both previous fields, not just one.  So, the initial Click routines have to be nested or combined I think.  So that, if one is not null it checks the other.  If either is null, then I need an abort.  If both are not null, then I can proceed with the search.  2.  The search itself looks like it's depending on the AssignmentID from the Assignments table.  That is the same problem I have with my original query.  Since this is a form that is in data entry mode, I do not think the AssignmentID and other data has been saved to the table yet.  I need to pull it from the form itself.  At least, that's how I think Access works.

Hi Pat, I wish I could use your solution.  I tried that route first, before I started looking for functions to use on the control.  I can't get the joins to work correctly.  The error is always 'ambiguous outer joins'.

I know I've seen this done before on a form.  If one control is not null AND a second control is not null, then use the information in both of those controls (by refreshing the form or something) to look up the data for a third control.

I think the lookup is what's confusing things because of the way I have data in the Assignments table joined to the Rates table.

There is probably a different way to approach this - maybe using queries joined to tables underlying the form (which is actually a subform).
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41800467
The "ambiguous outer joins" error can be fixed by creating a query that does the problem join and then joining that query back to the main query.  As joins are defined, you can go from an inner join to an outer join but not vice versa.
0
 
LVL 1

Author Closing Comment

by:fabi2004
ID: 41800553
Pat, as always, you're a genius.  Thank you so much!  It worked like a charm.
0
 
LVL 1

Author Comment

by:fabi2004
ID: 41800607
Oops, my subforms became unupdateable.  "This recordset is not updateable."  I tried adding/removing primary and foreign keys from the form's recordset but no luck.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41800875
Try setting the Recordset Type on the Data tab to Inconsistent updates.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

680 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