Stop form closing after error checking

[liam sheerin]

Hi
i am fairly new to vba and have a taxi booking database.
every time you book a job the cust_name field must be filled out and same if account job for account_ref and account_password fields but everytime the message box appears and you select ok the form closes
does any body no of a solution the code for the ok button is:

Sub ClickOKButton()
 
Dim rsJobs As DAO.Recordset
Dim rsCust As DAO.Recordset
Dim NewCustId As Long
Dim FromLoc
 
'***Error checking
If IsNull(cust_name) Then
  MsgBox "Name must be entered"
  cust_name.SetFocus
  Exit Sub
ElseIf Len(cust_name) < 2 Then
  MsgBox "Name must be minimum of 2 characters"
  cust_name.SetFocus
  Exit Sub
ElseIf cboCashAccount = "Account" Then
  If IsNull(account_reference) Then
    MsgBox "Account reference must be entered because this is an account job"
    account_reference.SetFocus
    Exit Sub
    ElseIf cboCashAccount = "Account" Then
      If IsNull(account_password) Then
      MsgBox "Account password must be entered because this is an account job"
      account_password.SetFocus
      Exit Sub
    End If
  End If
End If
'***Error checking end
 
 
'Add customer record if new customer
NewCustId = 0
If Not chkExistingCustomer Then 'match not found
  Set rsCust = CurrentDb.OpenRecordset("CUSTOMERS", dbOpenDynaset)
  rsCust.AddNew
  If Left(phone_number, 2) = "07" Then
    rsCust("mobile_phone") = phone_number
  Else
    rsCust("home_phone") = phone_number
  End If
  rsCust("cust_name") = cust_name
  rsCust("premises") = premises
  rsCust("house_number") = house_number
  rsCust("flat") = Flat
  rsCust("street") = street
  rsCust("area") = area
  rsCust("town") = town
  rsCust("postcode") = postcode
  rsCust("account_reference") = account_reference
  rsCust.Update
  rsCust.MoveLast
  NewCustId = rsCust("cust_id")
Else 'existing customer
  Set rsCust = CurrentDb.OpenRecordset("SELECT * FROM CUSTOMERS WHERE cust_id = " & cust_id, dbOpenDynaset)
  rsCust.Edit
  If Left(phone_number, 2) = "07" Then
    rsCust("mobile_phone") = phone_number
  Else
    rsCust("home_phone") = phone_number
  End If
  rsCust("cust_name") = cust_name
  rsCust("premises") = premises
  rsCust("house_number") = house_number
  rsCust("flat") = Flat
  rsCust("street") = street
  rsCust("area") = area
  rsCust("town") = town
  rsCust("postcode") = postcode
  rsCust("account_reference") = account_reference
  rsCust.Update
End If
Set rsCust = Nothing
 
'Add job record
Set rsJobs = CurrentDb.OpenRecordset("JOBS", dbOpenDynaset)
rsJobs.AddNew
If NewCustId = 0 Then 'existing customer
  rsJobs("cust_id") = cust_id
Else 'new customer
  rsJobs("cust_id") = NewCustId
End If
rsJobs("when_logged") = when_logged
rsJobs("when_needed") = when_needed_Date & " " & Format(when_needed, "hh:nn")
If from_location = to_location Then
  FromLoc = from_location & " (W/R)"
Else
  FromLoc = from_location
End If
If cboVehicleType <> "car" Then
  rsJobs("vehicle_type") = cboVehicleType
End If
rsJobs("from_location") = FromLoc
rsJobs("to_location") = to_location
If Not cboDelay = "ASAP" Then
  rsJobs("mins_add_delay") = cboDelay
Else
  rsJobs("mins_add_delay") = 0
End If
rsJobs.Update
Set rsJobs = Nothing
 
End Sub

how can i cancel the code after the error checking keeping the form open to make the changes
i have tried the docmd.cancelevent and cancel=true but cant sort this out p
please can someone help

thanks liam

[gershwyn]

How is the code being called? I assume it is the result of the clicking on a button somewhere. Is there any other code being executed as well? I don't see anything in the code you posted that would cause the form to close.

[liam sheerin]

Sorry the code was being called here:

Private Sub cmdOK_Click()
 ClickOKButton
 
  DoCmd.Close
  Form_Jobs.Requery
 
End Sub

I have put the error checking in this section and removed it from the job section.it now looks like this:

Private Sub cmdOK_Click()
 
 '***Error checking
  If IsNull(cust_name) Then
   MsgBox "Customer Name Must Be Entered !", vbQuestion, "CustomerName"
   cust_name.SetFocus
       Exit Sub
  ElseIf Len(cust_name) < 2 Then
    MsgBox "Name must be minimum of 2 characters", vbQuestion, "Customer Name"
    cust_name.SetFocus
    Exit Sub
  ElseIf cboCashAccount = "Account" Then
    If IsNull(account_reference) Then
        MsgBox "Account reference must be entered because this is an account job", vbQuestion, "Account Reference"
        account_reference.SetFocus
        Exit Sub
        ElseIf cboCashAccount = "Account" Then
    If IsNull(account_password) Then
        MsgBox "Account password must be entered because this is an account job", vbQuestion, "Account Password"
        account_password.SetFocus
        Exit Sub
        End If
        End If
  End If
  '***Error checking end
  ClickOKButton
 
  DoCmd.Close
  Form_Jobs.Requery
 
End Sub

It has sorted the problem i had the form now stays open bit of a strange one really.
thanks for your help
liam

[NeoPa]

I can't see how this code would produce the result you describe. Are you simply clicking on an OK button on your form? Or is something else going on we're not told about?

You may want to look at Debugging in VBA to see if you can narrow down where we should be looking for the problem.

[NeoPa]

Ah. It seems we overlapped in our posts.

From that information it's quite obvious what the problem was. You call ClickOKButton then, regardless of the outcome, you close the form with DoCmd.Close. Hardly surprising that it closes then ;-)

[liam sheerin]

Been stressing me out for a couple weeks now then its one of those problems you sit back and look at and you can see the problem
thanks again for all your inputs and help
liam

[NeoPa]

No worries Liam. I'm glad it's sorted anyway.

[Lysander]

I use form validation code a lot and there is one other thing you might want to consider.

ATM, your code validates the form and either saves and closes if valid, or leaves the form open if invalid to correct.

All our data is entered by data-entry operators and if say, using your example, the account password is just not available, they have to close the form somehow, and move onto the next record.

All of our data entry forms have a button with the following code (titled 'Close without Saving')

If Me.Dirty Then
    If MsgBox(strTranslate("TR000000132", "Closing this form will lose all changes. Are you sure?"), vbYesNo) = vbYes Then
        Me.Undo
        DoCmd.Close
    Else
        Exit Sub
    End If
Else
    DoCmd.Close
End If