423,873 Members | 2,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,873 IT Pros & Developers. It's quick & easy.

How to combine multiple Dlookup functions?

P: 1
I am having trouble getting the following segment of my code to run:

Expand|Select|Wrap|Line Numbers
  1.                 If DLookup("PCP", "REVIEW_PANEL_TABLE", "[PCP] = -1") Then
  4.                     strMessage = "Change fourth digit - HWCI/BL has been certified."
  5.                     MsgBox strMessage, vbInformation, "Action Required"
  6.                 Else
  7.                     strMessage = "Change fifth digit"
  8.                     MsgBox strMessage, vbInformation, "Action Required"
I am not getting any errors, but it is not displaying the expected behavior. Can I combine Dlookup functions this way?
Any help is greatly appreciated!
Aug 5 '18 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 1,380
Hi Sarah, Welcome to Bytes

Please can you put your code or SQL between the Code Tags so it would look like this
Expand|Select|Wrap|Line Numbers
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

Aug 5 '18 #2

Expert Mod 5K+
P: 5,285
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

Expand|Select|Wrap|Line Numbers
  1. 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)

Expand|Select|Wrap|Line Numbers
  1. DLookup("GWSHW_BL", _
  3.    "'[GWSHW_BL]' = 'Forms!DRAWING_INFO_FORM! _
  4.       Subform_DRAWING_STATUS! _ 
  6.         BL_HWCI_HWCI'")
Hmm... I'll break down what I think the error in your criteria and the subform reference below

Expand|Select|Wrap|Line Numbers
  2.    "'[GWSHW_HW]' = 'Forms!DRAWING_INFO_FORM! _ 
  3.       Subform_DRAWING_STATUS! _ 
  5.          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 [zCtrlSomeTextBox]
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.zCtrlSomeTextBox'"
This renders to
'[XmplTxt]=' = 'ME.zCtrlSomeTextBox'
>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.zCtrlSomeTextBox &"'"
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

Expand|Select|Wrap|Line Numbers
  1. zSQL = "[XmplTxt] = '" & ME.zCtrlSomeTextBox &"'"
  2. DLookup([RtrnFldName],[RecordSource],zSQL)
  3. 'ok, just a generic Dlookup() ;-)
Doing this will also allow you to use NeoPa's very slick replace() - again that's for latter!
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!

You are pulling from the record source: "REVIEW_PANEL_TABLE"
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:
Expand|Select|Wrap|Line Numbers
  2.       Subform_DRAWING_STATUS! _ 
  4.         BL_HWCI_HWCI
Are you calling the subform with the main form "DRAWING_INFO_FORM" 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:
Expand|Select|Wrap|Line Numbers
I didn't step this line because I didn't want any confusion in the referencing.

This kind of code leads me to suspect that your database may be in need of normalization.
>> Database Normalization and Table Structures
Aug 5 '18 #3

Expert Mod 5K+
P: 5,285
just a follow up, in the previous post as I was attempting to properly write the subform reference I used:
- 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: "ctrlSubFrmFormsName" 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.
Aug 6 '18 #4

Post your reply

Sign in to post your reply or Sign up for a free account.