Jul 10 2009, 04:59 PM
Access newbie here on a quest for knowledge!
I'm working on a database that involves tests and logging the test results. One of my test types has 3 subtypes. I need to know what the subtype is because each subtype corresponds to a different append query that needs to be run.
Because this is the only test type that has subtypes, I am creating special code to handle that, so I started by popping up a form in dialog mode, turning off the navigation controls, and prompting the user for the information.
I have a text box to prompt for a date, and I also have an option group for the user to pick one of the 3 test subtypes.
Because I need to confirm the user actually selects a subtype before running the rest of the code, I added a 4th option button, turned its Visible property off, and made its value the default for the option group. Now I want to run an If/Then check, and if the option group value is the default, pop a message box to prompt the user to pick one of the other 3 values.
I did a lot of searching on the Internet, and I actually came across a case where someone was asking this exact question, except on a regular, non-dialog mode form doing data entry. The suggested solution involved a line where Cancel = True, and that didn't make any sense to me, so I searched some more and that turned up that you can only use Cancel = True/False in a BeforeUpdate event, not a Click event. Looking at code online, I also see a bunch of variations on how people handle message boxes, and I'm confused.
Sometimes I see
MsgBox("Your message here")
Other times I see
MsgBox "Your message here"
Still other times I see
intResponse = MsgBox("Your message here")
Call Msgbox("Your message here")
Then sometimes the buttons (like vbOKCancel) are explicitly called out and other times they aren't, and did I say I'm confused?!?
Obviously, I'm interested in a solution to my immediate problem of the best way to do a message box on a form in dialog mode, but I'm probably *MORE* interested in knowing if someone can recommend a web page or other reference about all these little ins and outs about when to use the parentheses and when not to, what the Cancel = True thing is all about, and other tips and techniques for using message boxes.
Jul 10 2009, 05:08 PM
How do you allow the user to close the popup form? If you have a "Close" button that they click after inputing the date and/or the subtype, then you can use it's click event to test for the default value. You won't need the Cancel=True in the BeforeUpdate event. For example:
Private Sub cmdClose_Click()
If Me.OptionGroupName = DefaultValueYouCreated Then
MsgBox "Please select one of the subtypes.", vbCritical, "Missing Subtype"
Hope that helps...
Jul 10 2009, 05:14 PM
And just an FYI for you regarding the usage of the parentheses:
When you call a procedure directly without having to assign its value to something, you call it without parens:
MsgBox "Hello", vbInformation, "Test"
But if you need to return a value then you have to have the parens because it is on the right of the equation:
If MsgBox("Are you sure?", vbQuestion + vbYesNo, "Confirm") = vbYes Then ...
And if you use the word CALL to explicitly state that you are calling a procedure then you would use parens:
Call Msgbox("Hello", vbInformation, "Test")
Hopefully that helps.
Jul 10 2009, 05:16 PM
Now, to answer your other questions:
Some events in Access can be cancelled. Examples are BeforeUpdate and Unload
So, if you want to test for something before commiting the change to a table, you could use the BeforeUpdate event. And if you want to test for something before allowing the form to close, you can use the Unload event.
Once you set the Cancel argument to True, that event will be abandoned by Access and all other codes after it will not run.
The MsgBox() is a function that can return a value. Basically, if you don't need to capture that return value, you can use the format:
MsgBox "Your message here"
However, if you need to check for the return value, which could be a result of which button the user clicked on the message box, then you could use the format:
intReponse = MsgBox("Your message here", ButtonOptions)
Hope that helps...
EDIT: Oops, I guess it took me too long to type that. Sorry for the duplicate info.
Edited by: theDBguy on Fri Jul 10 18:16:59 EDT 2009.
Jul 10 2009, 05:18 PM
Hey just good reinforcement. Plus you showed a use that I had referred to but didn't show (with the value assignment on the left).
Jul 10 2009, 05:30 PM
Sorry about that, I knew I using the OnClick event, so a BeforeUpdate/Cancel = True won't work me.
I have both a Submit and a Close button in the form header (the Close btn is in case they misclicked and didn't actually wanna go there). I'm running the check for the default option group value for the OnClick event of the Submit button.
The code I have so far for the check is:
'Make sure a non-default value is selected for the Option Group
If Me.optEscNotSubTestTypes.Value = 4 Then
MsgBox "You must select an Escalation/Notification subtest type before proceeding.", vbCritical, "Selection Required..."
'Do nothing; just continue processing code.
I don't think I want to close the dialog form, because that's an extra click for the user if they just spaced out the option group selection. But I was wondering how to programmatically close the form at the very end of the rest of the code I have on the Submit button. So thanks!
Jul 10 2009, 05:35 PM
You would use the End Sub inside your If/Then code only if there are other things going on in your subroutine that you didn't want to execute.
Otherwise, I already gave you the code to close the form when the check fails, i.e.
Does that answer your question?
Edited by: theDBguy on Fri Jul 10 18:36:17 EDT 2009.
Jul 10 2009, 05:46 PM
Ooh, great information both of you, just the thing to help unconfuse poor me! Thank you, thank you, thank you!
Yes, I have *LOTS* of other code on my Submit button's OnClick event, the option value check is just a small piece, and if all the other stuff runs, it's bad news, so I definitely don't want to execute it.
But yes, DBGuy, you definitely answered my question.
Have a great weekend, everybody!
Jul 10 2009, 05:48 PM
You're very welcome. Bob and I are happy to help. Good luck with your project.
Jul 10 2009, 07:05 PM
I doubt if you will ever see this: -
MsgBox("Your message here")
We often see this: -
MsgBox ("Your message here")
You can do this: -
MsgBox "Your message here", , "Message Box Title"
But you can not do this: -
MsgBox ("Your message here", , "Message Box Title")
MsgBox (“Your message here”) is telling the editor/compiler to evaluate the stuff enclosed between the parentheses before it is passed to the MsgBox function.
This just happens to work because “Your message here” can be evaluated to a string.
That string can then be sent to the MsgBox function ByVal.
MsgBox ("Your message here", , "Message Box Title") will fail at the editor stage because "Your message here", , "Message Box Title" can not be evaluated.
In this instance what is being passed are three arguments, not just one, and they can not be evaluated.
If we stick the Call word, in front of the lines using parentheses, the space between the MsgBox and the opening parentheses will be lost.
The Call is forcing the MsgBox to be called as a function and the meaning of the parentheses changes from a forced evaluation to simply the enclosure of the argument list.
The Call word then simply trashes any possible return value.
I guess the moral of the story is to watch out for that space, it modifies the intension of the parentheses.
Jul 11 2009, 12:00 AM
Does it count if I saw it? Thanks for the extra info.
Jul 13 2009, 09:51 AM
Your clarification about the space modifying how the parentheses operate and also your point about Call making a return value impossible are exactly the sort of things I was looking for.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here