VBA/ADODB How to change/update value in access database

Hello!

I have a userform in excel that is linked to access database. Whenever I search for the employee/SAP ID# of staff, the userform retrieves all details and allows me to update them, except for the employee/SAP ID# itself. What I want is to have the option also to change the employee/SAP ID in the database.

Thanks in advance for the help!

Here's my code:

In userform there are 6 textboxes. Textbox1 is what needs to be searched.

Dim SAP, sName, pCode, sCode, Mgr As String
     
SAP = TextBox6.Text
sName = TextBox2.Text
pCode = TextBox3.Text
sCode = TextBox4.Text
Mgr = TextBox5.Text

conn.Open

With cmd
    .ActiveConnection = conn
    .CommandText = "SELECT * FROM dbase where SAP_ID = " & SAP & ";"
    .CommandType = adCmdText
End With

With rs
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd
End With

    Do While Not rs.EOF
        rs("SAP_ID").Value = SAP
        rs("Staff_Name").Value = sName
        rs("Station_Code").Value = sCode
        rs("Position_Code").Value = pCode
        rs("Line_Manager").Value = Mgr
       rs.Update
       rs.MoveNext
       MsgBox "Update successful!", vbInformation, ""
    Loop
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

Solved it!

My error was I used the value in textbox6 as my search criteria and not the value in textbox1.

Just fyi, my final code now is:

Dim i, SAP, sName, pCode, sCode, Mgr As String
     
i = TextBox1.Text
SAP = TextBox6.Text
sName = TextBox2.Text
pCode = TextBox3.Text
sCode = TextBox4.Text
Mgr = TextBox5.Text

With cmd
    .ActiveConnection = conn
    .CommandText = "SELECT * FROM dbase where SAP_ID = " & i & ";"
    .CommandType = adCmdText
End With

With rs
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmd
End With

    Do While Not rs.EOF
        rs("SAP_ID").Value = SAP
        rs("Staff_Name").Value = sName
        rs("Station_Code").Value = sCode
        rs("Position_Code").Value = pCode
        rs("Line_Manager").Value = Mgr
       rs.Update
       rs.MoveNext
       MsgBox "Update successful!", vbInformation, ""
    Loop
End If
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing

I'd guess that's because SAP_ID is a Primary Key, you shouldn't ever change a primary key that's why primary keys are usually just incrementing numbers that don't actually hold any relevance to the data in the record.

In your database, I suggest adding another field and making it the primary key and removing this from the SAP ID field

Scrap the above, it appears it wasn't a PK after all