none
Concurrent Users in adding a new record RRS feed

  • Question

  • Hi,

    I have a form which has 2 sections. 1st section's controls tie to a master table, the 2nd section's control tie to another table which link to the master. In addition, there are 2 table controls which also tie to 2 other tables in the DB. Each record on that form represent one record in the master table.

    I create a Duplicate button which duplicates this whole page to another one and below is the code. I ran into user-locking issue, but I haven't had the form locked. Will 

    Forms("myForm").RecordLocks = 2
    fix the problem? Or should I do it in a different way? Please advice. Your help would be greatly appreciated! 
    DoCmd.RunCommand acCmdSelectRecord
        If (MacroError = 0) Then
            DoCmd.RunCommand acCmdCopy
        End If
        If (MacroError = 0) Then
    '        .AddNew
            DoCmd.RunCommand acCmdRecordsGoToNew
        End If
        If (MacroError = 0) Then
            DoCmd.RunCommand acCmdSelectRecord
        End If
        If (MacroError = 0) Then
            DoCmd.RunCommand acCmdPaste
            'copy other tables within the form using Reocordset | AddNew
    
        End If
    

    Wednesday, August 30, 2017 2:48 PM

All replies

  • Hi,

    Is the lock error happening with a single user on the database? If so, are you making manual changes to the record before executing the code?

    Wednesday, August 30, 2017 2:50 PM
  • The error happens when 2 users are on, I think because both are duplicating the recording with the code above. I don't have any record locking setup at this time, I wonder what kind of setting will fix that issue.

    Thanks in advance.

    Wednesday, August 30, 2017 4:36 PM
  • Okay, I would take your answer to mean the error does not happen if there's only one person making the duplicate record, correct?

    Still, I am not sure I will looking at any specific settings to prevent the problem since you're using code to cause it. I might try modifying the code first to avoid the error before asking Access to do it for me. For example, if the table does not allow duplicate records, then perhaps you can query the table first for the possibility of creating a duplicate before creating the record.

    Just my 2 cents...

    Wednesday, August 30, 2017 4:40 PM
  • I think what was happening is when it executes ".AddNew", both users maybe on the same record. How can that be avoided?

    Please advice.

    Tuesday, September 5, 2017 2:42 PM
  • Hi,

    You can easily test this. Have two users go to the same record at the same time and click on the button. Then, have both users go to different records and click the button at the same time. Do you get the error only one time?

    Besides, .AddNew shouldn't cause a lock because you're creating a new record and not updating an existing one where two users could overwrite each other's changes.

    Just my 2 cents...

    Tuesday, September 5, 2017 2:46 PM