Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

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

    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 <name> 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
    <span style="background-color: #FFFF00; color: #000000; font-weight: bold">Me.OrderID = DMax("OrderID", "tblUSAOrders") + 1</span hi>
    End If
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Join key of table ' ' not in recordset (Access 200

    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.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join key of table ' ' not in recordset (Access

    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.<tracking Number>, [ShipFirstName] & " " & [ShipLastName] AS ShipFullName, tblUSACustomers.Phone
    FROM tblUSACustomers INNER JOIN tblUSAOrders ON tblUSACustomers.CustomerID = tblUSAOrders.CustomerID
    ORDER BY tblUSAOrders.OrderID;

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Join key of table ' ' not in recordset (Access

    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>post 401925</post#> for instructions.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join key of table ' ' not in recordset (Access

    Here it is.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Join key of table ' ' not in recordset (Access

    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, ...

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join key of table ' ' not in recordset (Access

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

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Join key of table ' ' not in recordset (Access

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

    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.
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Join key of table ' ' not in recordset (Access

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •