Cannot Add Record - Join Key of tblMaster not in RecordSet

[dipique]

This is probably a simple fix, but I'm new to Access and pretty fried from doing a lot of work in an unfamiliar tool.

I have a massive system that I've developed for tracking inventory, and there's an "Items" table that needs to be added to. Certain columns contain the primary keys of other tables through a normal lookup type function.

I have a query that patches all this together, creating a table with all the text values rather than just the keys; however, I can't add to that table because I get the error "Cannot Add Record(s) - Join Key of [table] not in RecordSet." I've fiddled and searched but I haven't been able to come up with a solution.

I boiled down my problem to an ultra-simple database; it's attached here. You can duplicate the error by opening the only query and trying to add a record.

I'd appreciate assistance; thanks in advance!

Dan

testDB.accdb
testDB.mdb

[orange]

Not everyone has Ac2007 or 2010 so can not read an accdb. You may get more responses if you post an mdb version of your database.

[dipique]

I've edited to include an MDB. Thanks for the tip!

[orange]

That's a very sparce Inventory database.

Getting your table structures and relationships correct is key to database.
Your tables and relationships must "accurately" model your business.
Here are some free video tutorials that describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

The first few topics at this site are well worth the read.

http://www.rogersaccesslibrary.com/forum/topic238.html

Good luck with your project.

[dipique]

Orange,

I appreciate the resources, but as you might imagine I don't have the time to pursue general education in the hopes that I will at some point understand my problem. My goal is to get this project taken care of, and then get me some formal learnin. At which time your links will be very helpful.

In the meanwhile, can anyone point out the issue with the DB I attached? I can also upload my actual DB if that would be helpful, but the attached DB is much simpler and represents the issue well.

Thank you,

Dan

[orange]

It isn't common for the ITEMS table to have a number of links to other Tables. Please describe what you have in plain English.

The data model in the sample videos deals with Customer , Order, LineItems and Items.

Can you tell us in real terms what Master represents, and ITEMS because your sample use Master and Things?

[dipique]

Sure. Master represents the Items table. Things represents a set of options--for example, colors. Black, white, red, and blue. Any item in the Master table could be black, white, red, or blue.

However, I don't want the Master Table to contain those words, only references (via Row ID) to the Items table.

Dan

[dipique]

I would still like to understand why this didn't work, but in the meanwhile I solved my problem. It only took a couple lines of code to add items directly to the Items table instead of adding them to the query that brings in all the lookup values, so that's what I did. It ain't pretty but it works.

[orange]

Please post your solution so others may benefit.

[dipique]

There's really nothing to post. Based on the DB I uploaded, the issue was not being able to add records to the query. So I stopped adding records to the query and instead just added records to the "Master" table from my form, looking up the "Thing" values in code. I'll post my actual code here, though of course it goes with my full DB and not the example I uploaded.

Public Sub AddItemWithMFG(MFG As String, ModelNumber As String, HasUniqueID As Boolean, IsPhysical As Boolean)
   'Create my database as a variable
   Dim dbInventory As DAO.Database
   Set dbInventory = CurrentDb
   
   'Create my Items table as a variable
   Dim rstItems As DAO.Recordset
   Set rstItems = dbInventory.OpenRecordset("Items")

   'Add a new item to my Items table
   rstItems.AddNew
   
   'Look up ID associated with MFG value.  This is what I was trying to use the query for.
   Dim MFGID As Integer
   MFGID = DLookup("ID", "MFGList", "MFG='" & MFG & "'")
   rstItems("Manufacturer").Value = MFGID
   
   'Add more fields...
   rstItems("ModelNumber").Value = ModelNumber
   rstItems("HasUniqueID").Value = HasUniqueID
   rstItems("IsPhysical").Value = IsPhysical

   'Update the table
   rstItems.Update
End Sub

I tried to simplify and clarify the code a bit, but you get the idea. Instead of having the query look up the manufacturer, I just did it in code so it could be added directly to the Items table.

Dan