Go Back   Access World Forums > Microsoft Access Discussion > Macros

 
Reply
 
Thread Tools Rating: Thread Rating: 48 votes, 5.00 average. Display Modes
Old 11-01-2010, 11:07 AM   #1
MyTech
Access VBA
 
Join Date: Jun 2010
Location: New York
Posts: 108
Thanks: 11
Thanked 2 Times in 2 Posts
MyTech is on a distinguished road
Run Yes/No MessageBox from MACRO

If I select MessageBox in a Macro, I only get a single "Ok" button in the Message Box.

How can I get a Yes/No Message Box by using MACRO (not VBA)?

MyTech is offline   Reply With Quote
Old 11-01-2010, 12:01 PM   #2
MyTech
Access VBA
 
Join Date: Jun 2010
Location: New York
Posts: 108
Thanks: 11
Thanked 2 Times in 2 Posts
MyTech is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Thanks,

I'm in middle on trying to use the RunCode function.

RunCode: MsgBox("My Message",4)

Yes/No Dialog Box appeared successful.

Now I want to use "If" function to either continue or cancel running the Macro.

How does the "If" argument have to look about? Or maybe a different approach is needed?
MyTech is offline   Reply With Quote
Old 11-02-2010, 08:48 AM   #3
AccessJunkie
Senior Managing Editor
 
AccessJunkie's Avatar
 
Join Date: May 2006
Location: Rogue Squadron
Posts: 277
Thanks: 0
Thanked 69 Times in 45 Posts
AccessJunkie will become famous soon enough
Re: Run Yes/No MessageBox from MACRO

Hi,

Could you please let us know which version of Access you are using? That will definitely help people replying to your question.

To answer your original question, yes you can certainly display a Yes/No message box from a macro and then take different action on what button is clicked.

In Access 2010, conditional logic in macros is way easier than previous versions, but it is still possible in previous versions; it's just a little more tricky.

Here's a quick example using Access 2007 in case you are using that version.

For the first line of this sample macro, enter this in the Condition column:

6<>MsgBox("Please click one of the buttons.",52)

In the Action column on the first time, use the MsgBox macro action and display a message that says "You clicked No."

On the second line of this macro, enter three dots ... in the Condition column to apply the condition from the previous line. In the Action column for the second line, use the StopMacro action.

On the third line of this sample macro, don't put anything in the Condition column. In the Action column of this third line, use the MsgBox macro action and display a message that says "You clicked Yes."

Save the macro and then run it to try it out.
You'll get a Yes/No message prompt that asks you to click a button. If you click the No button, you'll see a message box saying you clicked No and then the macro stops. If you click Yes, you'll see a message box saying you clicked Yes and then the macro stops.

In the sample example I provided, I used Yes/No buttons, a warning message box (the one with the exclamation point icon), and I was testing to see if they clicked the Yes button. You can modify this to present different options using the MsgBox function.

Hope that helps,

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------

AccessJunkie is offline   Reply With Quote
The Following 7 Users Say Thank You to AccessJunkie For This Useful Post:
bcmarshall (01-19-2017), dshank (07-18-2013), Gasman (09-21-2017), nikhilstephen (02-28-2012), NorthShoteTiger (06-06-2011), RachelC (09-17-2013), Snowflake68 (10-06-2015)
Old 11-03-2010, 07:27 AM   #4
MyTech
Access VBA
 
Join Date: Jun 2010
Location: New York
Posts: 108
Thanks: 11
Thanked 2 Times in 2 Posts
MyTech is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Thank you Junkie

I have 2010, I followed your instructions starting with an IF action, everything went nice and dandy.

May I ask you: It's interesting to me, in CheckBoxes I know that "Yes"/"No" stand for "0"/"1" or "-1"/"0",
From your example I learnt that in the MessageBox "Yes" stands for "6", not "0" or "1" or "-1" as assummed.

Why?
MyTech is offline   Reply With Quote
Old 11-03-2010, 01:53 PM   #5
AccessJunkie
Senior Managing Editor
 
AccessJunkie's Avatar
 
