Copying data from a table to a form

[kaya]

I have created a simple Order form, which includes one main form and one subform.

In the main form, the user is required to input Order Number, Date and select a customer from a combo box.

In the subform, user is required to select products and input the quantity of products sold to each customer.

Here is what I wanted to do:
After selecting a customer from the main form, ALL the products (from the Product table) will automatically be shown in the subform. So that the user will not have to choose the products from a combo box everytime they enter a new order. Instead, the user will ONLY be required to enter the quantity sold in the subform.

Does anyone knows how to do it? Please give me some advices. Thank you.

________________________________________________
MAIN FORM

Order Number: 001
Order Date: Oct 1, 2002
Customer Name: Samtron
________________________________________________
SUBFORM

PRODUCTS: QTY SOLD:
Pen
Pencial
Ruler
Easer
Highlighter
.
.
.
.
.

 

[Jeff Bailey]

Well, your subform is based on a table (or a query based on a table). You need to place rows in this table. Each row must have the CustomerID and one of the products.

One way to do this is to create a query based on the Product table. Put the Product name in the query and place a calculated field in there as well (something like CustName: Forms!frmMyMainFormName!CustIDField) which will take the CustomerID from your main form.

Change the query to an Append query and append to the relevant fields in your subform table.

You need to run the query when the customerID has been entered in the main form (after update event maybe). You might need to requery the subform after running the query.

me!subformcontrol.form.requery

is the syntax. Also

docmd.setwarnings false

will supress the Access warning about appending rows - but you should remember to place

docmd.setwarnings true

afterwards to switch the warnings back on. Do NOT leave them supressed - you need them to avoid deleting etc by mistake.

 

[kaya]

Thank you for your prompt reply.

I tried that method before but an error msg poped up"MS Access is unable to append the records due to key violation."

I think it's probably because I used the ProductID field in my append query. I want to have the productID stored in the table underlying my subform.

What should I do now?

 

[Jeff Bailey]

A key violation (probably) means one of 2 things is happening

You are trying to save 2 or more identical items in a field indexed No Duplicates

You have a primary key field which is being left blank.

In the first case you'll have to set the index on the relevant field to Duplicates OK

In the second case you'll probably need to create a new key field based on an autonumber.

Or maybe something else is going on ...