The misbehaving control is txtlicense. It does not save to the database.
Private Sub cmdSave_Click()
'Save appointment data to table
Dim vAppointmentID As Long
Dim vNewStart As Date, vNewEnd As Date
Dim vApptInfo As String
On Error GoTo ErrorCode
'Check if Subject field empty and error if it is
If Nz(Me.txtSubject) = "" Then
Beep
MsgBox "ERROR, you must enter some text in the Subject field before saving the appointment.", vbCritical + vbOKOnly, "Invalid Subject Field"
Exit Sub
End If
vNewStart = Me.txtStartDate & " " & Me.cboStartTime 'combine new start date and time
vNewEnd = Me.txtEndDate & " " & Me.cboEndTime 'combine new end date and time
'Check if new (or amended) appointment overlaps an existing appointment (add in next 6 lines of code if overlapping appointments is NOT required)
' vAppointmentID = AppointmentsCheck(vNewStart, vNewEnd, Me.txtAppointmentID) 'fetch ID of overlapped appt (if any)
' If vAppointmentID > 0 Then 'if function returns value >0 then
' vApptInfo = DLookup("ApptSubject & Chr(13) & Chr(10) & 'Date/Time = ' & ApptStart & ' to ' & ApptEnd", "tblAppointments", "ApptID = " & vAppointmentID)
' MsgBox "ERROR. This new appointment overlaps an existing appointment :-" & vbCrLf & "Subject = " & vApptInfo & vbCrLf & vbCrLf & "Please amend dates and/or times and try again.", vbCritical + vbOKOnly, "Invalid Appointment Times"
' Exit Sub
' End If
'If Me.txtAppointment = 0 then add a new appointment, if Me.txtAppointment > 0 then amend the existing appointment
If Me.txtAppointmentID = 0 Then 'if no existing appointment record found then
If vNewStart < Now Then 'if new appt time is earlier than Now then
Beep
If MsgBox("WARNING. This appointment is in the past, do you really want to create this appointment now?", vbQuestion + vbYesNo, "Invalid Appointment Time") = vbNo Then Exit Sub
End If
vAppointmentID = Nz(DMax("ApptID", "tblAppointments"), 0) + 1 'create new AppointmentID (= highest existing number + 1)
CurrentDb.Execute "INSERT INTO tblAppointments (ApptID, ApptSubject, ApptLocation, ApptStart, ApptEnd, ApptNotes, LicensePlateNunber) VALUES (" _
& vAppointmentID & ", " _
& QUOTE & Me.txtSubject & QUOTE & ", " _
& QUOTE & Me.txtLocation & QUOTE & ", " _
& "#" & Format(vNewStart, "yyyy/m/d hh:nn") & "#, " _
& "#" & Format(vNewEnd, "yyyy/m/d hh:nn") & "#, " _
& QUOTE & Me.txtNotes & QUOTE & ")", " _ 'and insert new record into tblAppointments"
& QUOTE & Me.txtLicense & QUOTE & ")" 'and insert new record into tblAppointments
Else 'if changing an existing appointment record then
CurrentDb.Execute "UPDATE tblAppointments SET " _
& "ApptSubject = '" & Me.txtSubject & "', " _
& "ApptLocation = " & QUOTE & Me.txtLocation & QUOTE & ", " _
& "ApptStart = #" & Format(vNewStart, "yyyy/m/d hh:nn") & "#, " _
& "ApptEnd = #" & Format(vNewEnd, "yyyy/m/d hh:nn") & "#, " _
& "ApptNotes = " & QUOTE & Me.txtNotes & QUOTE & " " _
& "ApptNotes = " & QUOTE & Me.txtLicense & QUOTE & " " _
& "WHERE ApptID = " & Me.txtAppointmentID 'update existing appointment with amended data
End If
gDummy = 1 'return 1 (refresh screen on return)
DoCmd.Close acForm, Me.Name 'and close form
Exit Sub
ErrorCode:
MsgBox Err.Description
End Sub