access database engine cannot find a record - Query or join wrong?

[Shortskimper]

@Pat Hartman

You've previously said this "BUT, it will NEVER show more than ONE record because the tblDetails is not included in qryOrders"

Could you explain why tblDetails is required to be in qryOrders and what joins should be on it and why? I'm assuming this is the many-to-many relationship you are speaking of. I've attempted to make 2 primary keys (JobID_FK and DetailsID_FK) with no success. If you become frustrated with my lack knowledge. Please let me know and I will look elsewhere for this answer as i feel this is the key to the form properly functioning. I have no doubt this question is answered in your DB AND what you've previously stated but I sadly cannot find it through context and apply it to my situation currently. Apologies!

 

[Pat Hartman]

As I said, I do NOT understand that form at all so I can't comment on it. I only mention the details because if you are ordering something, it would be the details. What else would it be?

Maybe you should step back and rather than ask us to fix your code/form, which I think I actually did when I discovered it was set as DataEntry because that was preventing the form from showing any records when it opened, tell us what business problem you are trying to solve. You alluded to the database being 8 years old so apparently it has been working. What new feature are you trying to add?

 

[Shortskimper]

As I said, I do NOT understand that form at all so I can't comment on it. I only mention the details because if you are ordering something, it would be the details. What else would it be?

Maybe you should step back and rather than ask us to fix your code/form, which I think I actually did when I discovered it was set as DataEntry because that was preventing the form from showing any records when it opened, tell us what business problem you are trying to solve. You alluded to the database being 8 years old so apparently it has been working. What new feature are you trying to add?

I would never request you fix it for me. I'm attempting to understand why my form isn't doing what I expect it to do.

The purpose of the form is for an individual to pull in the releaseID (making it relational) and to manually type in orders in to the OrderBody. The details table is down the road and shouldn't matter in this context. There will be multiple orders per release.

