Dlookup in variable

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

Hello
In my form, I am programmatically trying to set the controlsource of a field
to be a dlookup.
When I try to apply my Dlookup, I get a syntax error.

I have it on an afterupdate from a cascading cbo

Private Sub Combo4_AfterUpdate()
Dim val As String
val = varval
Me.Instructions_subform1!In_Instructions.ControlSource = val
End Sub

Function varval() As String
varval = "=DLookUp([In_Instructions],Instructions'[In_C_Type] =' _ &
Forms![Inst]!Combo0) & And '[In_P_Type]=' & Forms![Inst]!Combo4)"
End Function

What am I doing wrong? I think its probably the quotes that are wrong, but
Im not sure.

Thank you so much
Terry v
 
Terry,

I think you have missed a , out after Instructions.

varval = "=DLookUp([In_Instructions],Instructions,'[In_C_Type] =' _ &
Forms![Inst]!Combo0) & And '[In_P_Type]=' & Forms![Inst]!Combo4)"

HTH,

Neil.
 
In addition to the comma, you need to pass some quotes inside the string.

varval = "=DLookUp(""[In_Instructions]"",""Instructions"",""[In_C_Type]=" &
Forms![Inst]!Combo0 & " And [In_P_Type]=" & Forms![Inst]!Combo4 & ")"

This should work of Combo0's and Combo4's values are numbers. If they are
text, you'll need some more quotes to wrap the text values.

varval = "=DLookUp(""[In_Instructions]"",""Instructions"",""[In_C_Type]='" &
Forms![Inst]!Combo0 & "' And [In_P_Type]='" & Forms![Inst]!Combo4 & "')"
 
Back
Top