multiple Dlookups for looking on values in multiple tables

[zmbd]

"1Haircut" is the issue given that [Employee ID] is numeric.

What we can look at is a criteria that has [Employee ID] and [Procedure Name]; however, we are without the description of your tables.

So if you will list the fields in table [tblEmployee] we can go from there...

[CVAR]

ok my form is linked to tblSchedule, I'll list the fields of both tables just in case....

tblSchedule

Employee ID-Autonumber
EmployeeName-short text
Start-date
Break-date
End-date

fields (that have to do with this issue) on my tblEmployee are

Employee ID-autonumber
Procedure name-short text

[zmbd]

Remove the " ' " from around the [txtID] as it is a numeric value
Add the criteria against [tblEmployee]![Procedure name], as it is a text field we'll need the " ' " around the value.

Once again, this is typed in by hand; however, it should work, if not then post the resolved string like last time :)

zEmployeeID = "[Employee ID] = " & [txtID] & " AND [Procedure name] = '" & zRsltProcedureName & "'"

If this does work correctly, the Debug.Print lines can be removed.

One thing I really want emphasize here, building the strings outside of the functions. I know that all of the examples show building the strings within the functions; however, it makes it 10x harder to troubleshoot and near impossible to properly error trap if something goes wrong in the string. Especially in this particular case where one is nesting the functions within each other.

Honestly, I most likely would have setup a recordset against this instead of the nested-DLookups; however, that would depend on how the information was being used in the remaining code.

[CVAR]

Ok, again, got a syntax error, but this time I added a extra ampersand here:

 '" *&*  zRsltProcedureName & "'"

for the syntax error to go....


now after running it, I got a new error that reads,

Runtime error 2471
"the expression you entered produced an error :
[Procedure Name] "

I already checked that's the name in the actual field on the table, it is

[zmbd]

++ Post the resolve string please... I can do nothing without that information.

++ Also cut and past the line of code you have, there may be another typo in there...

[CVAR]

ok, sorry for not understanding, but if by "resolve string" you mean what the immediate window says then it says this:

zProcedureName = [Procedure Name]= 'Haircut'
zRsltProcedureName = Haircut
zEmployeeID = '[Employee ID] = '1Haircut''
zProcedureName = [Procedure Name]= 'Haircut'
zRsltProcedureName = Haircut
zEmployeeID = '[Employee ID] = '1Haircut''
zProcedureName = [Procedure Name]= 'Haircut'
zRsltProcedureName = Haircut
zEmployeeID = '[Employee ID] = 1 AND [Procedure name] = 'Haircut''

And this is my code :

Dim zProcedureName As String
   Dim zEmployeeID As String
   Dim zRsltProcedureName As String
   Dim TimeValProc As Date
'
'Stop code here so that we can step thru the code [F8] to
'see where this chokes
'Stop
'
   'let's build the string for the [Procedure Name] look
   '  up and print to the immedate window <ctrl><g> so
   '    that we can see the result.
   zProcedureName = "[Procedure Name]= '" & [cboProcedure] & "'"
   Debug.Print "zProcedureName = " & zProcedureName
   '
   zRsltProcedureName = DLookup("[Procedure Name]", "tblProcedure", zProcedureName)
   Debug.Print "zRsltProcedureName = " & zRsltProcedureName
   '
   'build the second criteria string
   zEmployeeID = "[Employee ID] = " & [txtID] & " AND [Procedure name] = '" & zRsltProcedureName & "'"
   Debug.Print "zEmployeeID = '" & zEmployeeID & "'"
   '
   'and run the final lookup
   TimeValProc = DLookup("[Haircut Time]", "tblEmployee", zEmployeeID)
   
   Debug.Print "timevalproc = " & TimeValProc

Me.Text101 = TimeValProc

[zmbd]

+ Exactly what I am asking for in the immediate window :-)

+ This is almost always either a misspelled field name or a mismatched data type.

++ 1st course of action:
Open the Table in design view, select and copy the field name there to the clipboard.

Go back to the code and paste the field name into the script. I suspect there's an extra space or missing space in the name as given in the code. This is one reason I never ever use spaces in field or table names - difficult to Trouble Shoot.

See if this runs.

++ Second course of action:

Try the following, IF and ONLY IF the first course doesn't solve the issue.
Removeing the quotes from around the value returned in zRsltProcedureN ame:

zEmployeeID = "[Employee ID] = " & [txtID] & " AND [Procedure name] = " & zRsltProcedureName

I am expecting an error here.


In either case, please, replace Line 21 with:

Debug.Print "zEmployeeID = " & zEmployeeID

[CVAR]

ok I tested both course of actions, none worked, same error 2471 happened. Also, it still highlights me the same line in yellow, this line :


TimeValProc = DLookup("[Haircut Time]", "tblEmploye e", zEmployeeID)

[zmbd]

Is [Haircut Time] part of [tblEmployee]?
It is the only other field referenced in the function.
Double check the spelling, in fact, do the same cut and paste with the field name that I suggested for [Procedure name].

[CVAR]

yes, [Haircut Time] is in tblEmployee, in that sentence it is the only field referenced in the function, it can change depending on what procedure I choose on the combo box

well, in fact it was [Procedure Name] in the table, not [Procedure name},so, I already fixed that in the code...

I've double checked the spelling but no errors in the code neither on what the pop up error msg is suggesting