Why is it when I update a table in MS Access using Excel userform, data that need to updated is not-CodePudding

I am creating a userform to update student attendance details. When updating a table in MS Access via Excel userform, the data is not updated. Instead it shows 0 or -1 on attendanceStatus column and nothing in the Excuse columnthis is a picture of my ClassDate table after attempting to update for student IT01 and IT02.

My userform contains values for courseCode, subject, classDate, studentID, name, attendance status, and excuse. Therefore, I will be using the values of classDate, courseCode, and studentID to find the attendance of the student whom I want to update. This is my attempt for the Userform update button to update the attendanceStatus and Excuse column in the MS Access ClassDate table.

Private Sub CommandButton1_Click()

Dim cnt As ADODB.Connection
Dim db_path As String
Dim db_str As String

db_path = "C:\Users\Lenovo\Documents\BIT\SEM4\SAD\StudentAttendanceMonitoring\attendance1.accdb;"
Set cnt = New ADODB.Connection

db_str = "provider=Microsoft.ACE.OLEDB.12.0; data source=" & db_path
cnt.Open (db_str)

insert_str = "update Classdate set attendanceStatus = '" & cmbUpdateStatus.Value & "' and Excuse = '" & txtUpdateExcuse.Value & "' where classDate = '" & cmbUpdateDate.Value & "' and courseCode = '" & cmbUpdateCourseCode.Value & "' and studentID = '" & cmbUpdateStudentID.Value & "'"

Debug.Print insert_str

cnt.Execute (insert_str)

MsgBox "Updated sucessfully", vbInformation

Set cnt = Nothing

End Sub

CodePudding user response：

Should be a comma to separate field update expressions, not and.

insert_str = "update Classdate set attendanceStatus = '" & cmbUpdateStatus.Value & _
                               "', Excuse = '" & txtUpdateExcuse.Value & _
             "' where classDate = '" & cmbUpdateDate.Value & _
                    "' and courseCode = '" & cmbUpdateCourseCode.Value & _
                    "' and studentID = '" & cmbUpdateStudentID.Value & "'"