Solved

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

Posted on 2016-09-13
9
35 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 3

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 49

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
 
LVL 34

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
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)

 
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 34

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 34

Expert Comment

by:PatHartman
ID: 41800875
Try setting the Recordset Type on the Data tab to Inconsistent updates.
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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
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 …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

708 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

16 Experts available now in Live!

Get 1:1 Help Now