How to use DLOOKUP with multiple selections 1

[Steven547]

Hey everyone...
I have a form that the user selects from 3 combo boxes. Each box generates specific values in the next (they are dependent upon each other). After all 3 are selected, a text box is populated based on those 3 combo boxes.

How would I populate the txt box based on 3 combos? This is the DLOOKUP I have for one record. The txt box is based off the company, subcompany, and department. All 3 of those equals ONE record in the txtBCCoord box:

txtBCCoord = DLookup("[BCCoord]", "[CFR-Master]", " [Company]=cboCompany")

Would I do an "and" after the [company] = cboCompany? I'm trying that and it's not working. Any thoughts?

Thanks.

 

[Steven547]

Sorry, forgot to post the full DLOOKUP:

txtBCCoord = DLookup("[BCCoord]", "[CFR-Master]", " [Company]=Company" And [SubCompany] = SubCompany And [Department] = Department)

I'm getting a DataType Mismatch...

 

[TheAceMan1]

How are ya Steven547 . . .

... and this:

[blue]txtBCCoord = DLookup("[BCCoord]", "[CFR-Master]", "[Company] = '" & Me!Company" And "'[SubCompany] = '" & Me!SubCompany & "'"[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Steven547]

Thanks. Trying that and getting a syntax error.

txtBCCoord = DLookup("[BCCoord]", "[CFR-Master]", "[Company] = '" & Me!Company" And "'[SubCompany] = '" & Me!SubCompany & "'"

So the "[Company] = '" & Me!Company" is the same as [company]=cboCompany?

 

[Gnism]

Replace:
"[Company] = '" & Me!Company" And

With
"[Company] = '" & Me!Company & "' And

 

[Steven547]

So it was missing that ' ? I was just "googling" that right now.

 

[Steven547]

Still trying to figure this out. This is what I have:
txtActualTargetPath = DLookup("[Target Data]", "[tblMigration]", " [Company]= '" & me!cboCompany & "' and " [subCompany]="' & me!cboSubcompany & '" and "'[department]='" & me!cboDepartment")

The txtActualPath is based on the the info gathered from the 3 sources: cboCompany, cboSubCompany, and cboDepartment. I keep getting a syntax error or "expression" error and can't figure out why.

text field = target field from the table 'A' where company=cboCompany and subcompany=cboSubCompany and department=cboDepartment.

 

[PHV]

txtActualTargetPath = DLookup("[Target Data]", "tblMigration", "Company='" & me!cboCompany & "' and subCompany='"& me!cboSubcompany & "' and department='" & me!cboDepartment & "'")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886