Excel userform edit database table

[Don Quixote]

I have an excel userform where I can search for and create new database entries in an ms access table.

But when I try to edit existing entries it gives me an error that the object doesn't support this action.

here is my code so far:

Private Sub EditButton_Click()

    Dim con As Object
    Dim rs As Object

    Set con = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Dennis\Documents\Blending & Filling\Basis Olie Lossing\Base Oils.accdb;"
    'Open Db connection
    con.Open
    Set rs.ActiveConnection = con
    
    rs.Open "Select * from [Planning] where [Bestelbon] = " & UserForm1.txtBestelbon.Text & ""
    'rs.Open "[Planning]", con, 1, 3, 2
    
    'rs.CursorLocation = adUseClient
    'rs.CursorType = adOpenStatic
    'rs.LockType = adLockBatchOptimistic
    
    rs.Edit
    rs.Fields("Productnaam") = UserForm1.txtProduct.Value
    rs.Fields("Transporteur") = UserForm1.txtTransporteur.Value
    'rs!Productnaam = UserForm1.txtProduct.Value
    'rs!Transporteur = UserForm1.txtTransporteur.Value
    rs.Update
    rs.Close
    
    Set rs = Nothing
    con.Close
    Set con = Nothing

End Sub

I would use the sql query to find a matching value of the one in txtBestelnummer textbox, and then edit the other fields in that row.

 

[MikeVol]

Hello @Don Quixote.
Try next code:

Private Sub CommandButton1_Click()
    Dim dbPath As String, SQL As String
    Dim cnn         As New ADODB.Connection
    Dim rst         As New ADODB.Recordset
    dbPath = "C:\Users\Mike\Documents\DB_Copy.accdb"   ' Replace with your path to the .accdb file

    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    If Me.TextBox0.Value <> "" Then
        SQL = "SELECT * FROM Planning WHERE Id = " & Me.TextBox0.Value
    Else
        SQL = "SELECT * FROM Planning Where Id = 0"
    End If

    rst.Open SQL, cnn, adOpenKeyset, adLockOptimistic

    If rst.RecordCount = 0 Then
        rst.AddNew
    End If

    rst.Fields("Productnaam").Value = Me.TextBox1.Value
    rst.Fields("Transporteur").Value = Me.TextBox2.Value

    rst.Update

    MsgBox "Updated Successfully", vbInformation
    Call UserForm_Initialize
End Sub

I hope I helped you this time too. Good luck.

 

[Don Quixote]

Again, it worked perfectly, thx a lot