dlookup runtime error 3075

[Bongard]

I have looked at numerous other posts trying many differerent syntax'
but I cannot seem this dlookup formula to work.

I have a two fields on a form [Bill] which is a combo box lookup to
the table [master] and field [LP name]. The second field I have that
need the dlookup is cusip, which is also in the master table. I am
trying to get the dlookup to populate the Cusip text box once the [LP
name] is selected from the combo box [Bill]. This is what I have right
now:
You can see that the top var3 is a trial with a different syntax from
another post. The bottom formula is what I am currently trying and
getting the error message after the update of the combo box saying
"Runtime error 3075. Syntax error (missing operator) in query
expression '[LP Name]=Francisco Partners'."

Where Francisco Partners is the LP name chosen

Private Sub Bill_AfterUpdate()

'var3 = DLookup("[Cusip]", "Master", "[LP Name]="" & Me!Bill & """")

Dim var3 As String
var3 = DLookup("[Cusip]", "[Master]", "[LP Name]=" & Forms!
Frm_MiscTranInfo![Bill])
Me!Text42 = var3

End Sub

Any help or suggestions would be much appreciated!

Thanks,
Brian

 

[Bongard]

I actually found a very useful solution to this problem searching the
forums from an example of John Vinson (Access MVP)

In your Combo box, that is looking up a field from your query, also
pull the fields you want to look up in with the combo box. (this can
be done by adding new fields to the row source/data or just starting a
new combo box and selecting the additional fields to pull in) Then
open the format tab of the combo box' properties window. Go to column
widths and make the width 0, for any items you do not want to appear
in the dropdown.

say the name of the combo box is Bill
Then go to the control source of the text box where you would have
wanted to enter the dlookup and put
=Bill.column(1)

Where "1" is whatever column you need from the Combo box starting with
0.

Hope this can help someone in the future!