Creating a macro to run a query

[dreamz]

Hi

i've managed to create a macro that opens a query into design view but can't seem to figure out how to get the macro to run the query

 

[ColinEssex]

Select Open Query in the macro then select Datasheet View not Design View.

Col

 

[dreamz]

Thx Col

works well.

Next Question - Can i add say 5 more macros to the end of this and will they all run?


Also, how would i set this up so that when i open the db, i just click 1 button to run the macro (which will run my 6 queries)

 

[ColinEssex]

Thx Col

works well.

Next Question - Can i add say 5 more macros to the end of this and will they all run?


Also, how would i set this up so that when i open the db, i just click 1 button to run the macro (which will run my 6 queries)

why have 6 macros? you can have the 6 queries run in one macro if thats what you want. Just do the same as the first one x 5

If you have a button on an opening form (for example) select the OnClick property and select the macro name, then it'll run when clicked.

Col

 

[dreamz]

yeah sorry, i mean't 6 queries in one macro.

Thanks again.

Am i correct in saying that when i create that form and add the OnClick button, i can also add a button to import the 2 source files that i'll use as tables for my queries?

 

[ColinEssex]

You can do that in the original macro (before the queries are run). You'll need to set up an import specification and provided the data always has the same fields etc it'll import. You'll need to delete the old data first I'm guessing, so use a delete query for that.

Col

 

[dreamz]

thanks again.


Well i've got as far as Setting the form up. Creadted a cmdselect button to open the new files BUT when i open up the db, the macro wants to run straight away without giving me the option to select my file 1st.

 

[ColinEssex]

Isn't the location of the file (to import) part of the Import Specification that you need and presumably have saved?

Also, why would a Command button run when a form is opened? If your macro is running "OnClick" its not going to run any other time.

Col

 

[dreamz]

no idea, just trying to find a way to import but don't know how so was trying to create a cmd select button so when it is was clicked it would promt u with a open box so that i could select the table/file to import?

 

[ColinEssex]

Maybe I'm misunderstanding you here.

Lets recap - you want to automatically import data just by clicking a button?

If so, use the import wizard and save all the settings as a specification. The settings being the location of the file to import and the format, destination etc.

Providing the file is always in the same place and has the same fields, all you need to do is include that specification in the macro and it'll automatically do it when the macro runs.

Col

 

[dreamz]

yeah i think i've got it now. Thanks Col

basically this is what i've done.

Created a Macro that

1.) Deletes the contence from my Table 1
2.) Deletes the contence from my Table 2
3.) Imports the new Table 1 (same file name, same location)
4.) Imports the new Table 2 (same as above)
5.) Runs all my queries
6.) Exports the new tables i've created.



Now, thats what my macro does. I now need to create a button that runs this macro. i assume i do this by creating a form and put a button on it?

 

[ColinEssex]

yeah i think i've got it now. Thanks Col

basically this is what i've done.

Created a Macro that

1.) Deletes the contence from my Table 1
2.) Deletes the contence from my Table 2
3.) Imports the new Table 1 (same file name, same location)
4.) Imports the new Table 2 (same as above)
5.) Runs all my queries
6.) Exports the new tables i've created.



Now, thats what my macro does. I now need to create a button that runs this macro. i assume i do this by creating a form and put a button on it?

You got it

Col

 

[dreamz]

sweeet

thanks again for your help.

Have a virtutal beer on me

 

[dreamz]

Okay another Quick question.

When i open access, i get my forum popup with my button that runs the macro. i click on the button and presto! all done, which is fine BUT - > when i try to close the form, the macro runs again! which i don't want. Any ideas on how i stop that from happening?

 

[ColinEssex]

Hmmm, make sure the macro isn't accidentally running in the forms properties (OnClose)

Try adding a close button on the form and closing it that way instead of using the X

Col

 

[dreamz]

yup, not running on close.

i renamed the macro, and ran it. when i clicked on my button, it said that it can't find the macro (coz i changed the name), so i closed the form and the macro ran!!!

added a close form button. when i close the form with the button, the macro runs!

 

[ColinEssex]

Well, as far as I'm aware, macros don't just run without being triggered. (I may be wrong though )

I would check everything including the VB code behind the buttons etc, I feel there must be a trigger somewhere

Good Luck

Col