Cannot Add Record Join Key of Table Not in Recordset

[MarkDownMark]

Hi all,

Fairly new here and I'm having issues trying to figure out why all of a sudden a db I have and was working is no longer working. The database is something I created for my wife to save her time to figure out her cost of goods for soaping products she makes. I created it several months ago on an older version of Access and after some testing (2010 & 2013) it was working but we never really used it until today. I went in and cleared out all of the tables and wanted to start from scratch. We added the ingredients and went to start making different recipes and this is where I was getting the "Cannot Add Record Join Key of Table Not in Recordset" error when listing ingredients for each recipe. I've looked and I can't seem to figure it out since I'm still pretty new to Access.

If you click on the New Recipe button and then go back to one of the recipes, you can then try to add an ingredient below. When clicking on the ingredient, it gives the error. I feel that it is tied to the Recipe Name not auto populating based on the recipe you're on but I don't understand how to get it to do that.

I've attached the database so you can open it up and see what's happening.

Any and all help is greatly appreciated. Thank you all!

 

[RuralGuy]

You may need to supply a more detailed path to the error as I'm not currently getting any errors.

 

[RuralGuy]

Okay, now I'm getting a problem. Will investigate.

 

[MarkDownMark]

Okay, now I'm getting a problem. Will investigate.

OK good...I was about to throw my computer out the window! Thanks for looking into it for me.

 

[RuralGuy]

Hmm...3rd thread this week using Lookup Fields.
http://access.mvps.org/access/lookupfields.htm
I'll see if it is fixable.

 

[MarkDownMark]

Sorry...I'm not that advanced at Access and in my head, this was the easiest way to do what I needed it to do. It worked for quite some time and when I tried it today, nope. What is a good way to get around what I have done?

 

[RuralGuy]

Not really sure yet but a LOOKUP FIELD is really a Multi-Valued field and so you can't really tell what is there under normal circumstances. Having a Combo Box on a form is just fine and the preferred way to do it. Having a "ComboBox" in a table can be a nightmare. The solution may be as simple as just changing the table to a Text Box. The ComboBoxes on your forms will remain and should still work, but you can then see what is in your table. If you try that, do it on a backup and not the live system.
Here's the instructions in pictures: http://btabdevelopment.com/how-to-remove-lookups-at-table-level/

 

[sneuberg]

I'd remove the Receipt table from the record source query of the Recipe SubForm with CPI. I can't see how that makes sense have a table in a subform record source when the parent's record source is the same table.

 

[MarkDownMark]

Not really sure yet but a LOOKUP FIELD is really a Multi-Valued field and so you can't really tell what is there under normal circumstances. Having a Combo Box on a form is just fine and the preferred way to do it. Having a "ComboBox" in a table can be a nightmare. The solution may be as simple as just changing the table to a Text Box. The ComboBoxes on your forms will remain and should still work, but you can then see what is in your table. If you try that, do it on a backup and not the live system.
Here's the instructions in pictures:

RG, I tried that at it still gives me the same error. Do you think I should just do away with the RecipeLines table all together and just use a ComboBox? But then how can I add multiple ingredients to a single recipe? That's really why I have that RecipeLines table since I don't know of another way to do it.

 

[MarkDownMark]

I'd remove the Receipt table from the record source query of the Recipe SubForm with CPI. I can't see how that makes sense have a table in a subform record source when the parent's record source is the same table.

I was using that to tie the ingredient to the actual recipe. I removed it and it didn't seem to change anything.

 

[RuralGuy]

As I said, Combo Boxes on a form are just fine! Just not at the table level.

 

[MarkDownMark]

RG, right. I got that. But how do I get it to create a new combo box once it's filled with the first ingredient since each recipe will surely have more than 1 ingredient?

 

[RuralGuy]

Use a Combo Box Wizard for the Ingredient Field and just show the name but save the ID value.

 

[MarkDownMark]

I've tried that but to add multiple ingredients and then save them for each recipe doesn't work for me. I'll try to keep figuring this solution out because I know that it worked at one point in time.

 

[RuralGuy]

I tried a Bill Of Materials (BOM) database before and finally gave up. I don't remember why now so I'll play with your database some more.

 

[MarkDownMark]

Thanks RG. I'm striking out miserably.

 

[RuralGuy]

I'd remove the Receipt table from the record source query of the Recipe SubForm with CPI. I can't see how that makes sense have a table in a subform record source when the parent's record source is the same table.

In tried that and still have a Read Only Recordset but thanks.

 

[RuralGuy]

Thanks RG. I'm striking out miserably.

Let's keep this thread open for a few days and see what we find.

 

[sneuberg]

In tried that and still have a Read Only Recordset but thanks.

Removing the Receipt table from the record source query of the Recipe SubForm with CPIis all I did to the attached database and it seems to be working. Where are you getting the Read Only Recordset?

 

[MarkDownMark]

So that gets rid of the problem I was having but now that has introduced a slew of other problems. I want each recipe to hold all of its unique ingredients per recipe. The way you have it, each ingredient is not unique to it's recipe. And then when I look at the recipe details page, that's now not working as advertised.

I'm sorry if I sound ungrateful because I'm not. I'm extremely grateful that you are helping me and taking time out of your day to look at my problem. I just wanted you to know that.