Using Table Fields to populate a Textbox

  • Thread starter Thread starter d
  • Start date Start date
D

d

I need to pull information from several Tables based on their joined field.
I had bee doing it by using a query and a ComboBox, but as the amount of
fields grows past the 255 limit, I would like to pull the info straight from
the Tables. I used the following code to pull from the ComboBox:

Me!SL3ItemEdit1.Value = Me!WpnTypeEdit6.Column(4)

This worked great except for two things; I need to do this several hundred
times over the course of the program - so I need to write a For Next or a Do
While and change the to:
Me!SL3ItemEdit2.Value = Me!WpnTypeEdit6.Column(5)
Me!SL3ItemEdit3.Value = Me!WpnTypeEdit6.Column(6)

and so on. Also the code to pull from the table instead of the ComboBox.

Thanks for any help offered,
 
I need to pull information from several Tables based on their joined field.
I had bee doing it by using a query and a ComboBox, but as the amount of
fields grows past the 255 limit, I would like to pull the info straight from
the Tables. I used the following code to pull from the ComboBox:

Me!SL3ItemEdit1.Value = Me!WpnTypeEdit6.Column(4)

This worked great except for two things; I need to do this several hundred
times over the course of the program - so I need to write a For Next or a Do
While and change the to:
Me!SL3ItemEdit2.Value = Me!WpnTypeEdit6.Column(5)
Me!SL3ItemEdit3.Value = Me!WpnTypeEdit6.Column(6)

and so on. Also the code to pull from the table instead of the ComboBox.
Click to expand...

The need to do this is EXTREMELY suspicious. It appears that you're
trying to move data from the table being used as WpnTypeEdit6's
Rowsource into the Form's Recordsource table.

WHY?

Storing data redundantly is a very bad idea and is rarely necessary.
If you do have a valid reason to move data from one table to another
table, an Append or Update query is a much simpler and more efficient
way to do it. And if you have 255 fields in your table, your table is
almost certainly incorrectly normalized!

To answer your question though - DLookUp will retrieve data from a
table:

Me!SL3ItemEdit2 = DLookUp("[fieldname]", "[tablename]", "<criteria>")
 
I know that this sounds bad. I have not figured out any other way to work
with this. I have many pieces of equipment and I need to store the
inventory of each major piece of equipment.



I used DLookUp, it was exactly what I need. Now to prevent from having to
write thousands of line of repetative code I am trying to insert a variable
in place of the field name:



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



When I use a watch to look at the value of the variables, they are what I
want them to be, but the code does not actually do anything. In other words
the textbox is not updated.



Thanks Again,

John Vinson said:
I need to pull information from several Tables based on their joined field.
I had bee doing it by using a query and a ComboBox, but as the amount of
fields grows past the 255 limit, I would like to pull the info straight from
the Tables. I used the following code to pull from the ComboBox:

Me!SL3ItemEdit1.Value = Me!WpnTypeEdit6.Column(4)

This worked great except for two things; I need to do this several hundred
times over the course of the program - so I need to write a For Next or a Do
While and change the to:
Me!SL3ItemEdit2.Value = Me!WpnTypeEdit6.Column(5)
Me!SL3ItemEdit3.Value = Me!WpnTypeEdit6.Column(6)

and so on. Also the code to pull from the table instead of the ComboBox.
Click to expand...

The need to do this is EXTREMELY suspicious. It appears that you're
trying to move data from the table being used as WpnTypeEdit6's
Rowsource into the Form's Recordsource table.

WHY?

Storing data redundantly is a very bad idea and is rarely necessary.
If you do have a valid reason to move data from one table to another
table, an Append or Update query is a much simpler and more efficient
way to do it. And if you have 255 fields in your table, your table is
almost certainly incorrectly normalized!

To answer your question though - DLookUp will retrieve data from a
table:

Me!SL3ItemEdit2 = DLookUp("[fieldname]", "[tablename]", "<criteria>")
Click to expand...
 
I know that this sounds bad. I have not figured out any other way to work
with this. I have many pieces of equipment and I need to store the
inventory of each major piece of equipment.
Click to expand...

Ummm... how about an Update query, or an Append query? Where is the
inventory stored, and where are you trying to put it? Thousands of
DLookUps is very nearly the WORST possible way to move data from one
table to another!
I used DLookUp, it was exactly what I need. Now to prevent from having to
write thousands of line of repetative code I am trying to insert a variable
in place of the field name:



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) & "'")
Click to expand...

If you MUST do it this way - and I am *NOT* at all convinced that
this is even a plausible way to accomplish the task, much less a good
one - bear in mind that the three arguments to DLookUp are strings. It
is NOT obligatory that they be string constants - the first should be
a string which evaluates to a fieldname, the second a string which is
the name of a table or query, the third a string which is a valid SQL
WHERE clause without the word WHERE.

strControlName = "SL3ItemEdit"
Me.Controls(strControlName) = DLookUp("FldName" & x, "SL3", "[ID] =
....

should work. BUT AN UPDATE QUERY WILL WORK BETTER. I can't suggest how
that query would be written because I don't know your table structure
- but I can say with great certainty that extracting data into a Combo
Box and then relaying it into another table via DLookUps is like going
from Boston to Providence via Saskatoon and Mexico City.
 
Back
Top