Microsoft Access - Unable to update more than one record after the first record is successfully updated.

You are making this much harder than it needs to be by using an unbound form.  Use a bound form and put your validation in the form's BeforeUpdate event to have COMPLETE control over whether or not the data gets saved.

What is the primary key of the table you are adding records to?  If it is not an autonumber, how is the pk being generated?

Also, reference form fields using Me. (Me.PhoneNumber).  That also means you don't need to use the .Value property since that is the default property of a control.  This reference gives you intellisense and you tell Access up front where the variable is defined (ME) so it doesn't have to search various libraries.  If you had actually posted the code rather than a picture, I would have fixed it for you.  In the future, post code NOT pictures to get the best help.

I'm a little newer to Access. What is the difference between a bound an unbound form when changing data in a table?

Copy of what the Form looks like.

Text copy of the Code - which is run when clicking the Submit button on the above picture.

Private Sub SubmitPhoneRecord_Click()

    Dim FullName As String
    Dim ClientID As String
    Dim ClientRS As Recordset
   
    If IsNull(PhoneRecordClientName.Value) Then
        MsgBox "Name cannot be empty.", vbInformation, "Error"
        Exit Sub
    End If
   
    If IsNull(PhoneNumber.Value) Then
        MsgBox "Phone number must be at least 7 digits.", vbInformation, "Error"
        Exit Sub
    End If
   
    If Len(PhoneNumber.Value) < 7 Then
        MsgBox "Phone number must be at least 7 digits.", vbInformation, "Error"
        Exit Sub
    End If
   
    If PhoneRecordCompany.Value = "" Then
        MsgBox "You cannot leave the Company field blank.", vbInformation, "Error"
        Exit Sub
    End If
   
    ClientID = PhoneRecordCompany.Value
    FullName = PhoneRecordClientName.Value
   
    Set ClientRS = CurrentDb.OpenRecordset("Client Contacts", dbOpenDynaset, dbSeeChanges)
   
    ClientRS.Edit
    With ClientRS
        .AddNew
        ![id] = ClientID
        ![Full Name] = FullName
        ![Phone Number] = PhoneNumber.Value
        ![Company] = "Other"
    End With
   
    ClientRS.Update
    ClientRS.Close
    Set ClientRS = Nothing
   
    DoCmd.Close acForm, "Add Phone Record", acSavePrompt
End Sub

With a bound form, Access does all the work of retrieving, adding, deleting, and updating records.  Your only code would be the validation code and it would go into the Form's BeforeUpdate event.

The validation code would look  like:

    If Me.PhoneRecordClientName & "" = "" Then
        MsgBox "Name cannot be empty.", vbInformation, "Error"
        Me.PhoneRecordClientName.SetFocus
        Cancel = True
        Exit Sub
    End If
   
    If Me.PhoneNumber & "" = "" Then
        MsgBox "Phone number must be at least 7 digits.", vbInformation, "Error"
        Me.PhoneNumber.SetFocus
        Cancel = True
        Exit Sub
    End If
   
    If Len(Me.PhoneNumber) < 7 Then
        MsgBox "Phone number must be at least 7 digits.", vbInformation, "Error"
        Me.PhoneNumber.SetFocus
        Cancel = True
        Exit Sub
    End If
   
    If Me.PhoneRecordCompany & "" = "" Then
        MsgBox "You cannot leave the Company field blank.", vbInformation, "Error"
        Me.PhoneRecordCompany.SetFocus
        Cancel = True
        Exit Sub
    End If

Use a continuous form so multiple records can be added at one time.  You should also include a type code so you know if the phone is cell, home, work, etc.

Using a subform with the master/child links properly set, you don't have to set the foreign key since Access will handle that for you.   However if you use a separate form, you must in your BeforeUpdate event, populate the foreign key so that the record you are adding will link to the correct customer record.

Switching that will make it save and not lock up after saving? From my understanding of what you said, that's an efficiency and minimizing resources used to achieve the same effect. I'm looking for a fix to stop the database from rejecting subsequent changes.

Thanks for your help. Here is a copy of the design view of the table.

Are there any unique indexes?

If you open the table in DS view, can you add multiple records manually?

Thanks again. If it sounds like I'm dodging questions I'm not, I just came into this project post creation so I'm still learning how it's setup myself. I've blotted out the client information as that's sensitive information, but I hope this explains what you were asking. I am able to edit this directly.

Did you look at the indexes?  Are there any that are unique?

Not quite sure what you mean by Indexes, but if you're talking about the unique identifier for this table, the ID is the company identifier. From my understanding, the table is organized and searched by Company ID. (if we pull up x company, it pulls up all records on that table related to the company ID, or X)

Tables can have indexes in addition to their primary keys and those indexes can be unique.  Open a table in design view and press the Indexes button on the Design ribbon.

You cut off the relevant part.  We know the ID is going to be defined as unique.  Click on company and look at the properties.

Client ContactsCompany is not primary or unique, however the one shown below is.

You are showing the properties for the first index which I already said that we knew would be unique.  As I already said:

Click on company and look at the properties.

OK.  It only took a day to find out that there is no index causing the problem.  I'm out of ideas.  Please post the database with just the form and table with a couple of records in it.

Are you wanting the Access Database or the SQL database?

It wasn't clear that the data was in SQL Server.  In order to upload your database, you would also need to create a separate database for the data tables and I'm sure that in the process, the problem would disappear.  I would suggest that you start a GIG but GIGs are just instand messages and I think someone will have to use something like GoToMeeting to actually log into your computer to look at the database using SSMS to find the issue.  I don't take GIGs so it can't be me but you might find someone who can help.

This is a system already in place. Access handles updating the SQL side for us, the issue is that once I make changes the SQL database is locked because it knows we changed something and doesn't unlock itself. I need to know how I can unlock that.

This is not normal behavior.  There is nothing in your code that should cause a permanent lock.  Once the rs.update is executed, the record is saved and that frees the lock.

That's why it's so baffling to me that this is causing an issue. I'm correctly adding the data to the table an updating the database (including the SQL side). I verified another person using this on another computer can see my changes directly after I make them, but for some reason if I run this command more than once, I get an issue ODBC--Call Failed when the ClientRS.Update happens.

Maybe the form is corrupted.  Try rebuilding the form.  I also suggest you use my initial suggestion of using a bound form.  It's a lot less work in the long run.

You're welcome