Results 1 to 13 of 13
  1. #1
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116

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

    Hi,



    I was just trying to improve one of my forms this morning, and I've hit a stumbling block. I can upload the database if necessary, but I'm hoping it's something simple you can help without it first.

    Basically, I changed the form so that it communicates with a query, instead of the table. I have a button that when you click on it, it takes you to the following code. The code worked perfectly before I tampered with the form! I'm sure it's self explanatory, but it should add the information from the form to the query, and the EngagemenText_PK is one number higher than the previous.

    Code:
    Private Sub Command6_Click()
    
    
        Dim x, y, test As Integer
        Dim rs As DAO.Recordset
        
        DoCmd.GoToRecord , , acNewRec
        
        Set rs = CurrentDb.OpenRecordset("SELECT EngagementsText_PK FROM Q_Engagements", dbOpenSnapshot)
        
        With rs
            .MoveLast
            x = Nz(![EngagementsText_PK], "")
            x = Int(x + 1)
        
        If Len([EngagementsText_PK] & "") = 0 Then
            Me.EngagementsText_PK = x
        End If
    
    
        Date_of_Remit.SetFocus
        
        End With
    
    
    End Sub


    It brings up the error message "
    Cannot add record(s); join key of table ‘T_Engagements’ not in recordset".

    I'm not sure why that's happening, or how to solve it.

    Many thanks in advance for any help.

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    2,009
    I would have thought it was easier and probably quicker to use a DMax() that open a recordset.

    Anyway - your problem is that it can't find T_Engagements in your underlying forms query.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks Minty. I'm not sure why it's trying to find T_Engagements anymore though. I thought I'd changed everything to communicate with Q_Engagements instead. Maybe I missed something? I'll check again.

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,950
    If you use AUTONUM field, you don’t need any programming and it always creates a key.

  5. #5
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks, but I need to be very specific with my Engagements Text number. That part is working great!

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    1,205
    Quote Originally Posted by neilsolaris View Post
    Thanks, but I need to be very specific with my Engagements Text number. That part is working great!
    Yes, but the autonumber is for linking records, nothing else. You can have whatever Engagements Text number format you want.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  7. #7
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by Welshgasman View Post
    Yes, but the autonumber is for linking records, nothing else. You can have whatever Engagements Text number format you want.
    I see. I'll bear that in mind.

  8. #8
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,233
    number of issues I can see -

    x and y are not typed so are a variant datatype, only test is dimmed as integer

    you are then potentially setting x to text with this line - x = Nz(![EngagementsText_PK], "") - you could use x = Nz(![EngagementsText_PK], 0) to set it to a number

    and then you are trying to add 1 - but you can't add text, only concatenate - and then convert to a number

    You could use
    x = Int(x + "1") to add 1 to the end so ABB becomes ABB1

    or if they are all numbers you would use x=int(x)+1

    And you are aware that records are returned randomly? so your movelast will not necessarily be the highest number (even less likely if it is text since 10 will come before 2) so you need to order it

    Overall, seems a very complex way of doing something very basic

  9. #9
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Thanks Ajax,

    I had no idea about that, I thought I was setting x, y and test as an integer. So in future I need to write something like Dim x as integer, dim y as integer etc?

    That's useful to know how to how to set x as a number.

    I was aware (or at least I thought) that with tables, the records are arranged in order of entry. But are they actually random? But with a query, if I've set the query to a particular order, is that the order that the records will appear in, when I use my VBA to add another one?

    Thinking about it, I could order the records in date order, as one of the fields is a date.

    In any case, I think I need to rework this, based on yours and others advice. Maybe I can post a sample database for some specific help with it.

    Many thanks.

  10. #10
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Here's a sample from my database. I'd like to incorporate your ideas that you've suggested. I'd also like to learn how to do it, so feel free to describe what I should do, and I'll try to incorporate the changes myself, if possible.

    Thanks again.
    Attached Files Attached Files

  11. #11
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Here's the same database, but I've inserted an Autonumber in the T_Engagements table, as recommended.
    Attached Files Attached Files

  12. #12
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    9,233
    Only on my phone right now but yes re dim

    With regards order, records are stored randomly and a query can mess this order up further if it is complex as it will fetch records in a way that is efficient for the query. Chances are with a small dataset this won’t be apparent but get a few hundred…… so always specify an order with your query if order matters. Don’t bother when it doesn’t as it takes time, not a lot but every millisecond counts. And don’t bother if the query is for a report as reports ignore query orders and you specify them in the report design

  13. #13
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Quote Originally Posted by Ajax View Post
    Only on my phone right now but yes re dim

    With regards order, records are stored randomly and a query can mess this order up further if it is complex as it will fetch records in a way that is efficient for the query. Chances are with a small dataset this won’t be apparent but get a few hundred…… so always specify an order with your query if order matters. Don’t bother when it doesn’t as it takes time, not a lot but every millisecond counts. And don’t bother if the query is for a report as reports ignore query orders and you specify them in the report design
    Many thanks for the info. I'm not back on my computer until tonight, but I can try any ideas then.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-25-2016, 10:58 AM
  2. Replies: 2
    Last Post: 10-28-2014, 01:10 AM
  3. Replies: 6
    Last Post: 07-24-2014, 04:01 PM
  4. Replies: 18
    Last Post: 06-01-2013, 02:26 PM
  5. Replies: 5
    Last Post: 09-19-2011, 12:01 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums