Drop down box selection to run macro

[jon.wright]

hello all,

im having a problem on a form, im not very well educated on access but im learning all the time and could do with a little help.

i have a combo box on a form, with two possible answers A and B,
what i want to achieve is when i select A it runs a macro 'A Specification', and when i select B it runs 'B Specification' Both A and B Specification are in a remote database.

the combo box is setup with the two options but i am struggling to write the command to run the macro.

under design view and properties of the combo box i pressume you will select On Enter?
can some body advise on this and help me with the SQL?

thank you jon.

 

[John Big Booty]

Welcome to the forum.

Firstly you will need to figure out at what point you wish to trigger the running of the macro.

I would probably trigger it with a command button and use some code in the button's On Click event, it might look something like;

If Me.ComboName = 1 then
     [URL="http://msdn.microsoft.com/en-us/library/bb214052%28office.12%29.aspx"]DoCmd.RunMacro[/URL] "YourMacroName1"
Else
     DoCmd.RunMacro "YourMacroName2"
End If

 

[jon.wright]

Thank you very much, and thank you for welcoming me to the forum.
You have already been a great help,
my sql i used is the following,

Private Sub Combo149_Click()
If Me.Combo149 = "ANSI Flange Standard" Then
DoCmd.RunMacro "CAD WORX Database Update"
End If
If Me.Combo149 = "PN Flange Standard" Then
DoCmd.RunMacro "CAD WORX Database Update"

End If
End Sub

One of the things i need to achieve now is to get the selection to remain selected until i change it, by this i mean, when i close the database and reopen again the box is blank, i need it to stayed filled with the previous selection.

any advice.

 

[John Big Booty]

To do that you will need to store the value that the Combo is holding when the form is closed, in a table, and then set that value as the combo box default value when the form is re-loaded.

Just a quick point the code you quoted above is VBA (Visual Basic for Aapplications). SQL (Structured Query Language) is the language that drives queries, and whilst it can be used within VBA is quite a different beast.

 

[jon.wright]

Thank you for the heads up on this,
is it possible you could help me with this, i currently have the combo as a table that the drop down boxes looks at, im just not sure how to store this value when selected, does it need to be stored in an additional table when selected? if so how do you do this?

any help is appreciated.

 

[John Big Booty]

Are you able to post a copy of your DB (Pre '07)?

 

[jon.wright]

Hello, thank you for your quick response,
I have been struggling all afternoon to get this working and still no joy.
It is kind of awkward for me to post the database as it is for work purposes and has alot of information tied to it.
To explain in more detail thou.
I have an autoexe macro which opens a form on load up, this form is like the front sheet for the whole database/project.
On this form I need an option for the user to choose either ‘ANSI Flange Standard’ or ‘PN Flange Standard’
To do this I created a table called flange standard, listed the two standards stated above and then created a Combo Box on my form that looks at this table for the two options, (this part is setup and is currently working okay) I then linked a docmd. To each of the options so that a query is run dependant on which of the options the user selects. Now the issue I have is that once the user has selected this option from the combo box for the particular project he will hardly ever need to change it again so I want the option to remain selected when the database is closed then re-opened unless the user changes it.
How can this be done?
I have been looking in to a MVB expression for the event procedure on After Update, but not 100% sure if I should be doing it at this point.
Please help.
Thank you.