Multiple uses of a query field on a form

[voltarei]

Hi all,
I'm trying to design a form that allows users to enter a part number, and have the description and price populate automatically.
I have designed a query from 3 linked tables (Customer, Staff, Products) which works wonderfully if there is only product to be entered.

If I try to use the part number, description and price fields from the query more than once on the form for multiple items to be entered, the same item appears in every field, and no matter which part number field you enter the information into, it changes them all to reflect the entry.

Is there a way around this, so I can have more than 1 product added to a form?

Many thanks
Phil

 

[IGPCS]

if i got you right i would add a primary key if you dont have yet one and then add before the query the me.refresh and then add to the query "where feild = formname.fieldname"

let me know

 

[PHV]

The part number, description and price controls must be bound to the underlaying query if you use a continuous form.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[voltarei]

Hi,
Thanks for the replies so far, however I'm not sure I understand !!
Maybe my initial description was not full enough, so I'll tell you how it all links together.

I'm actually using 4 tables:

Customer:
Includes name & address data, with a Customer ID as the primary key

Staff:
Includes name and address data, with a Staff ID as the primary key

Products:
Supplier Name
Part Number - primary key
Description
Size
Price

Invoice Table:
Sales Number - primary key
Invoice Date
Company Name - linked to same field in customer table
Staff ID - linked to same field in Staff table
Payment terms
Part Number - linked to same field in Products table
Description
Price

The query includes the following fields:
Sales Number - from invoice table
Invoice Date - from invoice table
Payment terms - from invoice table
Staff ID - invoice table
Part Number - invoice table
Description - products table
Price - products table
Company Name - invoice table

All of the above fields appear on a form, and if the user enters a product number into the product number field, the description & Price are automatically populated.

However, if on the form I add the part number, description, and price fields 3 times each, to look like:

Product Number Description Price
Product Number Description Price
Product Number Description Price

and the user enters a product number into the first product number field, all 3 product number fields are populated with the same number, all the descriptions are the same, and all the prices are the same.

What I'd like to do is allow the user to add a different product number to each field, so each different product number automatically populates the relevant description and price fields.

Does that make it any clearer ?????
(I did have the form set for the user to type in the data manually, but thought it would be easy to auto populate the fields based on an entered product number !!)

 

[PHV]

Your DB seems denormalised.
I think you need an InvoiceHeader table and a InvoiceDetails table.
Have a look here:

http://r937.com/relational.html

You may also play with the Northwind sample database.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886