Solved

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

Posted on 2016-09-13
9
51 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 37

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 37

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 37

Expert Comment

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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
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 …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

737 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