Form built on query

[Guest]

I am trying to replicate the functionality of the Northwind Database. I have
a Products Table and a Details Table both with a Unit Price field.
Previously, I built my database using the Unit Price field from the Products
Table. This becomes a problem whenever a price is changed in the Products
Table. It also changes all the Unit Prices in the Details Table.

I found the Northwind Database and am trying to replicate the way it uses
the Unit Price from the Details query. However, I end up with a $0.00 as the
Unit Price field. In Northwind this field automatically is populated with
the Products Unit Price value.

Can anyone have any suggestions or help with this?

 

[John Vinson]

I am trying to replicate the functionality of the Northwind Database. I have
a Products Table and a Details Table both with a Unit Price field.
Previously, I built my database using the Unit Price field from the Products
Table. This becomes a problem whenever a price is changed in the Products
Table. It also changes all the Unit Prices in the Details Table.

I found the Northwind Database and am trying to replicate the way it uses
the Unit Price from the Details query. However, I end up with a $0.00 as the
Unit Price field. In Northwind this field automatically is populated with
the Products Unit Price value.

Can anyone have any suggestions or help with this?

You will need to "push" the price into the UnitPrice field in the
AfterUpdate event of the product combo box. Take a look at the
Northwind Orders form; view the product combo's AfterUpdate event, and
model yours thereupon. Post back if it's not clear.

John W. Vinson[MVP]

 

[Guest]

That's great! Thank you very much. However, I've developed another problem
now that I am basing my form on a query. If I enter more than one line into
my subform, the main form becomes multiple records of the same. I am using a
field called Quote# as an Auto number. So, if I enter two records in the
subform field, I end up with two records using the same Quote # in the main
form.

As I look at my old data base, I see that in queries and tables this is
done. However, in the form it was consolidated into one record. Any
suggestions?

 

[John Vinson]

That's great! Thank you very much. However, I've developed another problem
now that I am basing my form on a query. If I enter more than one line into
my subform, the main form becomes multiple records of the same. I am using a
field called Quote# as an Auto number. So, if I enter two records in the
subform field, I end up with two records using the same Quote # in the main
form.

As I look at my old data base, I see that in queries and tables this is
done. However, in the form it was consolidated into one record. Any
suggestions?

Don't base your Form on that particular query, is all.

The main Form should be based on the "one" side table; the subform on
the "many" side table. It sounds like you've based the main form (or
perhaps both forms) on a query joining the two tables; this is neither
necessary nor appropriate, for just the reason you're seeing!

If that's not the case, please open the two forms in design view; view
their properties; and post the Recordsource property. If it's a query,
please post the SQL view of the query.

John W. Vinson[MVP]

 

[Guest]

That did it. As I stated previously, I was trying to replicate the Northwind
Database and their main form "Orders" is based on a query called "Orders
Qry".

Thanks for your help.