Changing DataEntry to No does let you enter exactly one entry. I need to be able to enter multiple entries but am unsure on what I have wrong in the query (i think it's in the query) to allow that.

 

[Shortskimper]

This is qryOrders. It will allow me to put in only the first record (when a left join is applied), but will not allow me to put in more records than that with the same JobID and ReleaseID pulled in.

This is the result if i attempt to continue to add records. It does not use the same JobID and ReleaseID and give a new OrderID as i would expect it to.

 

[Shortskimper]

I am re-uploading V1 of the DB with the proper left join and dataentry to Off so that way it acts the way my screenshots show.

 

[ebs17]

I checked the database:

=> In order to understand any connections, my first look goes into the relationship window. Here the view went into emptiness, which in itself is a reason to close and bury the whole thing.

=> I had overcome myself for a moment and still had a quick look at queries and tables, which is exhausting. Tables have lookup fields. This is already problematic in itself, and the reasons for this have been extensively presented in many places.
But there are even lookup fields in tables that refer to tables that do not exist in this DB.

With that, I was finally allowed to break off my attempt to understand. The move to forms would be in vain without understanding the data.

 

[Gasman]

I did not even see that form that is in the above pictures?

 

[Shortskimper]

I checked the database:

=> In order to understand any connections, my first look goes into the relationship window. Here the view went into emptiness, which in itself is a reason to close and bury the whole thing.

=> I had overcome myself for a moment and still had a quick look at queries and tables, which is exhausting. Tables have lookup fields. This is already problematic in itself, and the reasons for this have been extensively presented in many places.
But there are even lookup fields in tables that refer to tables that do not exist in this DB.

With that, I was finally allowed to break off my attempt to understand. The move to forms would be in vain without understanding the data.

"But there are even lookup fields in tables that refer to tables that do not exist in this DB."
I was told to gut everything as I had to shrink the DB small enough to upload, so I did. This is the reason you are seeing that. I removed everything that did not pertain to this issue. It must have removed all relationships. I didn't see that. I will re-establish and upload.

"Tables have lookup fields."
Table lookups are currently being addressed at the moment as soon as Pat flagged me on it.

The form name is frmOrders. I have double checked that it is there. The problem is in the query as it will not allow more than one record to be created. I am less concerned about the form than I am the query. Simplifying and just running the query shows the query does not have the ability to add more than one record when a job and release are picked.

It's very simple.

Pick a job, pick a release number. Run qryOrders. Attempt to put more than one record "via the field orderbody" in qryOrders.

EDIT: I discovered why the relationships were removed when shrinking the DB for upload. I copy / pasted everything in to a new access DB to make sure it was clean and it did not take the relationships with it. Apologies on that.

 

[ebs17]

Attempt to put more than one record in qryOrders

Here is the query on purpose, the form references have been replaced with realistic ID's => this makes the form uninteresting.

SELECT
   tblJobNo.JobID,
   tblReleaseNum.ReleaseID,
   tblReleaseNum.JobNumID,
   tblOrders.OrderID,
   tblOrders.ReleaseNumID,
   tblOrders.OrderBody
FROM
   (tblJobNo
      INNER JOIN tblReleaseNum
      ON tblJobNo.JobID = tblReleaseNum.JobNumID
   )
   LEFT JOIN tblOrders
   ON tblReleaseNum.ReleaseID = tblOrders.ReleaseNumID
WHERE
   tblJobNo.JobID = 2737
      AND
   tblReleaseNum.ReleaseID = 4965

A query with several JOINs is not an Excel table (where you can do EVERYTHING) and in my view cannot be updated or expanded by data records. In exceptional cases, this can be successful with a first data set, but you can't work like this in the long run.

It is safe to practice if you make edits or new additions in exactly one table and this in a separate form. The query then only summarizes the data from the tables including changes.

 

[Shortskimper]

Here is the query on purpose, the form references have been replaced with realistic ID's => this makes the form uninteresting.

SELECT
   tblJobNo.JobID,
   tblReleaseNum.ReleaseID,
   tblReleaseNum.JobNumID,
   tblOrders.OrderID,
   tblOrders.ReleaseNumID,
   tblOrders.OrderBody
FROM
   (tblJobNo
      INNER JOIN tblReleaseNum
      ON tblJobNo.JobID = tblReleaseNum.JobNumID
   )
   LEFT JOIN tblOrders
   ON tblReleaseNum.ReleaseID = tblOrders.ReleaseNumID
WHERE
   tblJobNo.JobID = 2737
      AND
   tblReleaseNum.ReleaseID = 4965

A query with several JOINs is not an Excel table (where you can do EVERYTHING) and in my view cannot be updated or expanded by data records. In exceptional cases, this can be successful with a first data set, but you can't work like this in the long run.

It is safe to practice if you make edits or new additions in exactly one table and this in a separate form. The query then only summarizes the data from the tables including changes.

As i read. I'm having an Ephinay. I don't need the tblJobNo in this query at all. If i only had the left join from tblReleaseNum to tblOrders. Would this allow me to safely add records to tblOrders with the proper ReleaseID? I will test this. More important, is it safe?

 

[The_Doc_Man]

Our member ebs17 has pointed out the same thing I did (related to purity of purpose), but in different words. If you confine your actions to a single table, you should have no trouble augmenting or updating that table. If you try to do too much at once, you invite confusion.

The issue about why you would only get/return one record if you omit the details is because of what all SQL engines really do. They form combinations. They are combinatorial engines. If you have unique-match relationships (i.e. JOIN ... ON T1.X = T2.Y) and any member of the relationship is a prime key, you cannot get a list from that. ONLY if you have tables that are many-to-one will you get multiple combinations. The relationships you were using didn't seem to support that many-to-one - but a detail table where you can have many details dependent on some other field - THAT is how you get back multiple records.

 

[ebs17]

I will test this.

Good idea. In all variants! Own experiences and pain improve the process of understanding.

When manipulating the data in a table, you only have to observe its regulations (uniqueness, validity rules), and of course always the set referential integrity. That's clear, so I look for the relationships and the table definitions first. Those are the basics.

 

[Pat Hartman]

Maybe you should step back and rather than ask us to fix your code/form,

I'm sorry. That came across wrong. Many people do feel they can ask us to build things for them as if we were their employee. You did not do that. But, you did fall into the typical. I did this. It doesn't work. How do I fix it? Which we can sometimes work with but not always. I did figure out what your original problem was and I described the solution. Please review #16. Did that not fix the initial problem? You then asked why you can not add multiple rows to the form and the answer is because it is linked to a specific, single record. So I posted an example of a m-m relationship in #18. Did you examine that? Do you understand the concept of a m-m relationship?

You CANNOT add multiple records to the order because you are adding a row to the order table and the order table has a foreign key that points to ONE record. That's where I mentioned that you might need to bind the order form to the details table rather than ReleaseNum.

The typical Order entry process is a many-many relationship at its lowest level.
tblOrder is the order header and has a FK to the customer
tblOrderDetails is the table that holds the items being ordered. Items NEVER go in the order header table which is what you seem to be doing. tblOrderDetails has two foreign keys. One to its parent table - tblOrder and a second to tblProduct which is the item being ordered. tblOrderDetails is the junction table that connects an order with many items. An order can contain many products and a product can be on many orders = many-many.

You don't have a many-many relationship that I can identify. I know the sample I linked to is not an order entry application so you have to put your thinking cap on to envision how the parts work to create an order. I know lots of people link to the Northwind Traders sample but that is garbage in my opinion. Yes it is sort of an order entry but its schema is poor as are its naming standards and functionality. To translate the m-m sample, open the Employee form. Then think of employee as the order and classes as the items being ordered.

 

[Shortskimper]

I'm sorry. That came across wrong. Many people do feel they can ask us to build things for them as if we were their employee. You did not do that. But, you did fall into the typical. I did this. It doesn't work. How do I fix it? Which we can sometimes work with but not always. I did figure out what your original problem was and I described the solution. Please review #16. Did that not fix the initial problem? You then asked why you can not add multiple rows to the form and the answer is because it is linked to a specific, single record. So I posted an example of a m-m relationship in #18. Did you examine that? Do you understand the concept of a m-m relationship?

You CANNOT add multiple records to the order because you are adding a row to the order table and the order table has a foreign key that points to ONE record. That's where I mentioned that you might need to bind the order form to the details table rather than ReleaseNum.

The typical Order entry process is a many-many relationship at its lowest level.
tblOrder is the order header and has a FK to the customer
tblOrderDetails is the table that holds the items being ordered. Items NEVER go in the order header table which is what you seem to be doing. tblOrderDetails has two foreign keys. One to its parent table - tblOrder and a second to tblProduct which is the item being ordered. tblOrderDetails is the junction table that connects an order with many items. An order can contain many products and a product can be on many orders = many-many.

You don't have a many-many relationship that I can identify. I know the sample I linked to is not an order entry application so you have to put your thinking cap on to envision how the parts work to create an order. I know lots of people link to the Northwind Traders sample but that is garbage in my opinion. Yes it is sort of an order entry but its schema is poor as are its naming standards and functionality. To translate the m-m sample, open the Employee form. Then think of employee as the order and classes as the items being ordered.

I read this Post for at least an hour. and did some experimenting. I HAVE FOUND A WAY! Between you, Doc, and ebs. I thank all of you for having the patience and saying the same thing to me 12 different ways until I was able to fully grasp / understand it!

What I ended up doing -

Locking recordsource for frmOrders to qryOrders
Removing the Jobs table from qryOrders (KISS Method Doc!) as well as using the left join to tblOrders (Credit to you here!).
Creating a subform (that Doc had mentioned as well as you at one point) that pointed directly at tblOrders.

This allows the main form to parse the data. And the subform to use that data (master/child) to properly add more than one record attached to the ReleaseID.

EBS - I was about to give up as I had been attempting to do this for almost 4 days now. Your post about continuing to try new things really did invigorate me to keep trying.

If anyone is interested. I will reload the DB with the form working as intended. Well, sort of. I didn't want a datasheet view. But i'll work on that! The point is, It works! I will look at those lookup fields and attempt to write queries instead of using them as it seems everyone is VERY against them. I don't fully understand why (truthfully ignorant on this and haven't researched it much, But I will).

Thank you all!

 

[Pat Hartman]

You're welcome. It is not always easy for us to explain something when you don't have a point of reference.

I hope you open that m-m sample and examine it carefully. The sample shows the relationship from each direction and it does it using two different techniques since sometimes one visualization makes more sense than the other. Employees -- > Classes uses a main form for the Employee table and a subform to update the junction table. The Classes --> Employees view shows a continuous form for classes. Then if you double click on the Class name, you open the "subform" which is a popup instead and it shows the employees who are taking that class. Because from this direction I didn't use a subform, there is code in the frmClassEmp table to populate the ClassID FK. Access handles populating the FK automagically when you use a subform with the proper master/child links set but you have to populate the FK manually.

There's lots of techniques to point out. Another one is that the validation for the Class relationship uses a common validation procedure. To do this, each form calls valClass and passes in a reference to the form that is calling it so when you call valClass(Me) from sfrmEmpClass, "Me" refers to that form but when you call the code from frmClassEmp.

I made some changes today so if you downloaded the sample earlier, please go and get the newer version.