Dlookup Multiple Criteria

[dcollard23]

Hello,

If there a way to combine all of the dlookup formulas since I am wanting the underwriter, region and line to fill in based on the prefix of the policy number?

Right now I have them separate:

'I am wanting the lookup to find the underwriter by looking at the policy prefix field and based on the prefix of the policy number pull the underwriter that is listed for that prefix"
Me.Underwriter = DLookup("Underwriter", "tblPolicyPrefix", "[PolicyPrefix] = '" & Left(Me.Policy_Number, 3) & "'")

'I am wanting the lookup to find the region by looking at the policy prefix field and based on the prefix of the policy number pull the region that is listed for that prefix"
Me.region = DLookup("region", "tblPolicyPrefix", "[PolicyPrefix] = '" & Left(Me.Policy_Number, 3) & "'")

'I am wanting the lookup to find the line by looking at the policy prefix field and based on the prefix of the policy number pull the line that is listed for that prefix"
Me.line = DLookup("Line", "tblPolicyPrefix", "[PolicyPrefix] = '" & Left(Me.Policy_Number, 3) & "'")


Thanks
Elaine

 

[KenHigg]

Sorry thats about it with dlookup(). You could write a function but if you're trying to get out of lines of code its about a wash.

 

[dcollard23]

Thanks Ken

 

[JANR]

Open up a recordset and get all 3 in one go.

ex:

With CurrentDb.OpenRecordset(" SELECT Underwriter,region,Line" & _
                             " FROM tblPolicyPrefix" & _
                             " WHERE PolicyPrefix ='" & Left(Me.Policy_Number, 3) & "'")
    If .RecordCount > 0 Then
        Me.Underwriter = !Underwriter
        Me.region = !region
        Me.Line = !Line
    End If
End With

JR

 

[dcollard23]

Thank you so much JANR it worked.

 

[JANR]

No problem, happy to help

JR

 

[Pat Hartman]

There are several solutions for this and JANR gave you one. I'll give you the NO CODE solution. Create a query that joins the main table to the lookup table and use that as the RecordSource for your form. You can select columns from both tables and bind them to controls. When you fill in the FK using the combo, the "lookup" fields will automagically populate. When I use this technique (which is most of the time), I usually set the locked property of the lookup controls to yes to avoid accidental updates unless I want the user to be able to modify the lookup values.

 

[dcollard23]

Thank you Pat. Will try it.