Problems with subforms and Before Update

[load3d]

I have a form with 3 subforms side by side covering the real estate of the entire "Main Form". The data entry occurs in the middle form while the forms on the left and the right are queries that update data that others user are entering in at the same time. This DB has SQL back end.


My problem is that.... when a user..... enters in data and does not click the "save" button, but instead clicks one of the sub forms.... it saves the data...incomplete... or avoids all error checking. I've put the same code that is on my save button into the Before Update event.... and I continue to have problems.

I'm curious to know if anyone can think of a way that when a subform is clicked out of it doesn't save via the before update but instead just throws up an error message saying "You must click the save button".

 

[TheAceMan1]

How are ya load3d . . .

Post the code? ...

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[load3d]

ACE MAN! Hey.... I'm good.. thank you for asking. Here is the code for Before Update... let me know if you need anything else.



Private Sub Form_BeforeUpdate(cancel As Integer)

On Error Resume Next

' \\ ERROR MESSAGES \\
Dim errmsg
Dim errmsg1
Dim errmsg2
Dim errmsg3
Dim errmsg4
Dim errmsg5
Dim errmsg6
Dim errmsg402


Dim Time1 As Date
Dim Time2 As Date
Dim Time5 As Date
Dim Time8 As Date

Dim apptime As Date





'\\ COMBINES ALL 3 TIME SELECTIONS \\
Me.AppointmentTime = Me.Hours & ":" & Me.Mins & ":" & "00" & " " & Me.Tod

'\\ CONVERTS 0 to 00 and 5 to 05 \\
If Me.Mins = "0" Then Me.Mins = "00"
If Me.Mins = "3" Then Me.Mins = "03"
If Me.Mins = "6" Then Me.Mins = "06"
If Me.Mins = "9" Then Me.Mins = "09"



Time1 = Me.AppointmentTime
Time2 = Time()
Time8 = DateAdd("h", 2, Time1)



' \\ COMBINES THE DATE AND TIME \\
ApptDateTime = Me.ApptDate & " " & Me.AppointmentTime

'\\ CONVERTS time5 TO A DATE TIME FIELD \\

Time5 = CDate(ApptDateTime)


'\\ CHECKING HOURS AND TIMES FOR VALIDITY \\'

If IsNull(Me.AppointmentMHE) Then
errmsg2 = MsgBox("Please enter the lift number!", vbOKOnly, "Invalid MHE#")
cancel = -1
Me.Assettag.SetFocus

ElseIf IsNull(Me.Hours) Then
errmsg2 = MsgBox("You need to enter the correct hours", vbOKOnly, "Invalid Hour for Appointment")
cancel = -1
Me.Hours.SetFocus


ElseIf IsNull(Me.Mins) Then
errmsg2 = MsgBox("You need to enter the correct minutes", vbOKOnly, "Invalid time for Appointment")
cancel = -1
Me.Mins.SetFocus

ElseIf IsNull(Me.Tod) Then
errmsg2 = MsgBox("You must specify AM or PM", vbOKOnly, "AM or PM?")
cancel = -1
Me.Tod.SetFocus






ElseIf DCount("*", "sitdowntimes", "[MHE] = '" & Me![Assettag] & "'") = 1 Then Me.Time5 = DateAdd("n", 4, Me.AppointmentTime)

ElseIf DCount("*", "sitdowntimes", "[MHE] = '" & Me![Assettag] & "'") = 1 Then Me.Time6 = DateAdd("n", 8, Me.AppointmentTime)

ElseIf DCount("*", "sitdowntimes", "[MHE] = '" & Me![Assettag] & "'") = 1 Then Me.Time7 = DateAdd("n", 12, Me.AppointmentTime)







'\\ CHECKS TO SEE IF THE USER ID IS A VALID ID IN THE SYSTEM \\'

ElseIf DCount("*", "tbl_UserMaster", "[UserID] = '" & Me![userid] & "'") < 1 Then
errmsg2 = MsgBox("You have entered an invalid user ID. Example: WHXXX177", vbOKOnly, "Invalid User ID")
cancel = -1
Me.userid.SetFocus


'\\ CHECKS TO SEE IF THE APPOINTMENT CONFLICTS WITH A SITDOWN APPOINTMENT \\