Join Date: May 2006
Location: Rogue Squadron
Posts: 277
Thanks: 0
Thanked 69 Times in 45 Posts
AccessJunkie will become famous soon enough
Re: Run Yes/No MessageBox from MACRO

Hi,

Thanks for the version number, that's very helpful.

If you're using Access 2010, designing macros in the new Logic Designer is way easier than previous versions. As you've already commented, you can use the new If/Else/Else If constructs right inside the macro logic. This is very helpful when doing conditional logic within macros.

On to your next question:
Quote:
May I ask you: It's interesting to me, in CheckBoxes I know that "Yes"/"No" stand for "0"/"1" or "-1"/"0",
From your example I learnt that in the MessageBox "Yes" stands for "6", not "0" or "1" or "-1" as assummed.

Why?
Excellent question. I had a feeling you were going to ask that.

When you're using the MsgBox function, you can customize what buttons show, what icons show, which button is the default, and get a return value for the button that was clicked by the user.

Here is a table of the return values right out of my 2007 and 2010 books for you:
Return Values for the MsgBox Function
1 OK button clicked
2 Cancel button clicked
3 Abort button clicked
4 Retry button clicked
5 Ignore button clicked
6 Yes button clicked
7 No button clicked

Does that help?

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
AccessJunkie is offline   Reply With Quote
The Following User Says Thank You to AccessJunkie For This Useful Post:
bcmarshall (01-19-2017)
Old 11-04-2010, 02:35 PM   #6
MyTech
Access VBA
 
Join Date: Jun 2010
Location: New York
Posts: 108
Thanks: 11
Thanked 2 Times in 2 Posts
MyTech is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Sure it helps!

Thank you for the satisfying answer, and for sharing your knowledge with others.


Quote:
Originally Posted by AccessJunkie View Post
If you're using Access 2010, designing macros in the new Logic Designer is way easier than previous versions.
Well, I got some problems with the new macro design in 2010.
After creating a product, customer couldn't run the Macro in 2007, I had to change the macro to VBA.. and there's no option of Save As previous version..
MyTech is offline   Reply With Quote
Old 11-08-2010, 07:41 AM   #7
Dairy Farmer
Newly Registered User
 
Join Date: Sep 2010
Location: S of E, E of GM
Posts: 244
Thanks: 1
Thanked 21 Times in 11 Posts
Dairy Farmer is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Quote:
Originally Posted by MyTech View Post
After creating a product, customer couldn't run the Macro in 2007, I had to change the macro to VBA.. and there's no option of Save As previous version..
Be professional, use Runtime and Developer.

Quote:
The Access Runtime is a redistributable program that allows people who do not have Access installed on their computers to use Access database applications. When you open an Access database by using the Access Runtime, the database opens in runtime mode.


The following Access features are not available in runtime mode:

Navigation Pane
The Navigation Pane is not available in runtime mode. This helps prevent users from accessing arbitrary objects in your database application. Only those objects that you expose to users for example, by providing a switchboard form can be opened while using runtime mode. You cannot make the Navigation Pane available in runtime mode.

The Ribbon
By default, the Ribbon is not available in runtime mode. This helps prevent users from creating or modifying database objects, and from performing other potentially harmful actions, such as connecting to new data sources or exporting data in ways that you do not intend. You can create a custom Ribbon, and then associate that Ribbon with a form or report. You cannot expose the default Ribbon tabs in runtime mode.

Design view and Layout view
Design view and Layout view are not available for any database objects in runtime mode. This helps prevent users from modifying the design of objects in your database application. You cannot enable Design view or Layout view in runtime mode.

Help
By default, integrated Help is not available in runtime mode. Because you control what functionality is available in your runtime mode application, some of the standard integrated Access Help may be irrelevant to people who use your application, and could potentially confuse or frustrate them. If you are using the Access Developer Extensions to package and deploy your database application, you can provide a custom Help file with your runtime mode application.

Dairy Farmer is offline   Reply With Quote
The Following 2 Users Say Thank You to Dairy Farmer For This Useful Post:
DonMac (02-23-2017), nikhilstephen (02-28-2012)
Old 06-06-2011, 06:08 AM   #8
NorthShoteTiger
Newly Registered User
 
