need one drop down box to populate three different fields

[snoopy12]

I am having form trouble. i have a drop down box that when a product is chosen a second field will populate with the corresponding product code. I would like that same drop down box to populate two other fields. the drop down is pulling data from a specific table that contains all the information for all three fields on the form. i stumbled upon getting it to currently work with the one field, so i'm not sure how to repeat the steps for the other two. also, i don't necessarily need the three populated fields on my form as long as the drop down is there and they populate the fields correctly on the table. is this possible or am i looking for a miracle?

 

[bob fitz]

If the combobox has the required data in it's columns, then a textbox control can be populated with the data from one or more columns of the combobox using code in the combobox's After Update event which references the combobox's Column property. Something like:
Me.[NameOfTextbox] = Me.[NameOfCombobox].Column(1)
The number (1) is the number of the column that has the data required. Keep in mind that the column numbers start with 0. So first column is number 0, second is number 1 etc.

 

[Pat Hartman]

Generally copying data from one table to another is unnecessary and poor practice. The only time you would ever do this is when you need to capture the data at a point in time. An example of this would be the unit price for an item in an order entry application. The order should contain the price of the item at the time the order was placed or filled.

It is easy enough to simply show lookup data by using a query that joins to the lookup table.

 

[bob fitz]

Pat

A good point, well raised, with which I would agree whole heartedly.

 

[snoopy12]

okay, I think I'm following Bob's thought and will give that a try. In reference to Pat's post - i have a product table with a list of products and their corresponding UPC numbers and product numbers. when a customer places an order i'm using a sales table that would include the customer information and data from the product table based on what they are ordering. is what i'm doing still redundant? i'm trying to keep it simple yet automated.

 

[DarrenR]

Just a thought coming from a newbie. If the data is coming from/going to different tables, wouldn't you just establish a One-to-Many or Many-to-Many relationship and query it that way?

 

[Pat Hartman]

The order table should have the CustomerID. From that you can look up the customer name and billing address. When you create the order, you can copy the billing address from the customer record into the shipping address of the order record. As to the items, you would store the ProductID and copy the UnitPrice. All the other fields would be lookuped with queries as necessary. The point is to store only the ID the vast majority of the time. Then occassionally, you may need to copy a field when you need to retain its value at a point in time so you don't want to use its ID to get the "current" value.

 

[snoopy12]

the customer name/address is working fine. i have a customer form/table to store their information and it populates correctly when i enter a sales record. will try the couple of suggestions given for the product issues. if needed can i post the database to have someone look at it and offer solutions or improvements?

 

[bob fitz]

if needed can i post the database to have someone look at it and offer solutions or improvements?

Yes, you can attach a db to a post, provided that it is not too big. Remember to remove any sensitivte data. Some of us are still using A2003, so if you can attatch a mdb version, there will be more people that are able to take a look at it.

 

[snoopy12]

Okay, i figure i'll post it for some input while i also try to work on it. this is what i would like it to do sticking with the sales records form:
when i choose an item from the 'product' drop down i would like it to auto-fill the following fields - product description, product id and GTIN number. all of these will pull from the Case GTIN table. Currently when i choose the product it fills GTIN properly. Once i get them to work correctly i can remove the fields from the form as long as i know the table is populating correctly. I'll also change the tab order and move things around on the form at that time.

on a side note, how do i get the fields to keep preceeding zeroes. you will see in the forms i have columns named filler with zeroes. when i convert the table to a text file i need the zeroes to remain and not disappear or convert to spaces.

thanks for any help or input.

 

[bob fitz]

The file that you've attached has not been converted properly to 2002-2003 mdb version. I can not open it.

 

[snoopy12]

I tried to convert it again to 2003, but it won't allow it. i've attached another file, but it's from 2010. something about using updated features that won't allow it to save to an earlier version.

 

[bob fitz]

I tried to convert it again to 2003, but it won't allow it. i've attached another file, but it's from 2010. something about using updated features that won't allow it to save to an earlier version.

Okay. Thanks for trying.