If Statement w/ And and Msgbox Nested

[Pamela]

I am getting a syntax error on this code. The code is intended to check that
the user entered data into the Remarks field if they also selected the text
that goes with the ID # 2 in cbFOLConsistent. ID (AutoNumber,pk) is the bound
control in my cbo but it displays text. I want the Msgbox to display Yes/No
buttons and on Yes set focus to Remarks so that the user is forced to make
their explanation. In the No case, I want the focus set to
Me.Parent!cbFOLConsistent so that the user can change their selection. I
actually had a lot more text in my msgbox but since I was having trouble,
trimmed it to narrow down my mistake so I understand that the box doesn't
make sense as it is. Here's my code:

If Len(Me.Remarks & vbNullString) And Me.Parent!cbFOLConsistent = "2" Then
If MsgBox("If the Facts of Loss are not consistent with the damage,") =
vbYes Then
Me.Remarks.SetFocus

Else
Me.Parent!cbFOLConsistent.Undo

Response = acDataErrContinue

End If
End If
Thanks for your help!
Pamela

 

[Jeff Boyce]

Pamela

If MsgBox("blah, blah, blah", vbYesNo,"Title of msgbox") = vbYes Then

Try checking Access HELP on the MsgBox function... for the complete syntax.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Pamela]

Hi Jeff,
I think my code for the MsgBox isn't the problem but rather the If
statement. I don't get an error, but no matter how I meet the criteria, I
don't get the intended message box to open which makes me think that somehow
my double criteria (If w/ AND) is the problem. Any ideas??

Pamela

 

[Tom van Stiphout]

On Mon, 4 Jan 2010 17:38:01 -0800, Pamela

I think the problem may be with the first line:
If Len(Me.Remarks & vbNullString) And Me.Parent!cbFOLConsistent = "2"
Then

Currently this line means: if there are any remarks and
cbFOLConsistent = 2 then...
Is that what you meant?

Set a breakpoint on the first line, re-run the code, and step through,
carefully inspecting the various values (either by hovering over the
variable name, or by using other debugging windows, or by entering
something like:
?Me.Remarks
or
?Len(remarks & vbNullString)
in the immediate window.
I bet you some values are not what you expected them to be.

-Tom.
Microsoft Access MVP

 

[Pamela]

Thanks for bringing that to my attention. I just try to copy code and mix it
in where and how I think I need it but I can't say I necessarily really
understand how it all works and what it all means.
Any suggestions on how to write the first line so that if Remarks is
empty/null (is blank) AND FOLConsistent is 2 that the msgbox then opens?? It
makes sense that it isn't working based on your synopsis of the first line.
Thanks so much!
Pamela

 

[John W. Vinson]

Any suggestions on how to write the first line so that if Remarks is
empty/null (is blank) AND FOLConsistent is 2 that the msgbox then opens??

Make it

If Len(Me.Remarks & vbNullString)=0 And Me.Parent!cbFOLConsistent = "2" Then

This will explicitly check that Remarks is empty.

 

[Pamela]

After some trial and error, I got it....thanks though!
Here's what worked:
If IsNull(Me.Remarks) And (Me.Parent!cbFOLConsistent = "2") Then
If MsgBox("If the Facts of Loss are not consistent with the damage, an
explanation must be made in Remarks") = vbOK Then
Me.Remarks.SetFocus

Else
Me.Parent!cbFOLConsistent.Undo

Response = acDataErrContinue

End If
End If

 

[Chegu Tom]

Be careful if your tables allow a null string "" in the remarks field
if me.remarks has "" in it, your IF IsNull will not work as you expect.

You can set that in your table design to not allow null string.

 

[Douglas J. Steele]

Or use

If Len(Me.Remarks & vbNullString) = 0 And (Me.Parent!cbFOLConsistent = "2")
Then

That will work whether or not zero-length strings are allowed.

 

[Douglas J. Steele]

Hmm. May want parentheses in there. To avoid the line-wrap problem, let's
try:

If (Len(Me.Remarks & vbNullString) = 0) And _
(Me.Parent!cbFOLConsistent = "2") Then