Using Variables in place of Field Names

[Dan]

Does anyone know if you can use a Variable in the place of a field name. I
used the following code with no luck. The variable does have the correct
value according to the watch I had on it, but it does not place anything
within the textbox.



Dim FldNm, Box

Dim x As Integer

FldNm = "SL3Item"

Box = "Me!SL3ItemEdit"

x = 1

Do While x <= 3



'Box& x' = DLookup("['FldNm&x']", "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")

x = x + 1

Loop



Thanks for any and all help,

 

[Ken Snell]

Assuming that Box is to be part of the name of the textbox:

Me.Controls(Box & x).Value = DLookup(FldNm & x, "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")

 

[Douglas J. Steele]

If there's a chance that the content of FldNm may include a blank, you'll
want to use square brackets around the field name part of the DLookup as
well. Since there's no penalty to be paid, you're probably best off to
include it even if you don't think there will be blanks:

Me.Controls(Box & x).Value = DLookup("[" & FldNm & x & "]", "[SL3]",
"[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Assuming that Box is to be part of the name of the textbox:

Me.Controls(Box & x).Value = DLookup(FldNm & x, "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")


--
Ken Snell
<MS ACCESS MVP>

Does anyone know if you can use a Variable in the place of a field name. I
used the following code with no luck. The variable does have the correct
value according to the watch I had on it, but it does not place anything
within the textbox.



Dim FldNm, Box

Dim x As Integer

FldNm = "SL3Item"

Box = "Me!SL3ItemEdit"

x = 1

Do While x <= 3



'Box& x' = DLookup("['FldNm&x']", "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")

x = x + 1

Loop



Thanks for any and all help,

 

[Dan]

Thanks,

I was able to get it to work using this code. You have saved me thousands
of line of code and a lot of bloating in this program.

Thanks again,

If there's a chance that the content of FldNm may include a blank, you'll
want to use square brackets around the field name part of the DLookup as
well. Since there's no penalty to be paid, you're probably best off to
include it even if you don't think there will be blanks:

Me.Controls(Box & x).Value = DLookup("[" & FldNm & x & "]", "[SL3]",
"[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Assuming that Box is to be part of the name of the textbox:

Me.Controls(Box & x).Value = DLookup(FldNm & x, "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")


--
Ken Snell
<MS ACCESS MVP>

Does anyone know if you can use a Variable in the place of a field

name.
I

used the following code with no luck. The variable does have the correct
value according to the watch I had on it, but it does not place anything
within the textbox.



Dim FldNm, Box

Dim x As Integer

FldNm = "SL3Item"

Box = "Me!SL3ItemEdit"

x = 1

Do While x <= 3



'Box& x' = DLookup("['FldNm&x']", "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")

x = x + 1

Loop



Thanks for any and all help,

 

[Ken Snell]

Good point, Doug.

--
Ken Snell
<MS ACCESS MVP>

If there's a chance that the content of FldNm may include a blank, you'll
want to use square brackets around the field name part of the DLookup as
well. Since there's no penalty to be paid, you're probably best off to
include it even if you don't think there will be blanks:

Me.Controls(Box & x).Value = DLookup("[" & FldNm & x & "]", "[SL3]",
"[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Assuming that Box is to be part of the name of the textbox:

Me.Controls(Box & x).Value = DLookup(FldNm & x, "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")


--
Ken Snell
<MS ACCESS MVP>

Does anyone know if you can use a Variable in the place of a field

name.
I

used the following code with no luck. The variable does have the correct
value according to the watch I had on it, but it does not place anything
within the textbox.



Dim FldNm, Box

Dim x As Integer

FldNm = "SL3Item"

Box = "Me!SL3ItemEdit"

x = 1

Do While x <= 3



'Box& x' = DLookup("['FldNm&x']", "[SL3]", "[ID]='" &
Me!WpnItemEdit1.Column(2) & "'")

x = x + 1

Loop



Thanks for any and all help,