ElseIf DCount("*", "SitdownTimes1", "[Time5] = '" & Me![AppointmentTime] & "'") > 0 Then errmsg402 = MsgBox("That time conflicts with a Sitdown appointment - Sitdowns take 16 Minutes", vbOKOnly, "Invalid Time!")
cancel = -1
ElseIf DCount("*", "SitdownTimes1", "[Time6] = '" & Me![AppointmentTime] & "'") > 0 Then errmsg402 = MsgBox("That time conflicts with a Sitdown appointment - Sitdowns take 16 Minutes", vbOKOnly, "Invalid Time!")
cancel = -1
ElseIf DCount("*", "SitdownTimes1", "[Time7] = '" & Me![AppointmentTime] & "'") > 0 Then errmsg402 = MsgBox("That time conflicts with a Sitdown appointment - Sitdowns take 16 Minutes", vbOKOnly, "Invalid Time!")
cancel = -1
' ElseIf DCount("*", "SitdownTimes1", "[Time8] = '" & Me![AppointmentTime] & "'") > 0 Then errmsg402 = MsgBox("That time conflicts with a Sitdown appointment - Sitdowns take 16 Minutes", vbOKOnly, "Invalid Time!")

ElseIf DCount("*", "sitdowntimes", "[MHE] = '" & Me![Assettag] & "'") = 1 Then Call Sitdowns







ElseIf DCount("*", "8hourappts", "[appointmenttime] = '" & Me.Time5 & "'") > 0 Then errmsg402 = MsgBox("The time you selected conflicts with another appointment, Sitdowns takes 16 minutes per battery change", vbOKOnly, "Invalid Time!")
cancel = -1
If DCount("*", "8hourappts", "[appointmenttime] = '" & Me.Time6 & "'") > 0 Then errmsg402 = MsgBox("The time you selected conflicts with another appointment, Sitdowns takes 16 minutes per battery change", vbOKOnly, "Invalid Time!")
cancel = -1
If DCount("*", "8hourappts", "[appointmenttime] = '" & Me.Time7 & "'") > 0 Then errmsg402 = MsgBox("That time conflicts with another appointment, Sitdowns takes 15 minutes per battery change", vbOKOnly, "Invalid Time!")
cancel = -1






'\\ CHECKS IF TIME CONFLICTS WITH ANOTHER APPOINTMENT \\

ElseIf DCount("*", "8hourappts", "[appointmenttime] = '" & Me.AppointmentTime & "'") > 0 Then errmsg402 = MsgBox("An appointment with that time already exists! Please choose another time!", vbOKOnly, "Invalid Time!")
Me.Mins.SetFocus
cancel = -1
'\\ CHECKS IF TIME CONFLICTS WITH A SCHEDULED LOCKOUT TIME \\

ElseIf DCount("*", "scheduledlockout", "[Times] = '" & Me.AppointmentTime & "'") > 0 Then errmsg402 = MsgBox("The battery changer is scheduled to be locked out at this time! Please choose another time!", vbOKOnly, "Invalid Time!")
Me.Mins.SetFocus
cancel = -1

'\\ CHECKS TO SEE IF DATABASE IS LOCKED OUT

ElseIf DCount("*", "tbl_lockoutdatabase", "[inserviceYN]") = 1 Then Forms!newdesign!Frame.SourceObject = "Mainswitchboard"
ElseIf DCount("*", "tbl_lockoutdatabase", "[inserviceYN]") = 1 Then errmsg402 = MsgBox("Appointment creation is currently locked out, please try again when the Appointments are unlocked", vbCritical, "Locked Out")
cancel = -1
'\\ IF THE TIME IS AFTER 10 PM THEN THEN ALLOW APPOINTMENTS TO BE CREATED IN THE PAST \\

ElseIf (Time() > #10:00:00 PM#) And (Time() < #11:59:00 PM#) Then GoTo 55

ElseIf Time1 < Time2 Then errmsg402 = MsgBox("You cannot create appointments that occur in the past!", vbOKOnly, "Invalid Appointment Time!")
cancel = -1
Me.Hours.SetFocus

' CHECK TO PREVENT TIMES FROM BEING CREATED MORE THAN 2 HOURS IN THE FUTURE '


Time11 = Me.AppointmentTime

Time10 = CDate(DateAdd("h", 2, Time()))

ElseIf CDate(Me.AppointmentTime) > Me.Time10 Then
errmsg2 = MsgBox("Appointment can not be scheduled more than 2 hours from the current time!", vbOKOnly, "Invalid time for appointment!")
cancel = -1

55 Forms!newdesign!atehourappointments.Form.Requery