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

[Shortskimper]

access database engine cannot find a record in the table with key matching. This is the current query. I'm attempting to pull in the picked combobox jobID and releaseID in to the query so the user can then fill out an order form that will be relational to tblReleaseNum.

When running the query. It runs properly. The issue i'm having is that it isn't pulling the ReleaseNumID so when anything is typed in to the form. I get error:

I know either something in my query is wrong (possibly need a different join?) or i've otherwise buggered something. Just need some help in narrowing down WHAT I need to done to pull in the ReleaseNumID to properly have these tables relational to each other! Any help is appreciated. Will post screenshots or load a copy of front/back end on request. Thanks!

ALSO: Don't judge the form. It's still in process but i want to make sure the query is working correctly before continuing!

 

[theDBguy]

When running the query. It runs properly. The issue i'm having is that it isn't pulling the ReleaseNumID so when anything is typed in to the form. I get error:

If the query runs properly, then perhaps there's an issue with the form's design, if you're only getting the error when you enter data on the form. Can you post a sample db?

 

[jdraw]

Would be better if you could post a copy of the database.

OOOops: Too slow again.

 

[Shortskimper]

Sure thing Guys. But it seems the database (zipped) is too large to upload @ ~8MB in size. Options to get it to you?

 

[Shortskimper]

Hopefully links are allowed? Loaded it to google drive.

BOMSystemStriped.zip

drive.google.com

 

[plog]

'it' implies one thing, your link goes to 2 files. Neither of which I can open, just says 'Copy File Name?' when I click on it.

Make this easy on us. Give us just what we need to see your issue. It would be best just to zip whatever file you need and upload it directly with your post.

 

[Shortskimper]

'it' implies one thing, your link goes to 2 files. Neither of which I can open, just says 'Copy File Name?' when I click on it.

Make this easy on us. Give us just what we need to see your issue. It would be best just to zip whatever file you need and upload it directly with your post.

Apologies. The front end and back end are split. I'll combine them and make sure the launched Form is the main form. Apologies, I forgot it ties in to AD to checks group permissions! Please hold!

 

[The_Doc_Man]

It is hard to be sure, so I'm qualifying this as "a guess" ... but:

Looking at the "relationship arrows", it appears that the relationships that you defined are giving you issues, and it is POSSIBLE that relational integrity is getting in your way. It is also possible that you have tried to do too much in one query.

You have three tables in a layered JOIN and imply that you will be adding data relating to the order. However, there will be no record matching up (yet) because you said you will be entering data to one of the participant tables. The form (through its use of the query as a record source) appears to be demanding data that can't be there yet. Probably because the underlying query isn't saying "give me this except if I happen to be entering it." It just says "Give it to me!"

Break up the query behind the scenes to break the dependency expressed by the relationships. If you only use a table for lookup purposes, having it in a recordsource query isn't necessary.

 

[Shortskimper]

Success. Took A LOT of gutting it!

 

[Shortskimper]

It is hard to be sure, so I'm qualifying this as "a guess" ... but:

Looking at the "relationship arrows", it appears that the relationships that you defined are giving you issues, and it is POSSIBLE that relational integrity is getting in your way. It is also possible that you have tried to do too much in one query.

You have three tables in a layered JOIN and imply that you will be adding data relating to the order. However, there will be no record matching up (yet) because you said you will be entering data to one of the participant tables. The form (through its use of the query as a record source) appears to be demanding data that can't be there yet. Probably because the underlying query isn't saying "give me this except if I happen to be entering it." It just says "Give it to me!"

Break up the query behind the scenes to break the dependency expressed by the relationships. If you only use a table for lookup purposes, having it in a recordsource query isn't necessary.

This makes sense to me as to the reason why i'm not seeing my desired results. I suppose i'm just lost on HOW I would break it up in to two queries to properly pull the JobID and ReleaseID I want to start the form off.

The more i read you middle paragraph. I know you stated to break up the query. How would you break the query up to be able to enter data in to the orders table relationaly to the ReleaseNum table?

 

[The_Doc_Man]

If you are looking up something from a combo box, you know it is valid if you found it. You have two lookups to identify two fields. Using the JOIN isn't necessary because the combos are (implicitly) doing validation for you.

There is a principle that falls under the KISS banner: Purity of purpose. You are inputting data from a data entry form. Bind ONLY the table into which data storage is intended and just "fill in the blanks" from the combo results. If you don't have that three-JOIN query as a record source, but rather only have the order table, the query and form will not REQUIRE the data to pre-exist. The fact that you have relationships will not get in the way as long as you supply valid JobID and ReleaseID numbers - which you can do via the data in the combos. The main point of the advice about breaking up the table is because that JOIN query has baggage that you don't need to carry at the moment - particularly if your combo lookup has already supplied your validation.

