Creating a new record in a subform (1 Viewer)

[Howlsta]

Hi there,

I am creating an orders database type thing and am struggling with updating info in a subform.

I have three tables in my database one for customers, one for orders (which has fields OrderID and furniture category) and one for customer orders.

In my form I am displaying the customer information in the main part of the form i.e. Name, address etc.

In the subform I am including their order history. I also want to be able to add new orders and this is the problem.

The subform simply shows date and furniture category. If I enter furniture category it creates a new record in the orders table. I don't want it to do this because I have five furniture categories and if I specify "bed" as the furniture category it creates a new record in the orders table. I want the furniture category to correspond to the order number. The order number will be a bit meaningless to the user you see.

So, I only want to add a new record in the customer orders table. This table contains the foreign keys OrderID, CustomerID and also the date. At the moment it is adding the record in this table, but is putting in a new orderID rather than an existing one.

I'm sure I've done this before but not for a couple of years. If I haven't explained very well please ask. I think it is just that the query on which the subform is based needs amending.

 

[Travis]

What are the relationships between these tables?

What I'm see is

Customers
CustomerID Primary Key
Name
Address
etc...

Orders
OrderID Primary Key
Date
FurnitureCatagory

CustomerOrders
CustomerOrdersID Assumption Primary Key
OrderID Foreign Key
CustomerID Foreign Key
Date

This looks like your relationship is:

Customers 1->Many CustomerOrders

Orders 1->Many CustomerOrders

 

[Howlsta]

Thanks for your reply

Yes, the tables are set up like that except that orders just has

OrderID
FurnitureCategory

so the orders table just contains all the types of furniture etc in stock. There are no other fields at the moment.

The other tables are exactly like you say.

The query on which the subform is based includes all tables at the moment with the 1 to many relationships that you mentioned.

So, I want to type in say "suite" for the furniture category and then it gets the orderID for "suite" from the orders table and adds that orderID into CustomerOrders with the customerID from the main part of the form.

Rich

 

[Travis]

If this is the case the "Orders Table" is effectivly a lookup table.


What you want is a Combobox on the form (Hide the First Field). The RowSource of the ComboBox is the "Orders Table" The OrdersID field is the ComboBox's Value and the FurnitureCatagory is the Viewable Field.

 

[Howlsta]

Okay, I'll try it later today.

thanks

Rich