How to combine multiple Dlookup functions?

**PhilOfWalton** · Aug 5 '18, 07:29 PM

Hi Sarah, Welcome to Bytes

Please can you put your code or SQL between the Code Tags so it would look like this

Code:

If DLookup("ECN", "ENGINEERING_CHANGE_NOTICE_TABLE", 
"[ECN] = Forms!DRAWING_INFO_FORM!Subform_DRAWING_STATUS!Sub form_DRAWING_STATUS_ECN!ECN")

There are a number of problems:-

1) You will get writer's cramp using such long names.
2) If statements must end with "Then ...."
3) What precisely are the names of your form & Subform?
4) There is definitely one too many Quotes and other problems in your criteria clause

Phil

**zmbd** · Aug 5 '18, 09:45 PM

sarah1994,
Happy to read that you solved your issue; however, there are a few thoughts I have about your code:

- I'll go out on a limb here and say that most of us use the Dlookup() for one-off returns or simple value searches and tend to avoid them for anything that requires a lot of activity within the database. You have potentially three lookups running here each time your event triggers.

So let's break your conditional down

First lets look at each of the Dlookup():
For reference a generic:
Dlookup([return field],[table/query],[SQL-Where])
As needed I'm going to "step" the script - this will not be a true "VBA" ready stepping just a breaking of the text using the underscore

Code:

DLookup("PCP", "REVIEW_PANEL_TABLE", "[PCP] = -1")

In and of itself, that's not too bad - really depends on what you're doing with this code (more to follow)

Code:

DLookup("GWSHW_BL", _
   "REVIEW_PANEL_TABLE", _
   "'[GWSHW_BL]' = 'Forms!DRAWING_INFO_FORM! _
      Subform_DRAWING_STATUS! _ 
        Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL! _ 
        BL_HWCI_HWCI'")

Hmm... I'll break down what I think the error in your criteria and the subform reference below

Code:

DLookup("GWSHW_HW", "REVIEW_PANEL_TABLE", _
   "'[GWSHW_HW]' = 'Forms!DRAWING_INFO_FORM! _ 
      Subform_DRAWING_STATUS! _ 
         Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL! _ 
         BL_HWCI_HWCI'")

Looks like the same type of errors here as in the second block so I'll address that below:

So the first thing I notice in the second two blocks is that you are attempting to reference a value on a subform. The problem is that you have it within the quotes.

Simple example: Say I have a bound control [zCtrlSomeTextBo x]
It currently holds the record value from record sources field [XmplTxt]="HelloWorld ". If we wanted to look-up a value in the same or other record source using the equivalent criteria string as you have yours (I'm using a simpler one here so you can see what is happening):
"'[XmplTxt]=' = 'ME.zCtrlSomeTe xtBox'"
This renders to
'[XmplTxt]=' = 'ME.zCtrlSomeTe xtBox'
>This is because the value of the control is NOT exposed to the parser because the control reference is enclosed within the string as denoted by the quotes!

what we want is to expose the Control's value thusly:
"[XmplTxt] = '" & ME.zCtrlSomeTex tBox &"'"
Note carefully the placement of the " ' " and " " ", because we're looking for a string value where I have placed the single quotes within the strings enclosed by the double quotes - some use the """" thing and it's a pain to keep track of! NeoPa uses a very slick replace() - but that's for latter!
This will now render to
[XmplTxt] = 'HelloWorld'

So your first step is to fix those issues in your code; read on, because there is a subform referencing issue as well as the string format.

I strongly advise pulling your string value out of the function and instead do something like

Code:

[iCODE]zSQL[/iCODE] = "[XmplTxt] = '" & ME.zCtrlSomeTextBox &"'"
DLookup([RtrnFldName],[RecordSource],[iCODE]zSQL[/iCODE])
'ok, just a generic Dlookup() ;-)

Doing this will also allow you to use NeoPa's very slick replace() - again that's for latter!
AND
You can insert a Debug.Print zSQL and have the string print to the immediate pane (press <ctrl><g>) to see what your code is actually trying to use - you cannot do that if it's locked-up within the function!

SECOND CONSDERATION:
You are pulling from the record source: "REVIEW_PANEL_T ABLE"
for all three lookups.
IF this is the record source for your form then we can use recordsetclone method against your form's record source (or even the subform(s)') otherwise, it may be better to open a record set on this table within the form, at the form level. It appears that you are performing this lookup for every record on the main form - hard to tell without proper context.
DLOOKUP() can be slow - especially if you are performing several in a row as you are apparently doing in your code.
If you open a record set at the form level, or use the form's record set if one and the same, then you can use the recordset.find method (or possibly the Seek if not using a split database and searching directly on the table) - in the long run this may be much faster.

Third consideration - properly referencing a control on the subform.
This appears to have several issues:

Code:

Forms!DRAWING_INFO_FORM! _
      Subform_DRAWING_STATUS! _ 
        Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL! _ 
        BL_HWCI_HWCI

Are you calling the subform with the main form "DRAWING_INFO_F ORM" then you can substitute the shortcut "Me" for that first section.
You are missing the "Form" sub-reference
+Allen Browne: Referring to Controls on a Subform

+The Access Web: Forms: Refer to Form and Subform properties and controls

Your Bangs ( ! ) and Dots ( . ) are a bit askew (refer to above links)
Not sure exactly how to fix your reference... If you are on the main form, and I interpret this correctly, then it looks like you are trying to reference a control on subform2 within subform1 from the parent form:

Code:

Me.Subform_DRAWING_STATUS.Form!Subform_DRAWING_STATUS_CONFIGURATION_HWCI_BL.Form!BL_HWCI_HWCI

I didn't step this line because I didn't want any confusion in the referencing.

Finally,
This kind of code leads me to suspect that your database may be in need of normalization.
>> Database Normalization and Table Structures

**zmbd** · Aug 6 '18, 04:20 AM

just a follow up, in the previous post as I was attempting to properly write the subform reference I used:
[Subform_DRAWING _STATUS_CONFIGU RATION_HWCI_BL]
- I am guessing here that the name for the Subform control was set to the default name of the form it contains.

The default name for the control that contains the actual form is the same as the form's name when the subform is dragged from the Access Object Navigation Pane on to a form in design view.

While for many this doesn't create much of an issue, it can be confusing when one then attempts to describe how to assign the master/child relationships, attempting to use the subform control's events and not the form it contains, and (of course) when attempting to reference the Subform Control and/or the form it contains therein in VBA/SQL/Calculated-Controls.

What I find myself doing is initially using the control toolbox to drag an empty subform control onto the Parent form. The default name is then something like "Child23" then assigning the subform, linking the fields etc... I then go back in and change the "Child##" to something like: "ctrlSubFrmForm sName" so when I reference the control in VBA it's clear what I am doing which makes debugging a lot easier.

IMHO it's best practice to rename the controls from their default names - how to best do this is a subject of some debate.

How to combine multiple Dlookup functions?

How to combine multiple Dlookup functions?

Comment

Comment

Comment