Relationship Troubles

[wolfm]

Any reason we can't copy and paste txt into here?



I am an intermediate user struggling t re-learn how to setup relationships among tables.
I messed up my real database so I set up the attached, to back track issues. Trying to figure out how to fix relationships in order to accomplish the below:
My dilemmas:

When entering an order using the 'Orders' form, I would like the job number from the main form 'Orders' to copy into the 'order details subform'. It does not, and I believe my relationships are setup right.
When I open table 'Orders' and click the plus sign at the left of each line, I would like to se theitems ordered, from the 'Order Details' table. I had this working on my real database, butnow is not working. I may have messed up the relationships. I want to see this so I know my relatioships are set up right.

tHANkS AND SORRY FOR THE TYPOS. This forum is messed up today.

[June7]

I am also having issue with copy/paste and even simply typing into post via Internet Explorer. Had to switch over to Edge. Issue started just this week. Nice to know now it is not just my PC.

Relationships are not right. The only one that is correct is link between ID and OrderID.

Do not build lookups in table. http://access.mvps.org/Access/lookupfields.htm

Shows table should be source for JobNumberID combobox. JobNumber should not be in both Orders and OrderDetails.

Why would you have a combobox for quantity? Certainly should not be linked to Orders ID field. Same for JobNumberID and Item. Only OrderID should link to ID in Orders.

What is an Item?

Then where are Customers, Suppliers, Products tables? Are shows your products? What is this db for?

[orange]

WolfM,

For a tutorial and some refresher info, see the link(Database Planning and Design) in my signature; and within that link
-RogersAccessLibrary tutorials will lead you through the process (and has answers)
-Dr. Widom videos on tables design.

Good luck.

[ArviLaanemets]

About form - subform relations: you have to set LinkMasterFields and LinkChildFields properties for subform (NB! For subform containing order details form! You have orders form and order details form. In orders form you have a subform, and order details form is source of this subform.). The link may be defined by one pair of key values (e.g. primary key OrderID of orders table and OrderDetails ID of order details table), or by several key pairs. Child keys must always be table fields, parent keys can be table fields, or controls in parent form. In child form, controls with child key fields as source must always not editable for users, or (even better option) hidden. Whenever a new record is added into suborm, all child key values are populated from parent keys.

About your question about seeing linked info from related tables: it is all about defining relationships. Follow links provided by Orange!

About behaving of forum: It all started after forum was closed for couple of hours some days ago I think. Currently at home I have no problems, but at work I have them a lot!
1. Sometimes after every character I type a carriage return is entered;
2. All time about half of keystrokes are not registered. I have to edit practically every word. For some character I have to type it 3 - 4 times to get it inserted;
3. Copy - paste through Ctrl+C/Ctrl+V doesn't work - but it does work from dropdown menu;
4. Forum is jerking all times. It looks, like text is after every second or so returned to some starting position. E.g. when I try to scroll the forum to read earlier messages, I almost immediately am returned to post I am entering. But when I can click on some older post, then after that I can scroll the forum.

[June7]

I started a thread on the IE issues so more info at https://www.accessforums.net/showthread.php?t=77163

[wolfm]

Point well taken about not running lookups on tables, but off queries. Will create queries for lookups.
I left some tables off this example, to make it simpler. Products table got left out.
My larger database is being made for warehouse ordering,fulfillment and shipping, to replace spreadsheets.
Thanks, as always for your help!

[wolfm]

WolfM,

For a tutorial and some refresher info, see the link(Database Planning and Design) in my signature; and within that link
-RogersAccessLibrary tutorials will lead you through the process (and has answers)
-Dr. Widom videos on tables design.

Good luck.

Thanks for all the info! I will save it in my resources page!

[wolfm]

Thanks Arvil! I will keep your explanation of parent/child fields. That should help!

[wolfm]

Thanks all! Run lookups from queries, use relationships to link major links and use queries to bring info in tables together, consider linked fields. Also, plan ahead and write out a plan before starting in Access. All great advice!