DLookup question

[Guest]

Dear All,

I have tried for the last hour to solve this one but it is really annoying.
I have a form with 26 controls A0 to A25. I am trying to programtically add a
DLookup to each of the TextBox controlsources.

It is the third part of the DLookup that is the problem. "[BtnNo]= 0" would
work but I want to have "[BtnNo]= 0";"[BtnNo]= 1";"[BtnNo]=2", etc.

It is not as simple as concatenating the variable onto [BtnNo] because this
results in a string.

Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Dim strControl As String
For i = 0 To 25
Me.Controls("A" & i).ControlSource =
"=DLookUp(""[ID]"",""TblTillLayout"","[BtnNo]=" & i)"
Next
End Sub

Thanks again.

Alastair

 

[RoyVidar]

Dear All,

I have tried for the last hour to solve this one but it is really
annoying. I have a form with 26 controls A0 to A25. I am trying to
programtically add a DLookup to each of the TextBox controlsources.

It is the third part of the DLookup that is the problem. "[BtnNo]= 0"
would work but I want to have "[BtnNo]= 0";"[BtnNo]= 1";"[BtnNo]=2",
etc.

It is not as simple as concatenating the variable onto [BtnNo]
because this results in a string.

Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Dim strControl As String
For i = 0 To 25
Me.Controls("A" & i).ControlSource =
"=DLookUp(""[ID]"",""TblTillLayout"","[BtnNo]=" & i)"
Next
End Sub

Thanks again.

Alastair

Did you try something like this?
Me.Controls("A" & i).ControlSource = _
"=DLookUp(" & Chr(34) & "[ID]" & Chr(34) & "," & Chr(34) & _
"TblTillLayout" & Chr(34) & "," & Chr(34) & "[BtnNo]= " & i & _
Chr(34) & ")"

 

[Guest]

Try

"[BtnNo] IN (0, 1, 2)"

 

[Alastair MacFarlane]

Thanks guys for the suggestions. I will check them out tomorrow at work (UK
time).

Alastair

 

[Guest]

RoyVidar,

Thanks for your solution. I have used it and it works brilliantly.

Thanks.

Alastair

Dear All,

I have tried for the last hour to solve this one but it is really
annoying. I have a form with 26 controls A0 to A25. I am trying to
programtically add a DLookup to each of the TextBox controlsources.

It is the third part of the DLookup that is the problem. "[BtnNo]= 0"
would work but I want to have "[BtnNo]= 0";"[BtnNo]= 1";"[BtnNo]=2",
etc.

It is not as simple as concatenating the variable onto [BtnNo]
because this results in a string.

Private Sub Form_Open(Cancel As Integer)
Dim i As Integer
Dim strControl As String
For i = 0 To 25
Me.Controls("A" & i).ControlSource =
"=DLookUp(""[ID]"",""TblTillLayout"","[BtnNo]=" & i)"
Next
End Sub

Thanks again.

Alastair

Did you try something like this?
Me.Controls("A" & i).ControlSource = _
"=DLookUp(" & Chr(34) & "[ID]" & Chr(34) & "," & Chr(34) & _
"TblTillLayout" & Chr(34) & "," & Chr(34) & "[BtnNo]= " & i & _
Chr(34) & ")"