Join key of table ‘ ‘ not in recordset (Access 200

Topic

New Reply

This form used to work fine for me. Yesterday I was trying to do some programming and realized that when I started with this database I was green enough that I didn’t name my tables correctly, so I changed the names of all my tables to simplify things. I used to have the below-mentioned table named USA Orders. After spending most of the day fixing other problems caused by renaming tables, I came upon this problem. When I try to add a new record in the Orders form, I get this message. “Run-time error ‘-2147352567 (80020009) Cannot add record(s); join key of table ‘tblUSAOrders’ not in recordset”. It gives me a Debug option with the line below highlighted. If I click on Help, I get this message. Cannot add record(s); join key of table not in result set. (Error 3348) This is an unexpected error. Please contact Microsoft Product Support Services for more information. Is it really something messed up? I have another form with the exact same code (different table, same scenario as yesterday) that works perfectly.

Private Sub Form_BeforeInsert(Cancel As Integer)
If IsNull(DMax(“OrderID”, “tblUSAOrders”)) Then
Me.OrderID = 1
Else
Me.OrderID = DMax(“OrderID”, “tblUSAOrders”) + 1
End If
End Sub

Reply | Quote

Replies

What is the record source of the form? If it is a query or an SQL statement, try to edit it, you may find the cause of the error.

Reply | Quote

Here is the SQL view of the query source query. I’ve gone through it and cross checked it with the other form and query that are working and can’t find anything missing. I’m losing time here at work and have been thinking of just manually entering order numbers until I can get this figured out.

SELECT tblUSAOrders.OrderID, tblUSACustomers.CustomerID, tblUSAOrders.OrderDate, tblUSAOrders.ShippedDate, tblUSAOrders.ShipFirstName, tblUSAOrders.ShipLastName, tblUSAOrders.ShipCareOf, tblUSAOrders.ShipAddress, tblUSAOrders.ShipCity, tblUSAOrders.ShipState, tblUSAOrders.ShipPostalCode, tblUSAOrders.ShipVia, tblUSACustomers.Comments, tblUSAOrders.Packages, tblUSAOrders.Donation, tblUSAOrders.Labels, tblUSACustomers.FirstName, tblUSACustomers.LastName, tblUSACustomers.Careof, tblUSACustomers.Address, tblUSACustomers.City, tblUSACustomers.State, tblUSACustomers.PostalCode, tblUSAOrders., [ShipFirstName] & ” ” & [ShipLastName] AS ShipFullName, tblUSACustomers.Phone
FROM tblUSACustomers INNER JOIN tblUSAOrders ON tblUSACustomers.CustomerID = tblUSAOrders.CustomerID
ORDER BY tblUSAOrders.OrderID;

Reply | Quote

Could you have a combo box or list box on the form with a row source you forgot to update?

If you wish, you can post a stripped down copy of your database. See post 401925 for instructions.

Reply | Quote

Here it is.

Reply | Quote

The query qryUSAOrders is not updatable because you have used the CustomerID field from tblUSACustomers. You should use the field from tblUSAOrders instead:

SELECT tblUSAOrders.OrderID, tblUSAOrders.CustomerID, tblUSAOrders.OrderDate, …

Reply | Quote

You saved my day for me! Thanks a lot! It was worth the 1/2 hour to strip the database down.

Reply | Quote

[indent]

---

After spending most of the day fixing other problems caused by renaming tables,

---

[/indent]

Do you know about Rick Fisher’s Find and Replace?

Using this you should be able to rename a table and change all relevant references to it in a few minutes.

Reply | Quote

Thanks for that link. I wondered if there was something like that but I was losing enough time at work I didn’t want to spend more time looking for something like that. Hopefully now I won’t need it. Some people learn the hard way.

Reply | Quote