If...Then Code not working

[Pamela]

I have a cbo on my form and I want it to be that if the user tries to skip
it, a message box opens instructing the user to enter "None" as opposed to
just leaving it blank and so return focus to that cbo for that entry. I
don't get an error on my code, but I don't get the message box either.
Here's my code:
Private Sub ShopName_Exit(Cancel As Integer)
If Me.ShopName = Null Then
MsgBox ("Please make a selection from the list." & vbCrLf & "Enter ""None""
if the owner has not chosen a shop.")
Me.ShopName.SetFocus
End If
End Sub
Thanks so much!
Pamela

 

[Arvin Meyer [MVP]]

Instead of:

If Me.ShopName = Null Then

try:

If Len(Me.ShopName & vbNullString) = 0 Then

Even better, why bother making them enter "None"? Just do it for them. So
I'd do this:

If MsgBox("Are you sure there's no shop?", vbYesNo, "Enter Shop") = vbYes
Then
Me.ShopName = "None"
Else
Me.ShopName.SetFocus
End If

 

[Gina Whipp]

Hmmm, thinking out loud here... Suppose the End-User goes right past that
field and never stops there? You could set the Default Value as 'None" and
then if they do pass it by at least it's not blank. And then put a message
letting them know the Shop is None before proceeding to the next record.
Not sure what your forms are like so not sure where to tell you to put the
message...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[John W. Vinson]

I have a cbo on my form and I want it to be that if the user tries to skip
it, a message box opens instructing the user to enter "None" as opposed to
just leaving it blank and so return focus to that cbo for that entry. I
don't get an error on my code, but I don't get the message box either.
Here's my code:
Private Sub ShopName_Exit(Cancel As Integer)
If Me.ShopName = Null Then
MsgBox ("Please make a selection from the list." & vbCrLf & "Enter ""None""
if the owner has not chosen a shop.")
Me.ShopName.SetFocus
End If
End Sub
Thanks so much!
Pamela

The Exit event will fire *only* if the user enters the ShopName control in the
first place, and then goes somewhere else. It won't fire if the user simply
skips over this control.

The Form's BeforeUpdate event is your best bet for making the user enter
required fields:

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Len(Me!ShopName & "") = 0 Then
MsgBox "Please select a value for ShopName", vbOKOnly
Me.ShopName.SetFocus
Cancel = True
End If
End Sub

If "None" is an appropriate default... then by all means make it the default,
rather than forcing your user to type it!

 

[Pamela]

That is wonderful!! I think that's the hardest part about being so green in
Access -- I don't even know the possibilities! It makes perfect sense and
is a great answer but the possibility just escaped me. Thank you, Thank you!!

Pamela

 

[Arvin Meyer [MVP]]

That because I didn't mention that:

If Len(Me.ShopName & vbNullString) = 0 Then

should be used in the form's BeforeUpdate event.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Gina Whipp]

Arvin,

Wasn't *picking* on you was just thinking out loud...

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm