We value your feedback.
Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!
Course Of The Month
7 days, 23 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS Access Query Join 2 Tables Together for one Subform Master Child Link and cannot add record Join key of table 'ProdLocations' not in recordset
Posted on 2016-10-25
I have a subform that is made by query of two tables joined. The two tables are "ProdLocations" and "Products". This Subform is Linked to the Mainform by "SkuID". I have most of it figured out but two thing.
1: The field "SkuID" which is found in the "Products" table is not auto populating like it should on a new entry.
2: If I was to click on the field "QtyLoc" it errors with: cannot add record Join key of table 'ProdLocations' not in recordset. BUT if I click on the new record row and then back on "QtyLoc" then it will allow me to enter a Quantity. (Which I believe has to do with the "SkuID" above.
It is putting the information into the tables all correctly accept the "SkuID" in the "Products" Table
I know query joining tables for a subform isn't the best approach but I need a single form to enter fields"Condition ID" "Price" in the Products table and "QtyLoc" into the "Locations Table.
Subform Query:
THANK YOU!
1: The field "SkuID" which is found in the "Products" table is not auto populating like it should on a new entry.
2: If I was to click on the field "QtyLoc" it errors with: cannot add record Join key of table 'ProdLocations' not in recordset. BUT if I click on the new record row and then back on "QtyLoc" then it will allow me to enter a Quantity. (Which I believe has to do with the "SkuID" above.
It is putting the information into the tables all correctly accept the "SkuID" in the "Products" Table
I know query joining tables for a subform isn't the best approach but I need a single form to enter fields"Condition ID" "Price" in the Products table and "QtyLoc" into the "Locations Table.
Subform Query:
SELECT Products.ConditionID, Products.Price, Products.ProductID, Products.ProdConSku, ProdLocations.ProdLocID, ProdLocations.LocID, ProdLocations.QtyLoc, Products.SkuID
FROM Products INNER JOIN ProdLocations ON Products.ProductID = ProdLocations.ProductID
WHERE (((ProdLocations.LocID)=1));
THANK YOU!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
4
9 Comments
because of the 1:M relationship between products and prodlocations, the product must exist before location can be added.
Clicking the 'new record' creates the required (product) record in the clone (ready for insert)
Is the SkuID in products defined as autonum?
Clicking the 'new record' creates the required (product) record in the clone (ready for insert)
Is the SkuID in products defined as autonum?
because of the 1:M relationship between products and prodlocations, the product must exist before location can be added.
My thoughts exactly but I didn't know if there was a work around in a nice clean fashion.
Is the SkuID in products defined as autonum?
It is a autonumber in SKUs table and then a Integer in the Products Table with no default.
the skuid isn't auto populating because it isn't autonum (correct) but a foreign key. You need a drop-down (based on data from the SKUs table) for the user to select this in the subform.
Maybe put the Product fields first in the form?
I'm not convinced your design is sound. It looks as if you should have many products in a location, not many locations for a product. Alternatively, location would be an attribute of product?
Maybe the subform should be just location, and have a dropdown to select the product?
Maybe put the Product fields first in the form?
I'm not convinced your design is sound. It looks as if you should have many products in a location, not many locations for a product. Alternatively, location would be an attribute of product?
Maybe the subform should be just location, and have a dropdown to select the product?
Redesign are you crazy! Seriously though I already started redesigning. I didn't see it working but kept praying it would. Thanks for the help. I could use some of your input though on the redesign. I will tell you want I need and if you could point me in the direction.
When a new product comes in and is entered into the database. I need the user to fill out 3 fields for the two tables.
Products table = ConditionID & Price
ProdLocations table = QtyLoc (Quantity)
Of course Products has to be filled out before Prodlocations and I have beforeinsert codes on the current Subforms for that to block the user and make them do it in a sequence.
There is a unique index on the ( ProductID & LocID).
Location 1 is the default in the ProdLocations and that is basically receiving area.
The user is responsible to enter the quantity they have available. It will default to the receiving location (LocID 1) Automatically.
****If there is more items coming in and or found at a later time while there are still items currently in (LocID 1) then the user needs to be able to increase the quantity in (LocID 1)
What I have currently as my redesign it allows the user (Which I don't Like or want) to add multiple rows of quantity but then errors because of the unique index.
the Subform Products is linked by SkuID to the main form called frmSkusEntry.
The 2nd Subform for Prodlocations is linked to the Products Subform by Child ProductID and Master:
So if you could take that all in and give any suggestions that would be awesome.
I would like the subform (Subforms) to appear to be one combined subform and have only 4 records max showing (One for Each ConditionID: NSOP,NSSP,USOP,USSP) in the products and then Prodlocations to have 4 lines to match for the Quantity in (LocID 1).
Thank you for your time and thought!
When a new product comes in and is entered into the database. I need the user to fill out 3 fields for the two tables.
Products table = ConditionID & Price
ProdLocations table = QtyLoc (Quantity)
Of course Products has to be filled out before Prodlocations and I have beforeinsert codes on the current Subforms for that to block the user and make them do it in a sequence.
There is a unique index on the ( ProductID & LocID).
Location 1 is the default in the ProdLocations and that is basically receiving area.
The user is responsible to enter the quantity they have available. It will default to the receiving location (LocID 1) Automatically.
****If there is more items coming in and or found at a later time while there are still items currently in (LocID 1) then the user needs to be able to increase the quantity in (LocID 1)
What I have currently as my redesign it allows the user (Which I don't Like or want) to add multiple rows of quantity but then errors because of the unique index.
the Subform Products is linked by SkuID to the main form called frmSkusEntry.
The 2nd Subform for Prodlocations is linked to the Products Subform by Child ProductID and Master:
Forms!frmSkusEntry!sbfrmProducts.Form!ProductID
So if you could take that all in and give any suggestions that would be awesome.
I would like the subform (Subforms) to appear to be one combined subform and have only 4 records max showing (One for Each ConditionID: NSOP,NSSP,USOP,USSP) in the products and then Prodlocations to have 4 lines to match for the Quantity in (LocID 1).
Thank you for your time and thought!
because you only want 1 qty for each prod/loc combination you don't want to have multiple rows. The user must 'update' the qty instead of adding a new record.
The alternative design-approach is transaction-based, where users enter 'debits' or 'credit' transactions and you derive a total based on initial value plus sum of debits and credits, This is a bunch of work so I would stay with a single record as you have.
I am signing-off now but suggest you solve the first issue before going to 'One for Each ConditionID'
The alternative design-approach is transaction-based, where users enter 'debits' or 'credit' transactions and you derive a total based on initial value plus sum of debits and credits, This is a bunch of work so I would stay with a single record as you have.
I am signing-off now but suggest you solve the first issue before going to 'One for Each ConditionID'
Active today
Please excuse me if I am off the wall. I didn't try to digest the thread so far BUT - if the form is not populating the foreign key for the subform then -
1. Make sure that the query for the subform is selecting the correct foreign key. You said the subform query uses multiple tables. I don't know if one of those is the parent table. If it is, you should delete the parent table since you don't need anything from the parent table in the subform. If you keep it, you have to select the foreign key from the child table rather than the primary key from the main table since it is the foreign key that Access will populate based on the master/child links.
2. Make sure the master/child links are set correctly.
1. Make sure that the query for the subform is selecting the correct foreign key. You said the subform query uses multiple tables. I don't know if one of those is the parent table. If it is, you should delete the parent table since you don't need anything from the parent table in the subform. If you keep it, you have to select the foreign key from the child table rather than the primary key from the main table since it is the foreign key that Access will populate based on the master/child links.
2. Make sure the master/child links are set correctly.
Featured Post
This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works
The attack works by targeti…
- The Email Laundry
- Email Servers
- Cybersecurity
- Email Protocols
- Network Security
- Security, *Phishing
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month7 days, 23 hours left to enroll
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.