transform query into sub

[niki]

Hello,
I have created a set of queries and macros which "transform" a mother database into different tables. My problem is that I want to know how I can change a query into a module which will be commanded by a macro.
I need to do this because my database treatment will be automatical, and when I put my query inside a macro, access asks me If I want to add the lines to the tables and so on.
I want the user to be able to populate a table on a single click, therefore I need to point the use of a button from the form on a macro based on a module and not on queries.

The queries I use are quite simple, they use small function based on Instr function, or the count function itself...
Can someone tell me how to write a code line which would perform the same task as the query? After that I'll bind the action of the code and the macro.

Thanks
nico

 

[dcx693]

Please do not post the same question in multiple categories. http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=51676

 

[jeremie_ingram]

Try going into the SQL view of your queries. Copy the text (SQL statement) and then go to your form in design view. Under the properties EVENT tab for the command button you want to trigger all of this, select EVENT PROCEDURE for the CLICK event.

In the VB window, under the Private Sub for the command click, put in DoCmd.RunSQL and then paste the SQL in. This should do the trick, and you can always add more DoCmd.RunSQL for each of the queries.

Heres a question, do you want to empty the tbls first? I have something similar to what you described, and had to delete first, then bring in the new data.

Also, if there are anoying pop up warnings about the data manipulation, add the line DoCmd.SetWarnings = False to the begining of the code, then repeat it at the end but =True.

 

[niki]

HEy jeremie, thx for your answer!
To your question, yes I want to reset the tables first, I'd like to see the code you are talking about! Attach it to your reply!

nico
PS: Sorry for the multiple posts but I thought the two topics were related to my question

 

[jeremie_ingram]

Sorry it took so long to reply, I havent been availible for a few days. Basically what I was getting at is this, If you have the queries set to draw the information in (appen query), all you need to do is get the old data out (delete query). What I initially did was create a linked tbl which I created an actual tbl out of by adding all of the fields to a query and then changing it into a MAKE TABLE query. Once the table was created, I changed the MAKE TABLE query into an APPEND query. Then I added all of the fields from the new tbl into a delete query, so I could get the old data out. Then I created a form, added a cmd button, canceled the wizard and went to its properties. I selected EVENT PROCEDURE under the click event, then added similar code to this.

Private Sub Command0_Click()
DoCmd.SetWarnings False
DoCmd.OpenQuery "qry2"
DoCmd.OpenQuery "qry1"
DoCmd.SetWarnings True

End Sub

Simply put, it turns all warnings off (so no annoying pop ups)
Runs the delete query (qry2)
runs the append query (qry1)
Turns the warnings back on (not really neccessary but good pratice)

You could even add this to the ON OPEN event for the form and eliminate the need for the cmd button.

 

[niki]

All right jeremie!!!
thanks a lot for the tip, it will be useful to me!!
c ya around!
nico