Simple Order Entry Form will not Populate Data

[JGalt90]

I appreciate the help before, here are more specifics. I attached my relationships and the form I would like to have for order entries. It is telling me "Join key of order details not in record set." I cannot wrap my head around what is happening, and why the data won't populate. Please tell me what I would have to do to get a single form for this. Thank you very much, it is greatly appreciated.

-J

[June7]

OrderDetails form needs to be linked to Orders form. Review http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

Otherwise, set Orders form as Continuous view and put OrderDetails as a subform on Orders.

[keviny04]

JGalt90,

As June7 said, your Orders and OrderDetails forms need to be linked together as master and child. But the way your form is set up, they don't seem to be. They are currently both children of the Customers main form. Orders can be the child of Customers, but OrderDetails can't, because OrderDetails doesn't have the CustomerID field like Orders does. My solution is below.

OrderDetails form needs to be linked to Orders form. Review http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

Otherwise, set Orders form as Continuous view and put OrderDetails as a subform on Orders.

You don't need to use the subform-within-a-subform approach (something I want to avoid if I can). You CAN have the form layout as JGalt90 designed and still be able to link Orders and OrderDetails together. Add an INVISIBLE textbox named OrderID on the main form (i.e. the Customers form) and set its control source to be:

=[Orders Subform]![OrderID]

But first, you need to add a CustomerID field on your Customers form, and you need to add OrderID and CustomerID fields in your Orders subform. You need these fields in order to link the three forms together.

Then, in your Orders subform properties, set the link master field and child master field to both be CustomerID . This links the Customers and Orders forms together.

In your OrderDetails subform properties, set the link master field and child master field to both be OrderID. This links the Orders and OrderDetails subforms together. All three forms should now be linked properly.

The result should look something like this.

The necessary linking fields are indicated here, also below:

[JGalt90]

Hi and thank you very much. I did what you said and posted the various errors. I can understand if you are done with this problem, but I need it put more simply. Why is it so hard to input orders. Is it an issue with my relationship design? I'm so frustrated with access for making this so difficult.

[June7]

Going in circles. Suggest you provide db for analysis. Follow instructions at bottom of my post.

[JGalt90]

Thank you, I have attached the DB.

I had this link as well, this guy had essentially what I need, I just couldn't figure it out. Sorry this is taking up time and thank you again. BreadDB.zip

http://ms-access-tips.blogspot.dk/20...y-to-many.html

[June7]

Table relationships are fine - the form design is not right.

Normally OrderDetails would be a subform on Orders but you have these forms at the same level on Customers form. This requires a trick to force Access to recognize the dependency of these two subforms. That hack is demonstrated in link referenced in post2 and keviny04 describes in post3. I don't see any attempt to use this technique in the posted db.

The ProductID textbox on OrderDetails should be a combobox that uses the Products table as 'lookup' source for RowSource.

CustomerID textbox on Orders form should not be editable - in fact, advise removing it. Same for OrderID textbox on OrderDetails. Or at least set them Locked Yes and TabStop No.

[keviny04]

Hi and thank you very much. I did what you said and posted the various errors. I can understand if you are done with this problem, but I need it put more simply. Why is it so hard to input orders. Is it an issue with my relationship design? I'm so frustrated with access for making this so difficult.

To fix your missing OrderID error, you need to put a textbox on the main form with the control source =[Orders Subform]![OrderID]. Just as shown in my earlier picture, where I typed "Make OrderID box invisible". (Making it invisible is for cosmetic purpose; you can make it visible if you like.)



Fixing your error with the missing Product is not so simple. You got the error because you entered a nonexistent product in the order details. And your form doesn't provide a way to enter new products. Even the sample form you mentioned doesn't provide a means to enter products; it only has a dropdown box to look up EXISTING products. As I mentioned in your earlier thread , ideally, you need a separate data entry form for entering products and other master tables.

And June7 was right about protecting the linking fields in the subforms. Ideally, you need to hide the CustomerID column in the Orders subform, and hide the OrderID column in OrderDetails subform. Or make them uneditable as June7 said.

But I sensed that you needed a kind of quick-and-dirty solution. Nothing wrong with quick-and-dirty solutions. I do it all the time -- for simple projects with simple data. For novices, it's a also good way to learn the ropes. But if your demands begin to get higher, then the work will become more involved.

For instance, you don't have to make the linking fields uneditable IF you are sure you won't edit them. And you can enter new products by just opening up the Products table in table view and make the entries there. Those would be the quick-and-dirty ways of doing things. Again, I do it all the time -- whenever I don't need fancy data entry forms. The downside is, of course, exposing the linking fields and editing data in table view make it harder to protect your data. But if you know what you're doing, know how to avoid harming your data, it's fine.

When you try to enter an nonexistent product, are you satisfied with just the generic Access error message "You cannot add or change a record because a related record is required in table 'Products'"? Or do you need a more user-friendly way to handle it?

What I'm trying to say is right now you have a quick-and-dirty entry form. Again, nothing wrong with that, IF you don't mind all its limitations. It still WORKS, but with the aforementioned caveats.

If you need to make a better form than this, then you need to learn the finer details of form design, which include:

- controlling user inputs (how should the user enter new products, new suppliers, etc.)
- controlling the messages the user gets (what to tell the user if a product or supplier doesn't exist)
- user-friendliness (easy-to-follow ways to do the above)

... and many other things. Designing forms is an ART, not a science. For instance, you can have fancy popups for prompting the user to enter new products when needed. Or you don't prompt the user at all, and expect him or her to exit the order form themselves and go to the product data entry form. Both are still VALID designs. It depends on what the users (and their superiors, if any) want, and the designer's "penchant" for certain design choices.

[JGalt90]

Wow, thank you I finally did your steps correctly and it worked! Tracks the orders! I know it is quick and dirty, I planned on cleaning up the look, hiding non-pertinent fields, and applying validation rules to require entry. My last step now is trying to make product ID a combo box. Changing the OrderDetailsID will cascade a change to ProductName, I'm trying to make a dropdown that selects a product and changes the ID. Anyway, thank you! Edit: Cascading Combo Box I think is the avenue I want to investigate.
-J