Auto Populate Data from Tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shalen - 23 Jun 2006 19:27 GMT

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!

Reply to this Message

Jeff Boyce - 23 Jun 2006 20:23 GMT

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

> Hi,
>
[quoted text clipped - 28 lines]
>
> Thanks for your help!

Reply to this Message

Shalen - 23 Jun 2006 21:03 GMT

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. : )

Signature

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.
[quoted text clipped - 39 lines]
> >
> > Thanks for your help!

Reply to this Message

Jeff Boyce - 23 Jun 2006 21:12 GMT

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,
>
[quoted text clipped - 72 lines]
>> >
>> > Thanks for your help!

Reply to this Message

Steve Schapel - 24 Jun 2006 05:02 GMT

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

Signature

Steve Schapel, Microsoft Access MVP

> Jeff,
>
[quoted text clipped - 12 lines]
> more confusing... oops! I am really just trying to format the order entry
> form to do more of the work for me. : )

Reply to this Message