Problems updating a customer details

[shaunacol]

I have 3 related tables - tbl_Order has many tbl_OrderDetail and tbl_Order also has many tbl_Customers.



The process for adding orders means that quite often staff do not get time to add customer details (from their paperwork) until afterwards. I have a frm_ClientsOrder with a subform for Frm_OrderSub and a further subform Frm_OrderDetailSub. If I enter info in order (i.e. client then order then order details) this work OK. However, since staff often enter customer details last then it doesn't work as when they enter client details the subforms move onto new records and any order or order details are lost. I have also tried using a button to open a customer form but of course it doesn't recognise clientID<>clientID because they are blank.

I am a little stuck now on how they can enter customer details after the order details. Maybe I have my relationships wrong or am doing something silly? Thanks in advance.

[June7]

Can't. Client record must be created. Whether or not the record is complete with all data is irrelevant, record must exist.

I don't really understand the form/subform/subsubform design. I would expect main form bound to Clients, subform bound to Orders, subsubform bound to OrderDetails.

or

Main form bound to Orders with a combobox to select client and subform bound to OrderDetails.

[shaunacol]

Sorry I probably wasn't clear, my design is exactly as you say (main form bound to Clients, subform bound to Orders, subsubform bound to OrderDetails.) The issue that I have is the users will go straight to OrderDetails (subsub) then when they go back up to enter any Order or Client details then the OrderDetails disappear (i.e. it goes to a new record). It only works if I enter the details in order (i.e. enter something for client, then something for order and lastly any order details). I don't see how I can expect the users to have to do this. Any idea why it jumps to the next record? Ive been trying all day to write some code in the backend so a customer record and then an order record are saved automatically. I can get a customer record to save but not the order so have same issue of it going to next record. I have even tried opening a customer form, adding some details and then opening the order form with a button but tht didn't work either. I am at the end of my tether now Any help appreciated.

[June7]

Code does not go in backend.

Users must have client record first. Then must have order record. Then enter order details. Period. Why would users do otherwise?

Can prevent availability of subform and subsubform with code until data is entered appropriately if users refuse to be educated.

Anything else probably means can't use bound forms and can't set Relationships which then would mean tons of code to populate forms and save data.

[shaunacol]

Yes im going to have to treat it as a training issue. The reason they tend to go straight to OrderDetails is that a customer is in front of them shouting what they want and they take note of that before anything else. its going to be hard to get them to take customers contact details first but if there is no other way then ill train them thanks for feedback.

[June7]

If customers are shouting, sounds like a customer relations issue. There is training for that, even on-line course - I had to take it for the computer tech program I was studying.

[orange]

I'm not clear with what relationships you have.

I have 3 related tables - tbl_Order has many tbl_OrderDetail and tbl_Order also has many tbl_Customers.

I understand Order has many OrderDetails, but Order has many Customers seems backwards.

Please describe the business set up in more detail to clarify your base design.

[shaunacol]

Sorry if I wasn't clear. CUSTOMER table has many ORDERS and ORDERS table has many ORDER DETAILS. This seems like the best and most honest relationship but I am open to suggestions. It just means that my primary form for data entry is customer with a sub of orders and a subsub of order details. Hense issues if the subsub if filled out before the other forms. I hope that makes sense but from what I read above I will have to teach them to enter something in customer and then in order before they can enter any order details. So my current plan is to treat as a training issue.

[orange]

See the links to videos and tutorials here.

The videos deal with Customers, Orders, OrderDetails and Items.

[June7]

Alternative is the second form arrangement described in post 2. This will require use of combobox to select customer and the NotInList event if customer is not found in the combobox list.