Form closes after mssage box

[liam01752]

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
please can someone help
sorry forgot to mention the other piece code from the ok button which might explain a bit more
Private Sub cmdOK_Click()
ClickOKButton

DoCmd.Close
Form_Jobs.Requery
End Sub

thanks liam

[ssanfu]

Is there a reason the code in "Sub ClickOKButton()" is not in "Private Sub cmdOK_Click()"??
Is "Sub ClickOKButton()" called from other subs??


----------
Also note:

You should explicitly close the recordset before you set it to Nothing:

rsCust.Close
Set rsCust = Nothing


You do know that a table is just a "bit bucket"? When you enter a new record, it doesn't necessarily get added as the "last" record in the table. There is no order in a table. So when you use code like:

rsCust.Update
rsCust.MoveLast
NewCustId = rsCust("cust_id")

NewCustId might not be the record Id you think it should be.

When you add a new record (as in rsCust.AddNew), if the field is an autonumber type, Access increments the ID field. You could get the "NewCustId" number then. Or you could use the " .LastModified" property to move to the last record added, then get "NewCustId".