I have a MySQL database from which I request a recordset of clients, and then I filter the recordset for a specific client based on two fields: 1) client code and 2) year applicable. I then assign values from a userform's objects to the MySQL fields, and update that specific client's records to the database.
I get the following error upon updating the table (running 'rstIT.Update'), and I think it has to do with the way I am implementing the filter:
I get the following error upon updating the table (running 'rstIT.Update'), and I think it has to do with the way I am implementing the filter:
I am sure there must be an easier / more clever way to filter for the correct client, and update the records accordingly. Any help to resolve my current problem or another method would be greatly apppreciated!
VBA Code:
Dim cn As ADODB.Connection
Dim rstIT As ADODB.recordSet
Dim rstClients As ADODB.recordSet
Dim Jaar As String
Jaar = cboTaxPeriod.Text
ITID = lblClientCode.Caption
Application.DisplayAlerts = False
Dim str As String
str = "DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=myserveraddress;DATABASE=mydatabasename;PORT=myport;UID=user_"
str = str & LCase(LoggedInName)
str = str & ";PWD="
str = str & Password
str = str & ";FOUND_ROWS=1;"
Set cn = New ADODB.Connection
cn.ConnectionString = str
cn.Open
Set rstIT = New ADODB.recordSet
With rstIT
.Open "IT", cn, adOpenKeyset, adLockOptimistic, adCmdTable
End With
rstIT.Filter = "ClientCode = '" & ITID & "' AND TaxYear = '" & Jaar & "'"
If rstIT.EOF Then
MsgBox "Client code does not exist in tax table.", vbOKOnly, "Choose new client code"
GoTo GaanUit 'This closes everything and exits
Else
rstIT!TaxStatus.value = LTrim(RTrim(cboTaxStatus.Text))
rstIT!TaxStatusStaff.value = RTrim(LTrim(lblTaxStatusStaff.Caption))
If lblDueDate.Caption <> "" Then
lblDueDate.Caption = Format(lblDueDate.Caption, "yyyy-mm-dd")
rstIT!DueDate.value = lblDueDate.Caption
Else
rstIT!DueDate = Empty
End If
End If
rstIT.Update