Join Date: Jun 2011
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
NorthShoteTiger is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Thanks to you Mr. Junkie! This is exactly what I needed.
NorthShoteTiger is offline   Reply With Quote
Old 07-19-2011, 07:02 PM   #9
Madlom
Newly Registered User
 
Join Date: Jul 2011
Posts: 4
Thanks: 1
Thanked 0 Times in 0 Posts
Madlom is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

How would you build in 2010 with the macro builder...kinda stumped on this one....


Nevermind just did it in VBA instead...

Last edited by Madlom; 07-23-2011 at 06:40 AM.
Madlom is offline   Reply With Quote
Old 01-26-2012, 04:45 AM   #10
Padwan
Newly Registered User
 
Join Date: Jan 2012
Location: Savannah Georgia
Posts: 28
Thanks: 7
Thanked 1 Time in 1 Post
Padwan is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Mine uses vba but when you click yes it opens a form and when you click no it just closes. my issue is when it opens the form it opens it in normal view which is cool but I would like it to open in add mode instead. Any ideas?

here is my lovely message box

Private Sub DailyReportmsgbox1()
Dim DailyReportmsgbox As String
DailyReportmsgbox = MsgBox("Do you want to do the Daily report?", vbInformation + vbYesNo, "Daily Report")

If (DailyReportmsgbox = vbYes) Then
DoCmd.OpenForm "Dailyreport"

ElseIf (DailyReportmsgbox = vbNo) Then

Exit Sub

End If

End Sub
Padwan is offline   Reply With Quote
The Following User Says Thank You to Padwan For This Useful Post:
DonMac (02-23-2017)
Old 07-21-2012, 05:04 AM   #11
shutzy
Newly Registered User
 
Join Date: Sep 2011
Location: Wesham, Lancashire, UK
Posts: 775
Thanks: 105
Thanked 4 Times in 4 Posts
shutzy is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

just thought i would add the macro i have used for a yes/no option in access 2010

If MsgBox("Do You Want To Merge These Clients?",4)=6 then
openquery: qryClientDelete-Merge-Update

If MsgBox("Do You Want To Delete This Client?",4)=6
openquery: qryClientDelete-Merge-Delete

i cant remember what the '4' stands for in the brackets but i have this on a form and i guess it means show yes/no options. i cant remember where i got this from but it was some website that someone pointed to. if remember ill post it as i think it is quite a good resource.

it is possible without vba. i am no programmer and prefer to stick with macros. if i could do everything in macro i would.
shutzy is offline   Reply With Quote
Old 10-06-2015, 11:34 AM   #12
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 334
Thanks: 220
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Quote:
Originally Posted by AccessJunkie View Post
Hi,

Could you please let us know which version of Access you are using? That will definitely help people replying to your question.

To answer your original question, yes you can certainly display a Yes/No message box from a macro and then take different action on what button is clicked.

In Access 2010, conditional logic in macros is way easier than previous versions, but it is still possible in previous versions; it's just a little more tricky.

Here's a quick example using Access 2007 in case you are using that version.

For the first line of this sample macro, enter this in the Condition column:

6<>MsgBox("Please click one of the buttons.",52)

In the Action column on the first time, use the MsgBox macro action and display a message that says "You clicked No."

On the second line of this macro, enter three dots ... in the Condition column to apply the condition from the previous line. In the Action column for the second line, use the StopMacro action.

On the third line of this sample macro, don't put anything in the Condition column. In the Action column of this third line, use the MsgBox macro action and display a message that says "You clicked Yes."

Save the macro and then run it to try it out.
You'll get a Yes/No message prompt that asks you to click a button. If you click the No button, you'll see a message box saying you clicked No and then the macro stops. If you click Yes, you'll see a message box saying you clicked Yes and then the macro stops.

In the sample example I provided, I used Yes/No buttons, a warning message box (the one with the exclamation point icon), and I was testing to see if they clicked the Yes button. You can modify this to present different options using the MsgBox function.

Hope that helps,

--------------------
Jeff Conrad - Access Junkie - MVP Alumnus
SDET II - Access Test Team - Microsoft Corporation

