By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,649 Members | 1,808 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,649 IT Pros & Developers. It's quick & easy.

Cry For Help! - Filling In Forms Automatically From Tables

P: n/a
I am DESPERATE for a quick reply.. have exhausted my options...

i have a table called order details which contains:

order id
product id
product name
quantity
unit price
sale price
discount

and a products table containing:
Product id
product name
unit price
units in stock

I have a form which currently shows text boxes for
order id
product id
product name
quantity
unit price

(product id is a combo box)

i can choose the product id from the drop down i have created but it does
not put any other data into any of the other fields.. how on earth EXACTLY
can i manage to do this? i want the data from the product table to fill in
the form once i choose the product id from the form combo box

cheers for ANY help

Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
If the Product Name will not change (always based on the Product ID) then
there is generally no good reason to save the Product Name in the details
table. Do you have a good reason to save both the Product ID and Product
Name in the details table?

--
Duane Hookom
MS Access MVP
"-elniniol999-" <allan_reid@"nospamplease"hotmail.com> wrote in message
news:u5*************@newsfe3-win.server.ntli.net...
I am DESPERATE for a quick reply.. have exhausted my options...

i have a table called order details which contains:

order id
product id
product name
quantity
unit price
sale price
discount

and a products table containing:
Product id
product name
unit price
units in stock

I have a form which currently shows text boxes for
order id
product id
product name
quantity
unit price

(product id is a combo box)

i can choose the product id from the drop down i have created but it does
not put any other data into any of the other fields.. how on earth EXACTLY
can i manage to do this? i want the data from the product table to fill in
the form once i choose the product id from the form combo box

cheers for ANY help

Nov 13 '05 #2

P: n/a
Make sure that the form is based on the order details table....or a query
that joins both and gives you the info you are looking for...

The 'magic' happens by.
1. Making sure that the design wizard is turned on....wand in the toolbar.
2. Creating a new combo box in the FORM HEADER..if you can't see it already,
click VIEW, FORM HEADER.
3, The wizard kicks in to complete the process...with 'FIND DATA based on a
selection.

HTH
Mal.
"-elniniol999-" <allan_reid@"nospamplease"hotmail.com> wrote in message
news:u5*************@newsfe3-win.server.ntli.net...
I am DESPERATE for a quick reply.. have exhausted my options...

i have a table called order details which contains:

order id
product id
product name
quantity
unit price
sale price
discount

and a products table containing:
Product id
product name
unit price
units in stock

I have a form which currently shows text boxes for
order id
product id
product name
quantity
unit price

(product id is a combo box)

i can choose the product id from the drop down i have created but it does
not put any other data into any of the other fields.. how on earth EXACTLY
can i manage to do this? i want the data from the product table to fill in
the form once i choose the product id from the form combo box

cheers for ANY help

Nov 13 '05 #3

P: n/a
Your tables are wrongly set up.

You need to have one table, which holds the order information, with
the following fields:

Order Table:
----------------
order id
product id
quantity

and one table for the product information with the following fields:

Product Table:
------------------

Product id
product name
unit price
units in stock
sale price
discount

Create a query from these two tables with:

+ order id, product id and quantity from the Order table,
+ then product name, unit price, units in stock, sale price and
discount from the Product table.

Use this query with all its fields as the basis for your form. Add the
fields to your form respectively change the control source for
existing fields to the query fields.
Whenever you now select a product from your combo box (which should
add its info into the 'product id' field), all other fields should
update accordingly.
"-elniniol999-" <allan_reid@"nospamplease"hotmail.com> wrote in message news:<u5*************@newsfe3-win.server.ntli.net>...
I am DESPERATE for a quick reply.. have exhausted my options...

i have a table called order details which contains:

order id
product id
product name
quantity
unit price
sale price
discount

and a products table containing:
Product id
product name
unit price
units in stock

I have a form which currently shows text boxes for
order id
product id
product name
quantity
unit price

(product id is a combo box)

i can choose the product id from the drop down i have created but it does
not put any other data into any of the other fields.. how on earth EXACTLY
can i manage to do this? i want the data from the product table to fill in
the form once i choose the product id from the form combo box

cheers for ANY help

Nov 13 '05 #4

P: n/a
"Sabine Oebbecke" <Sa*************@unilever.com> wrote in message
news:5c**************************@posting.google.c om...
Your tables are wrongly set up.

You need to have one table, which holds the order information, with
the following fields:

Order Table:
----------------
order id
product id
quantity

and one table for the product information with the following fields:

Product Table:
------------------

Product id
product name
unit price
units in stock
sale price
discount

Create a query from these two tables with:

+ order id, product id and quantity from the Order table,
+ then product name, unit price, units in stock, sale price and
discount from the Product table.

Use this query with all its fields as the basis for your form. Add the
fields to your form respectively change the control source for
existing fields to the query fields.
Whenever you now select a product from your combo box (which should
add its info into the 'product id' field), all other fields should
update accordingly.


Sorry, but I strongly disagree with this setup. Both Sale Price and
Discount are dynamic values that can (and likely will) change over time.
With your method anyone looking at an Order from a year ago would see
*current* price and discount values, not the values that were in effect at
the time of the Order.

This is a case where it is NOT a breach of normalization principles to copy
the values from the Products table to the Orders table at the time the
Order record is created so the OP was closer to doing it correctly. The
only thing I would set up differently is that the Orders table does need to
have both the ProductID and the ProductName.

To the OP...

Add all of the info you need to the ComboBox for ProductID as additional
columns (they can be hidden if desired). Then in the AfterUpdtae event of
the ComboBox you run code similar to...

Me.Price = Me.ProductID.Column(1)
Me.Discount = Me.ProductID.Column(2)
etc..

I would NOT do the above for ProductName as this does not need to be stored
with your Order. Just have an unbound TextBox with a ControlSource of...

=ProductID.Column(3)

....where the fourth column of the ComboBox contains the ProductName from
the Products table. This will allow the user to *see* the ProductName, but
will not redundantly store it.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


Nov 13 '05 #5

P: n/a
> Sorry, but I strongly disagree with this setup. Both Sale
Price and Discount are dynamic values that can (and
likely will) change over time.
With your method anyone looking at an Order from a year
ago would see *current* price and discount values, not
the values that were in effect at the time of the Order.


Of course you are right, Rick. Thanks for pointing it out.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.