Text box does not save contents to table or display in listbox

[thomasgist]

After cllicking the save buttong all controls on my form save their contents, however, one text box does not save contents to table or display in listbox.

How might this problem be solved?

 

[pr2-eugin]

Hello Thomas, Welcome to AWF..

Is this form bound to the Table? If so make sure that the Text box that hold the value is bound to the appropriate field in the table..

What do you mean by "display in Listbox"?

 

[thomasgist]

no the form is not bound
when i get the form to save the contents of all controls, then the misbehaving text box's contents will appear in the listbox

 

[thomasgist]

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) 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
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 & " " _
& "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

 

[pr2-eugin]

What is the misbehaving control here?

 

[thomasgist]

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

 

[Sketchin]

If you would care to share, I wouldn't mind seeing the code for your AppointmentsCheck Function.

 

[Sketchin]

CurrentDb.Execute "INSERT INTO tblAppointments (ApptID, ApptSubject, ApptLocation, ApptStart, ApptEnd, ApptNotes, LicensePlateNunber) VALUES (" _

Spelling

 

[thomasgist]

Public Function AppointmentsCheck(vStart As Date, vEnd As Date, vApptID As Long) As Long
'Checks if new appointment overlaps any existing appointment, return 0 if no or return overlapped appointment ID if yes
'Entry vStart = Start date and time of new appointment
' vEnd = End date and time of new appointment
' vApptID = ID of new appointment
'Exit AppointmentsCheck = ID of existing appointment record if new appointment overlaps or 0 if no overlap
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAppointments WHERE ApptID <> " & vApptID) 'make list of appointments except the one being changed
Do Until rst.EOF
If vStart < rst!ApptEnd And vEnd > rst!ApptStart Then 'if new start < existing end and new end > existing start then
AppointmentsCheck = rst!ApptID 'new appointment overlaps this appointment so return ID of this appointment
Exit Do 'no need to check any more
End If
rst.MoveNext 'next record
Loop
rst.Close
Set rst = Nothing
End Function

 

[thomasgist]

LicensePlateNunber is misspelled the same way in the table also

 

[Sketchin]

Did you ever figure this out? I was seeing an error on this line:

& QUOTE & Me.txtLicense & QUOTE & ")" 'and insert new record into tblReservations

till I deleted the comment on the end of this line:
& QUOTE & Me.TxtNotes & QUOTE & ")", "" _