Loop difficulty

[Bill (Unique as my name)]

Replacing
[Forms]![fpaysheet]![prds1]
with
[Forms]![fpaysheet].controls("prds" & CStr(variable))
works, but replacing
[Forms]![fpaysheet]![pr1]
with
[Forms]![fpaysheet].controls("pr" & CStr(variable))
does not work.

Surely its a syntax error on my part. Where did I go wrong?
Thank you so much in advance.

[Forms]![fpaysheet]![prds1] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr1] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
[Forms]![fpaysheet]![prds2] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr2] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
[Forms]![fpaysheet]![prds3] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr3] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
.. . .
[Forms]![fpaysheet]![prds14] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr14] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")

 

[Douglas J. Steele]

For intLoop = 1 to 14
[Forms]![fpaysheet].Controls(["prds" & intloop] = _
DLookup("prds", "qfmpayroll", _
"[qfmpayroll]![prda] = " & _
[Forms]![fpaysheet].Controls("pr" & intLoop1) _
" AND [qfmpayroll]![pifirstlast] = " & _
[Forms]![fpaysheet]![pifirstlast])
Next intLoop

This assumes that prda and pifirstlast are numeric fields. If they're text,
you need to include quotes:

For intLoop = 1 to 14
[Forms]![fpaysheet].Controls(["prds" & intloop] = _
DLookup("prds", "qfmpayroll", _
"[qfmpayroll]![prda] = '" & _
[Forms]![fpaysheet].Controls("pr" & intLoop1) _
"' AND [qfmpayroll]![pifirstlast] = '" & _
[Forms]![fpaysheet]![pifirstlast] & "'")
Next intLoop

Exagerated for clarity, that's

For intLoop = 1 to 14
[Forms]![fpaysheet].Controls(["prds" & intloop] = _
DLookup("prds", "qfmpayroll", _
"[qfmpayroll]![prda] = ' " & _
[Forms]![fpaysheet].Controls("pr" & intLoop1) _
" ' AND [qfmpayroll]![pifirstlast] = ' " & _
[Forms]![fpaysheet]![pifirstlast] & " ' ")
Next intLoop

 

[Marshall Barton]

Replacing
[Forms]![fpaysheet]![prds1]
with
[Forms]![fpaysheet].controls("prds" & CStr(variable))
works, but replacing
[Forms]![fpaysheet]![pr1]
with
[Forms]![fpaysheet].controls("pr" & CStr(variable))
does not work.

Surely its a syntax error on my part. Where did I go wrong?
Thank you so much in advance.

[Forms]![fpaysheet]![prds1] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr1] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
[Forms]![fpaysheet]![prds2] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr2] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
[Forms]![fpaysheet]![prds3] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr3] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
. . .
[Forms]![fpaysheet]![prds14] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr14] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")

Your statements above need to use the value of the prN
controls, not a reference to them. The ! reference to the
query field should also produce an error.


If all the fields in the query are numeric, use this:

Me("prds" & variable) = DLookup("prds", "qfmpayroll",
"prda = " & Me("pr" & variable) & " AND pifirstlast = " &
Me.pifirstlast)

If they are text strings, then you need to enclose the
values in quotes (count them carefully):

Me("prds" & variable) = DLookup("prds", "qfmpayroll",
"prda = """ & Me("pr" & variable) & """ AND pifirstlast =
""" & Me.pifirstlast & """")

 

[Bill (Unique as my name)]

Dear Doug and Marshall,

Thanks for the information.
You guys make user groups the most wonderful place in the world.

Replacing
[Forms]![fpaysheet]![prds1]
with
[Forms]![fpaysheet].controls("prds" & CStr(variable))
works, but replacing
[Forms]![fpaysheet]![pr1]
with
[Forms]![fpaysheet].controls("pr" & CStr(variable))
does not work.

Surely its a syntax error on my part. Where did I go wrong?
Thank you so much in advance.

[Forms]![fpaysheet]![prds1] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr1] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
[Forms]![fpaysheet]![prds2] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr2] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
[Forms]![fpaysheet]![prds3] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr3] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")
. . .
[Forms]![fpaysheet]![prds14] = DLookup("prds", "qfmpayroll",
"[qfmpayroll]![prda] = [Forms]![fpaysheet]![pr14] AND
[qfmpayroll]![pifirstlast] = [Forms]![fpaysheet]![pifirstlast]")

Your statements above need to use the value of the prN
controls, not a reference to them. The ! reference to the
query field should also produce an error.


If all the fields in the query are numeric, use this:

Me("prds" & variable) = DLookup("prds", "qfmpayroll",
"prda = " & Me("pr" & variable) & " AND pifirstlast = " &
Me.pifirstlast)

If they are text strings, then you need to enclose the
values in quotes (count them carefully):

Me("prds" & variable) = DLookup("prds", "qfmpayroll",
"prda = """ & Me("pr" & variable) & """ AND pifirstlast =
""" & Me.pifirstlast & """")