editing all fields in an access row based off of primary key-CodePudding

My work uses a userform in excel as the front end module for an Access database. I want to make it possible to edit ALL fields in a purchase order based off of the unique transaction ID (primary key) in that database. So far, the code I use seems to randomly update some records and not others without giving any errors. the Access db is a .mdb file (2003). I am wondering if there is a command to move from field to field during edit, but can't seem to come up with a reason for why you would need it??

``
Sub EDIT_PO()

    On Error GoTo ErrorHandler
    
    Application.EnableCancelKey = xlDisabled
    
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Dim nConnection As New ADODB.Connection
    Dim nRecordset As New ADODB.Recordset
    
    Dim sqlQuery As String
    
    'Connection Strings - Dynamic Path

    #If Win64 Then
        nConnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & "C:\Users\samue\OneDrive\Desktop\Database1.mdb"
    #Else
        nConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\Users\samue\OneDrive\Desktop\Database1.mdb"
    #End If
    
    
    sqlQuery = "Select * from PO_TABLE"
    
    'Open the recordset
    
    nRecordset.Open Source:=sqlQuery, ActiveConnection:=nConnection, CursorType:=adOpenKeyset, LockType:=adLockOptimistic
    
    
If nRecordset.Fields("Transaction ID").Value = CStr(POform.poformtransid.Value) Then
    With nRecordset
        .Fields("PO Number").Value = POform.poformponumber.Value
        .Fields("PO Date").Value = CDate(POform.poformpodate.Value)
        .Fields("Status").Value = POform.poformstatus.Value
        .Fields("Material ID").Value = POform.poformmatid.Value
        .Fields("Unit Cost").Value = CDbl(POform.poformunitcost.Value)
        .Fields("QTY").Value = CDbl(POform.poformamount.Value)
        .Fields("QTY Units").Value = POform.poformunits.Value
        .Fields("Vendor ID").Value = POform.poformvendorid.Value
        .Fields("Receipt Date").Value = CDate(POform.poformreceiptdate.Value)
        .Fields("Supporting File").Value = POform.poformsf1.Value
        .Fields("Lot Identifier").Value = POform.poformlotinfo.Value
        .Update
        .Close
    End With

End If
    nRecordset.Close
    nConnection.Close
    
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    Exit Sub
    
ErrorHandler:

    MsgBox Err.Description & " " & Err.Number, vbOKOnly   vbCritical, "Database Error"
    
   
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
     nConnection.Close

End Sub

``

CodePudding user response：

You just open table, so any record may be the first. Try filtering for the specific record you wish to edit:

sqlQuery = "Select * from PO_TABLE Where [Transaction ID] = '" & CStr(POform.poformtransid.Value) & "'"

or, if the ID is numeric:

sqlQuery = "Select * from PO_TABLE Where [Transaction ID] = " & CStr(POform.poformtransid.Value) & ""