Auto Populate Data from Tables

[Shalen]

Hi,

I have a database that works well, but I heard there are ways to have data
pull from previous record sources and auto fill based on the data in other
fields to ease the amount of data entry required.

Example: Order Entry Form (Data from form stored in the Order Entry Table)

Field- Order ID
Field- Manufacturer
Field- Customer
Field- Qty (numeric)
Field- Model Number (combo box)
Field- Sku# (combo box)
Field- UPC# (combo box)
Field- Cost (currency)

I would like to enter the Model Number and have Sku#, UPC# and Cost
auto-populate.

I have a Product List Table that contains the following fields
Model ID; Model Number; Model Description;12-digit UPC; SKU#;Manufacturer ID.

Is it possible to enter the Model Number and have the remaining items noted
above auto-populate? If so, please explain what should be in my control
source row and the event procedure needed to complete this task... I am not
up to speed in this area of Access.

Thanks for your help!

 

[Jeff Boyce]

Why? If you have a table that lists Products (and related information like
SKU, UPC, etc.), you don't need to replicate those fields in a new table.
It would be enough to simply store the ProductID. Then, when you need to
see SKU, etc., use a query that joins the ProductID back to the Product
table to get the Product's information.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Shalen]

Jeff,

Thanks for your reply. Maybe I wasn't really clear.... I do not want to
auto-populate data from one table to another. I want the data from the
product table to auto-populate into my order entry form by selecting just one
field, in this case my Model Number. Ideally, I would enter the Model into
the Model Number field (which is a combo box that pulls from my Table[Product
List]), then the information that is contained within the Product List table
(associated by Product ID) would pull the SKU#, UPC# and cost from the table
and enter it for me into the form. Does this make sense? Is this possible?

I gave all the data below so that whomever answered my question would have a
better understanding of my database design- one table for my product list and
another table that holds the data from my order entry form. Maybe I made it
more confusing... oops! I am really just trying to format the order entry
form to do more of the work for me. : )

 

[Jeff Boyce]

Take a look at the .Column() property in Access HELP. The basic approach is
that you'll add code to the AfterUpdate event of your combobox (in which
folks are selecting the Product). That code would look something like:

Me.MySKUTextbox = Me.MyProductComboBox.Column(m)
Me.MyUPCTextbox = Me.MyProductComboBox.Column(n)

You'd use your controls' names, and you'd need to include multiple columns
in your combo box's source. The Column() property is zero-based, so it
starts counting with 0, 1, 2, ...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your reply. Maybe I wasn't really clear.... I do not want to
auto-populate data from one table to another. I want the data from the
product table to auto-populate into my order entry form by selecting just
one
field, in this case my Model Number. Ideally, I would enter the Model
into
the Model Number field (which is a combo box that pulls from my
Table[Product
List]), then the information that is contained within the Product List
table
(associated by Product ID) would pull the SKU#, UPC# and cost from the
table
and enter it for me into the form. Does this make sense? Is this
possible?

I gave all the data below so that whomever answered my question would have
a
better understanding of my database design- one table for my product list
and
another table that holds the data from my order entry form. Maybe I made
it
more confusing... oops! I am really just trying to format the order
entry
form to do more of the work for me. : )
--
Thanks for your help!
Shalen

Why? If you have a table that lists Products (and related information
like
SKU, UPC, etc.), you don't need to replicate those fields in a new table.
It would be enough to simply store the ProductID. Then, when you need to
see SKU, etc., use a query that joins the ProductID back to the Product
table to get the Product's information.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Steve Schapel]

Shalen,

I think the confusion arose where you listed the Sku# and UPC# as fields
in the table that the form is bound to. This should not be the case,
and I am still not sure that you have clarified this.

There are several approaches that are sometimes taken in these types of
scenarios. Some of them are discussed in this article...
http://accesstips.datamanagementsolutions.biz/lookup.htm