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
Solved

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

Posted on 2016-09-13
9
46 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 35

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 35

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 35

Expert Comment

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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

860 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