Customer Product List - Quantity

[Guest]

Is there a way to make a form display customer info and a list of all
products available with price and an empty quantity field.

I have 3 tables, customer, products and order.
I am trying to create a check out form that will sub total prices and
quantity, any info you could provide will be greatly appreciated

 

[Guest]

Put the customer in a main form and products in a datasheet view subform.
Link Master and Child on the CustomerID. The CustomerID should be a primary
key field so as to set a one-to-many relationship to Orders table. Also
have a a one-to-many relationship from product table, ProductID, to Orders
table.

 

[Steve]

To start you need a different design of your tables:
TblCustomer
CustomerID
CustomerName
<<Other customer contact fields>>

TblProduct
ProductID
ProductName
ProductPrice
<<Other product descriptive fields if needed>>

TblOrder
OrderID
CustomerID
OrderDate
<<Other order fields needed by the database>>

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price

You need a form/subform where the main form is based on a query named
QryFrmOrder and the subform is based on a query named QrySFrmOrderDetail.
QryFrmOrder needs to include TblCustomer and TblOrder with the appropriate
fields in the query. QrySFrmOrderDetail needs to be based on TblOrderDetail.
Besides the appropriate fields from TblOrderDetail, QrySFrmOrderDetail needs
a calculated field that looks like:
ExtendedPrice:[Quantity]*[Price]
To enter the Product (ProductID) in the subform, use a combobox with
TblProduct as the rowsource property. Set the Bound Column property = 1,
Column count = 3 and Column Width = 0;1.5";0. Put the following code in the
AfterUpdate event of the combobox:
Me!Price = Me!NameOfYourComboBox.Column(2)
To get total order, add a text box to the footer of the subform and put the
following expression in the control source property of the textbox:
=Sum([Quantity] * [Price])

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[John W. Vinson]

Is there a way to make a form display customer info and a list of all
products available with price and an empty quantity field.

I have 3 tables, customer, products and order.
I am trying to create a check out form that will sub total prices and
quantity, any info you could provide will be greatly appreciated

You need a fourth table: OrderDetail, with fields for OrderID, ProductID and
Quantity.

Take a look at the Northwind sample database. It does exactly what you're
asking.

John W. Vinson [MVP]

 

[Guest]

Thanks for pointing me in the right direction John, The Northwing db gave me
the insite that I needed.

 

[Guest]

Thanks Steve, the 4th table and the 2 queries did the trick, I was also able
to look at the Northwind db for insite.

To start you need a different design of your tables:
TblCustomer
CustomerID
CustomerName
<<Other customer contact fields>>

TblProduct
ProductID
ProductName
ProductPrice
<<Other product descriptive fields if needed>>

TblOrder
OrderID
CustomerID
OrderDate
<<Other order fields needed by the database>>

TblOrderDetail
OrderDetailID
OrderID
ProductID
Quantity
Price

You need a form/subform where the main form is based on a query named
QryFrmOrder and the subform is based on a query named QrySFrmOrderDetail.
QryFrmOrder needs to include TblCustomer and TblOrder with the appropriate
fields in the query. QrySFrmOrderDetail needs to be based on TblOrderDetail.
Besides the appropriate fields from TblOrderDetail, QrySFrmOrderDetail needs
a calculated field that looks like:
ExtendedPrice:[Quantity]*[Price]
To enter the Product (ProductID) in the subform, use a combobox with
TblProduct as the rowsource property. Set the Bound Column property = 1,
Column count = 3 and Column Width = 0;1.5";0. Put the following code in the
AfterUpdate event of the combobox:
Me!Price = Me!NameOfYourComboBox.Column(2)
To get total order, add a text box to the footer of the subform and put the
following expression in the control source property of the textbox:
=Sum([Quantity] * [Price])

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Is there a way to make a form display customer info and a list of all
products available with price and an empty quantity field.

I have 3 tables, customer, products and order.
I am trying to create a check out form that will sub total prices and
quantity, any info you could provide will be greatly appreciated

 

[Guest]

Thanks Karl

Put the customer in a main form and products in a datasheet view subform.
Link Master and Child on the CustomerID. The CustomerID should be a primary
key field so as to set a one-to-many relationship to Orders table. Also
have a a one-to-many relationship from product table, ProductID, to Orders
table.