Cannot add record(s); Join key of table is not in recordset

[LizNyx]

Hello,

I was wondering if someone could help me with an issue I have with my form?

Basically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:

Cannot add record(s); join key of table 'OrdersList' is not in recordset.

I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).

I have tried to look at other posts with similar issues but I can't find a solution that works for me.

I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.

The form is 'Orders', which is based on a query called 'Orders List'.

Any help would be greatly appreciated!

 

[ConnorGiles]

Hi Liz!

I've just tried opening your database and it doesn't appear to suit my format

I've had this problem before - can you try uploading without the zip folder?

Maybe that will do the trick!

 

[LizNyx]

Hi Connor,

I've attached it as a normal Access Database. Thanks for looking at this

 

[ConnorGiles]

What version of access are you using Liz?

 

[LizNyx]

Access 2010

 

[ConnorGiles]

could you please use save as to convert it to access 2007 please, I will try my best for you.

or compact and repair it for me

 

[LizNyx]

Hopefully this has worked?
There wasn't an option to convert to 2007 in my Save As file (only had MS Access Database listed).

 

[gemma-the-husky]

anything about search key's join key's etc indicates a possible corruption to me.

copy the database first

then check the table carefully. try and copy the table. I expect the copy will fail because there are corrupt records.

 

[ConnorGiles]

Regretfully that didn't work, Hey maybe its a sign of what Dave has said!

May be a corruption? - try your compact and repair

 

[LizNyx]

Thanks Dave & Connor.

I've tried copying the table and then copying the records directly in - they all copied in fine, or at least there were no error messages or any indications that anything had failed. I also tried creating a new table called OrdersTest, reimported the data from an Excel file, and then changing all the queries to look at OrdersTest instead of OrdersList and I'm still having the same error.

Also Compact and Repair doesn't seem to do too much for the database. I've clicked and while it appears to do something, it's had no effect on that error.

Oh well, sounds like it might be worth starting from scratch again. At least I learn something everything I create a new database!!

Thanks for your help!

 

[gemma-the-husky]

Your query "orderlist" appears to be corrupt.

Rebuild that query.

the table orderslist accepted data, and a rebuilt query accepted data.

 

[LizNyx]

You are an absolute legend! That has worked perfectly!!

 

[gemma-the-husky]

glad you got it fixed

I have this very occasionally.

queries don't work correctly, and they are unfixable. They "look" correct, but don't work right. Offhand, I think I get problems (but only sometimes) when renaming fields in a table, or when copying, and slightly modifying a query for a new version. sometimes they ask for a parameter that just isn't there - but it's a vestige of what was there before.