Force an entry into a field on a form based on another field

[mpm32]

Hi, this seems simple and I have tried several things to get it to work.

I have a field on a form that's a dropdown box. What I want to happen is when the option "Other Companies" is selected, it forces an entry to be made in the comments field. I also want to have a message box come up telling them to do so.

I have tried;

Private Sub Reason_AfterUpdate()

If (Me!Reason) = "Other Companies" Then
MsgBox ("Comments must be entered",vbOKOnly,,,) As VbMsgBoxResult

End if

Its expecting an equal sign. I don't think that the If statement should be used here to call the MsgBox. I tried having it call a Macro with a MsgBox but it expected an equal sign as well.

Can anyone help?

Thanks in advance.

 

[lespaul]

I think your problem is actually the As VbMsgBoxResult. I believe this is making the MsgBox return a value and the program is expecting you to assign that return value. So something like:

ReturnValue = MsgBox ("Comments must be entered",vbOKOnly,,,) As VbMsgBoxResult

I think if you change that to:

Private Sub Reason_AfterUpdate()

If (Me!Reason) = "Other Companies" Then
MsgBox ("Comments must be entered",vbOKOnly,,,)

End if
End Sub

you should be ok.

HTH

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[mpm32]

Hmmm, thanks for the suggestions. I tried it and I now get a compile error.

I'll keep trying.

 

[SgtJarrow]

You can also drop any commas in a function/sub where the parameters are optional and you are not providing values.

So

MsgBox ("Comments must be entered",vbOKOnly,,,)

can be written as

MsgBox ("Comments must be entered",vbOKOnly)

That might help.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[PHV]

Why not simply this ?
MsgBox "Comments must be entered", vbOKOnly

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mpm32]

Excellent, that worked. Thanks PHV.

Sometimes I make things too complicated. And since it prompts you for each thing after MsgBox, I always try to fill everything in.