Using Variables in place of Field Names

  • Thread starter Thread starter Dan
  • Start date Start date
D

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,
 
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) & "'")
 
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)



Ken Snell said:
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>

Dan said:
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,
Click to expand...
Click to expand...
 
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,

Douglas J. Steele said:
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)



Ken Snell said:
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>

Dan said:
Does anyone know if you can use a Variable in the place of a field
Click to expand...
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,
Click to expand...
Click to expand...
Click to expand...
 
Good point, Doug.

--
Ken Snell
<MS ACCESS MVP>

Douglas J. Steele said:
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)



Ken Snell said:
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>

Dan said:
Does anyone know if you can use a Variable in the place of a field
Click to expand...
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,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top