Field on form not keeping focus

[Guest]

I am having trouble setting my Customerid field on my form to stay as the
focus. The if statements all execute.

When I choose vbYes in the MsgBox (“You Must Enter a Customer to Continue",
vbQuestion + vbYesNo <> vbNo) the procedure simply carries on to the next
statement.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNothing(Me!CustomerID) Then
If MsgBox("You Must Enter a Customer to Continue", vbQuestion +
vbYesNo <> vbNo) Then
CustomerID.SetFocus
Cancel = True
Else: Exit Sub
End If

End If


If MsgBox("Save changes?", vbOKCancel) <> vbOK Then
Me.Undo
MsgBox "Changes were not Saved."
Cancel = True
End If
End Sub

Any help would be appreciated

 

[Allen Browne]

Replace
IsNothing
with
IsNull

Nothing refers to an object variable that is unset.
Null refers to a field/control that has no content.

More info on how to work with nulls:
http://members.iinet.net.au/~allenbrowne/casu-12.html

 

[Guest]

Allen

Thanks for the reply. I have checked out your website and the nulls info
was very helpful. However I changed the Isnothing to a IsNull and still no
luck. The programme simply ignore the first procedure statements and execute
the following


Thanks

Ronnie

 

[Allen Browne]

Set a breakpoint by pressing F9 at the first line in the procedure.

Then run it. It will stop highlighting the breakpoint line.
Open the Immediate window (Ctrl+G), and enter:
? Me.CustomerID

Does it respond with Null?
Does CustomerID have a value?
Can you make sense of what the code is doing now?

 

[Guest]

I have followed your procedure and the value returned was 0. I traced this
back to the table design where default value was set to Zero. I have now
removed this, so customerid now has a null.

The original problem however still remains. I was under the impression that
when you set a cancel to true in the procedure, the focus of the cursor
remains with the field on the form. This however goes from one procedure to
another and does not return the focus to the Customerid field when it is Null.

If IsNull(Me!CustomerID) Then
If MsgBox("You Must Enter a Customer to Continue", vbQuestion +
vbYesNo <> vbNo) Then
CustomerID.SetFocus
Cancel = True
Else: Exit Sub
End If

End If


If MsgBox("Save changes?", vbOKCancel) <> vbOK Then
Me.Undo
MsgBox "Changes were not Saved."
Cancel = True
End If
End Sub

Thanks Ronnie

 

[Allen Browne]

Good. Glad you traced it. That default zero for foreign key fields is a real
nuisance.

The focus will stay in the CustomerID field if you cancel the BeforeUpdate
event of that control, i.e. CustomerID_BeforeUpdate.

That's not the case with the BeforeUpdate event of the form. The current
record stays current and is not saved if you cancel Form_BeforeUpdate. You
can SetFocus to a different control in Form_BeforeUpdate, as in your
example.

 

[Guest]

Allen

I see what you are saying about the before_update event of a form, or a
control. As I am under the impression that validating data should be done on
a form before_update, as it can cause user frustration if done on individual
controls, I have updated my before update procedure as below. Although this
procedure works I just want to pick your brains if you have time.

In the if cancel = true or if cancel = false statements, whichever msgbox
option I choose I still get the default access message saying “Database has
encountered an error, etc. Do you want to close Database anyway? Yes/No.

Is there a way of removing this access check, or is it still required by the
access program, as it will confuse a user who chooses yes or no, to continue
but this message appears. Or do I simply need a better method of coding
these checks

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate

If IsNull(Me!CustomerID) Then
MsgBox "Customer name required", vbInformation + vbOKOnly, "Customer
Name"
CustomerID.SetFocus
Cancel = True
End If

If IsNull(Me!EmployeeID) Then
MsgBox "Employee name required", vbInformation + vbOKOnly, "Employee
name"
EmployeeID.SetFocus
Cancel = True
End If

If IsNull(Me!AppointmentWith) Then
MsgBox "Person to see required", vbInformation + vbOKOnly, "Person
to See"
AppointmentWith.SetFocus
Cancel = True
End If

If IsNull(Me!RepSelection) Then
MsgBox "The name of a Rep is Required", vbInformation + vbOKOnly,
"Rep Name"
RepSelection.SetFocus
Cancel = True
End If

If Cancel = True Then
If MsgBox("Do you want to continue?", vbYesNo) <> vbYes Then
Cancel = True
Else: Exit Sub
End If

If Cancel = False Then
If MsgBox("Save changes ?", vbYesNo) <> vbYes Then
Me.Undo
MsgBox "Changes were Saved."
Else
If MsgBox("Save changes ?", vbYesNo) <> vbNo Then
DoCmd.Save
End If
End If
End If
End If

Exit_Err_Form_BeforeUpdate:
Exit Sub

Err_Form_BeforeUpdate:
MsgBox Err.Description
Resume Exit_Err_Form_BeforeUpdate
End Sub

Thanks Ronnie

 

[Allen Browne]

If you receive another message before your Form_BeforeUpdate message, then
yes: the engine is validating the field.

You will have to deal with that one first. You can trap it in Form_Error,
but it may be better to remove the requirement from the field in the table
(e.g. not mark it as Required).

 

[Guest]

Thanks Allen

I have been away for a short time so only just got back to this. I removed
the field for required access but made no difference to what I was trying to
achieve. Also tried to trap error in the onform error event, but I do not
know enough about it. As I am not too concerned at this moment as I haven’t
got the time, I am leaving this topic. Just thought I would thank you for
your help

Thanks.