EDIT: Note also that if you were trying to store both RELEASE info and ORDER info at the same time, your purity of purpose can become more complex. Doing two things at once, when one of the things has a dependency on the other thing, can become problematic because in that case, the order of execution can be EXTREMELY important.

I don't know if that made a lot of sense, because my sentences can get complex. But I think I got the point covered there. You can ask a more specific question if you need to do so. I or my colleagues will certainly try to help.

 

[Pat Hartman]

Use a LEFT join between tables 2 and 3.

Also, your names are confusing.
It is always best to use the same name for your FK as for the PK it points to. Some people like to suffix with _FK for emphasis. So ReleaseNumID becomes just ReleaseID or ReleaseID_FK. Your current names confuse the meaning of ReleaseID and ReleaseNum and between JobID and JobNumID. Is the confusion caused because you are using table level lookups?

 

[Shortskimper]

Use a LEFT join between tables 2 and 3.

Also, your names are confusing.
It is always best to use the same name for your FK as for the PK it points to. Some people like to suffix with _FK for emphasis. So ReleaseNumID becomes just ReleaseID or ReleaseID_FK. Your current names confuse the meaning of ReleaseID and ReleaseNum and between JobID and JobNumID. Is the confusion caused because you are using table level lookups?

Pat, Thank you for the feedback on the names. I will rename appropriately as when I initially picked the naming scheme 8 years ago. I didn't think about possible confusion with other fields in the same table! Thanks again for pointing that out.

In regards to using a left join. That does work. But only for one entry and not a continuous entry with the same unique ID that I would expect from a one-to-many relationship. Is there a way to just left join it and still be able to put in multiple entries based off of the unique ID pulled from the combobox in the criteria section?

 

[Shortskimper]

If you are looking up something from a combo box, you know it is valid if you found it. You have two lookups to identify two fields. Using the JOIN isn't necessary because the combos are (implicitly) doing validation for you.

There is a principle that falls under the KISS banner: Purity of purpose. You are inputting data from a data entry form. Bind ONLY the table into which data storage is intended and just "fill in the blanks" from the combo results. If you don't have that three-JOIN query as a record source, but rather only have the order table, the query and form will not REQUIRE the data to pre-exist. The fact that you have relationships will not get in the way as long as you supply valid JobID and ReleaseID numbers - which you can do via the data in the combos. The main point of the advice about breaking up the table is because that JOIN query has baggage that you don't need to carry at the moment - particularly if your combo lookup has already supplied your validation.

EDIT: Note also that if you were trying to store both RELEASE info and ORDER info at the same time, your purity of purpose can become more complex. Doing two things at once, when one of the things has a dependency on the other thing, can become problematic because in that case, the order of execution can be EXTREMELY important.

I don't know if that made a lot of sense, because my sentences can get complex. But I think I got the point covered there. You can ask a more specific question if you need to do so. I or my colleagues will certainly try to help.

So you're stating because my criteria is considered validation of the Primary keys. It is no longer required to have any form of JOIN for data integrity in the query and i should remove it based on the KISS method. Am i understanding this correctly?

The only thing I want to store in the orders table is the releaseID (primary key) to maintain a relational database linked to the releasenum table.

 

[Shortskimper]

If you are looking up something from a combo box, you know it is valid if you found it. You have two lookups to identify two fields. Using the JOIN isn't necessary because the combos are (implicitly) doing validation for you.

There is a principle that falls under the KISS banner: Purity of purpose. You are inputting data from a data entry form. Bind ONLY the table into which data storage is intended and just "fill in the blanks" from the combo results. If you don't have that three-JOIN query as a record source, but rather only have the order table, the query and form will not REQUIRE the data to pre-exist. The fact that you have relationships will not get in the way as long as you supply valid JobID and ReleaseID numbers - which you can do via the data in the combos. The main point of the advice about breaking up the table is because that JOIN query has baggage that you don't need to carry at the moment - particularly if your combo lookup has already supplied your validation.

EDIT: Note also that if you were trying to store both RELEASE info and ORDER info at the same time, your purity of purpose can become more complex. Doing two things at once, when one of the things has a dependency on the other thing, can become problematic because in that case, the order of execution can be EXTREMELY important.

I don't know if that made a lot of sense, because my sentences can get complex. But I think I got the point covered there. You can ask a more specific question if you need to do so. I or my colleagues will certainly try to help.