Author - Microsoft Access 2010 Inside Out
Co-author - Microsoft Office Access 2007 Inside Out
Access 2007/2010 Info: http://www.AccessJunkie.com

----------
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.mspx
----------
Hi can I ask what the 52 refers to in this code?
6<>MsgBox("Please click one of the buttons.",52)
I have been trying to find a list of codes so that I can change the icon type and title but cannot find any reference to 52.
Snowflake68 is offline   Reply With Quote
Old 10-06-2015, 01:30 PM   #13
spikepl
Eledittingent Beliped
 
spikepl's Avatar
 
Join Date: Nov 2010
Location: San Serriffe
Posts: 6,144
Thanks: 77
Thanked 980 Times in 958 Posts
spikepl is a jewel in the rough spikepl is a jewel in the rough spikepl is a jewel in the rough
Re: Run Yes/No MessageBox from MACRO

Look up MsgBox in the documentation.

The second argument specifies what is shown on the popup dialog box, bitwise. So you can specify, e.g., vbInformation + vbYesNo etc, each component having some bit value and the total makes up 52 here. Try it.
__________________

Don't tell us what did not happen.
Do you tell the mechanic about your car "it doesn't work" or vague stuff like:
I couldn't, No joy, To no avail, No go
, Incorrect, Wrong, Seems, Without success, It didn't let me, I/it failed.

Do tell us what did happen
:

I want this ... to happen, I attempted this ..., using this ... code and pressing these buttons, the system did this ... in response, and I got this ... error in this ... line.

Did it help? Pass it forward!

Joys of Access:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

spikepl is offline   Reply With Quote
The Following User Says Thank You to spikepl For This Useful Post:
Snowflake68 (10-06-2015)
Old 10-06-2015, 01:42 PM   #14
Snowflake68
Newly Registered User
 
Join Date: May 2014
Location: Hampshire, England
Posts: 334
Thanks: 220
Thanked 4 Times in 4 Posts
Snowflake68 is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Quote:
Originally Posted by spikepl View Post
Look up MsgBox in the documentation.

The second argument specifies what is shown on the popup dialog box, bitwise. So you can specify, e.g., vbInformation + vbYesNo etc, each component having some bit value and the total makes up 52 here. Try it.
What documentation? I understand what you mean about how the bits are made up but im not using vba im using the conditions in a macro and just need to know how to change the icon from a warning triangle to a critical icon.
I have tried changing the numbers but dont get the combination that I need.

I just need Yes and No buttons but want to change the icon. This is my macro;

message box.JPG

Thanks for helping
Snowflake68 is offline   Reply With Quote
Old 01-22-2018, 08:17 AM   #15
CraigDouglas
Newly Registered User
 
Join Date: Sep 2016
Posts: 16
Thanks: 2
Thanked 0 Times in 0 Posts
CraigDouglas is on a distinguished road
Re: Run Yes/No MessageBox from MACRO

Dear Access Junkie

Please, can you help me? I am using Access 2007. I want to use a macro. If a certain field IsNull I want a message to pop up and ask a question and then I want it so you can chose Yes No or Cancel or even just Yes No. The fields name is [EmailAddress] the question would be "Are you sure you want to close the form without an email address?" If Yes is chosen then I want the form to close and if No is chosen I want the form to stay open so that an email address can be added. Would I put the macro in the On Close form event?

Sincerely
Craig

CraigDouglas is offline   Reply With Quote
Reply

Tags
button , cancel , message , ok/cancel , yes/no

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
excel macro won't run twice dmonney Macros 9 05-27-2009 08:07 AM
run VBA code in Macro Dylan Snyder Macros 3 07-11-2008 04:57 AM
Run Macro Automatically Whenever a Query/Form/Table/etc is Run josh-plus Macros 1 07-07-2006 09:15 AM
run excel macro from access (report)button Ziggy1 Modules & VBA 4 03-03-2004 08:23 PM
schedule a macro to run Bob Thornton Macros 4 06-04-2003 11:42 PM




All times are GMT -8. The time now is 07:27 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World