Orders Table won't add Customer Name

[3239]

After an Order Is placed by a customer in my Orders form, The customer name replaces an existing customer in the Orders tables instead of going to a new record of its own.
Could someone help me please!!!

 

[RoyVidar]

Hi!

To be able to address your challenge, we would need some more information on your table structure. Please describe the customer table and order table and how you've set up relationship between them.

Roy-Vidar

 

[3239]

Orders Table : CustomersTable: OrderDetails:
OrderID CustomerID Date
CustomerName LastName OrderDetailID
OrderDate FirstName OrderID
PickUpDate Address ProductID
PaymentMethod City ServiceType
Status State Quantity
Zipcode UnitPrice
Phone Location

CustomerID in the CustomersTable is a One-to-Many Relationship to CustomerName in Orders.

OrderID in the Orders table is a One-to-Many Relationship with OrderID in OrderDetails.

I have a table called Status(Which is not listed above) that has a productkey field called StatusID which has a One-to-Many Relationship with Status in the Orders Table.

I have a table called Locations(Not Listed)that has LocationID as the Primary key, and it has a One-to-Many Relation with Location in Order Details.

 

[RoyVidar]

Hi!

I'm not sure what "CustomerName" contains in the Order table. An ordinary relationship between Customer and Order, would be a numeric PK (Primary key) in the Customer table (Your CustomerID), and using the same field (with index type Yes - duplicates OK) as FK (Foreign Key)in the order table. The usage of "CustomerName" as the name of the FK, makes me believe this might be an issue here.

Only one other comment to the structure, the field Date in your order details, should be renamed, cause Date is a reserved word in Access (the Date function).

If what I've commented on above, is OK in your structure, then we'll have to move on to how you've buildt the forms (which fields are included in the forms recordsource. I think one thing to check, is which field do you store the selected customer in - is it the field "CustomerName" from the Order table or the field CustomerID from the Customer table) - but then, do elaborate a bit more on the form structure (main/sub, usage of combos...)

HTH Roy-Vidar

 

[3239]

Can I e-mail the database to you?

 

[RoyVidar]

Some of the members do that, I don't.

You might try to wait and see if some of the members who do, might pop into this thread and assist, or perhaps, if you have the possibility to upload the db to some server, perhaps some of us might download, and continue to address issues thru this thread.

Roy-Vidar