I've done some quick and dirty testing Doc. It seems if the relationship between tables isn't there. It won't actually allow you to add new records from the query itself. So I think i'll still have to keep that relationship in play. Would you agree?

 

[Pat Hartman]

The left join isn't preventing multiple entries. I deleted all the extraneous data and there is no data for an order related to any of the Job/Release combinations. So I tried to enter data.

The qryOrders returns the expected records but the form opens to none because it is set to data entry. Once I changed AllowDataEntry to No, the form worked as expected. BUT, it will NEVER show more than ONE record because the tblDetails is not included in qryOrders.

I had to remove the table level lookup to add records to tblReleaseNum -- you should NOT use table level lookups. Leave the combo boxes for forms. They are a crutch in tables and since users NEVER see tables and you know how to write a query, they don't server any purpose but to cause a problem.

 

[Shortskimper]

The left join isn't preventing multiple entries. I deleted all the extraneous data and there is no data for an order related to any of the Job/Release combinations. So I tried to enter data.

The qryOrders returns the expected records but the form opens to none because it is set to data entry. Once I changed AllowDataEntry to No, the form worked as expected. BUT, it will NEVER show more than ONE record because the tblDetails is not included in qryOrders.

I had to remove the table level lookup to add records to tblReleaseNum -- you should NOT use table level lookups. Leave the combo boxes for forms. They are a crutch in tables and since users NEVER see tables and you know how to write a query, they don't server any purpose but to cause a problem.

Wow Pat. I know you just gave me my answer but i'm trying to unpack all of it. I've been self-taught for the last 8 years so there are still some standard definitions that I faulter on and can't piece together. So bare with me.

The left join is the proper join for this. My issue with ONLY being able to add ONE new record to the query(Removing the form for simplicity) is simply because I have to add tblDetails in to the qryOrders query (due to the relationship it has with field "ReleaseID"?) ? Should it be joined as it is in relationships or should it just "be there"? I'm assuming if it does need to be joined. It will require an INNER JOIN (I will do some testing after this post)?

In regards to table level lookups. I'm assuming this is in reference to Combobox I have set up in certain fields. You would prefer that to be handled at the Form level and not the table level? Are there inherent risks I should be aware of by handling that at the table level.

 

[Pat Hartman]

There are lots of threads with us "fixing" problems caused by table level lookups and multi-value fields. They are crutches. With regard specifically to the table level lookups, ANYTHING that obfuscates the actual value stored in a table will confuse you. Table level lookups are just one. Formatting date fields to hide time components is another. If you store the time as part of the date, then do NOT hide it at the table level because when you look at the records you will never be able to figure out why certain records are not being selected when your criteria is something like "Where MyDate = #1/1/2023#". The answer invariably is that they have time and so will never be = to just the date. This is caused frequently by mistakenly using Now() when you really meant to use Date(). If you want to hide the time on a report or form, that is fine. Just format the control that displays the date to show short date which will always conform to your local Windows date format.

I do not understand the purpose of the form that is showing the orders. If you are trying to use it to add details to an order, then it is not built correctly.

I'm going to post a link to a many-many sample database so you can see how to create records in junction tables because this may be what you actually are trying to do.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

 

[Shortskimper]

There are lots of threads with us "fixing" problems caused by table level lookups and multi-value fields. They are crutches. With regard specifically to the table level lookups, ANYTHING that obfuscates the actual value stored in a table will confuse you. Table level lookups are just one. Formatting date fields to hide time components is another. If you store the time as part of the date, then do NOT hide it at the table level because when you look at the records you will never be able to figure out why certain records are not being selected when your criteria is something like "Where MyDate = #1/1/2023#". The answer invariably is that they have time and so will never be = to just the date. This is caused frequently by mistakenly using Now() when you really meant to use Date(). If you want to hide the time on a report or form, that is fine. Just format the control that displays the date to show short date which will always conform to your local Windows date format.

I do not understand the purpose of the form that is showing the orders. If you are trying to use it to add details to an order, then it is not built correctly.

I'm going to post a link to a many-many sample database so you can see how to create records in junction tables because this may be what you actually are trying to do.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

I am indeed attempting to view if orders exist as well as ADD new orders with that form. I will look at that. Thank you.

 

[Shortskimper]

I have combed over your many to many DB and have done quite some googling and research and I can't seem to find a working combination(I believe i'm failing to understand the principle). I appreciate all of the resources you've given me and I will continue to try and get this working. I appreciate the time you've given me.