running queries one after another

[Simon_MT]

Stick with your Functions, the are so easy to manage, in older versions of Access 1997 it was advised to removed the From Functions and transfer them entirely to Functions.

After Set Warnings put a line for MsgBox and whatever message you need.

Simon

 

[johnmerlino]

I tried the msgbox already but doesn't work:

Public Function initializeFirstSet()
 
DoCmd.SetWarnings False

'Drop table if exists 
On Error Resume Next
DoCmd.RunSQL "DROP TABLE defense_final"
DoCmd.RunSQL "DROP TABLE duplicatecheck"


'Otherwise we now just execute queries
DoCmd.OpenQuery "defense_query"

DoCmd.OpenQuery "CREATE possibles"

DoCmd.OpenQuery "APPEND to possibles"

DoCmd.OpenQuery "FINALIZE possibles Other"

DoCmd.OpenQuery "remove_more_than_5_different_addresses"

DoCmd.SetWarnings True
 
'Alert when finished.
MsgBox "Complete."

End Function

 

[vbaInet]

I beblieve queries are run synchronously so one query will finish before the other one starts. The msgbox at the end should be a sufficient confirmation.

I would however use:

CurrentDb.Execute "defense_query", dbFailOnError

...for all of your action queries. You can test the RecordsAffected property of CurrentDb if you were to use the same db object for all 5 queries.

Edit: Oh, so I did mention this before

 

[johnmerlino]

CurrentDb.Execute "defense_query", dbFailOnError

Thanks for response. What does that do and how is that any different from what I already have?

 

[Mike375]

If I am reading you correctly then an easy way to get what you want basically working would be as follows.

On the side of your forum put a button or a label to represent the group of queries you wish to run. Then behind each label put the code to run the group of queries. You can have them run with either one click or a double click.

Doing it that way is ofen referred to as hard coding. It won't allow for variables and thus needs a button or label for each group of queries. But it will get what you want working. Then the next stage is you can refine it.

 

[vbaInet]

Thanks for response. What does that do and how is that any different from what I already have?

You have a group of action queries to run and if the first query fails, the next query will run. Well in most cases that is if you have Resume Next in your error handler. The db's RecordsAffected property allows to check how many records were affected by the update/insert/delete action plus there's the dbFailOnError which helps to roll the table back to its previous state if an error occurs. You don't want to run the second query if the first failed? Check the help file on CurrentDb.RecordsAffected. But like I mentioned, queries run synchronously so your msgbox will work.

On the side of your forum put a button or a label to represent the group of queries you wish to run. Then behind each label put the code to run the group of queries. You can have them run with either one click or a double click.

Doing it that way is ofen referred to as hard coding. It won't allow for variables and thus needs a button or label for each group of queries. But it will get what you want working. Then the next stage is you can refine it.

Interesting suggestion but I'm sure the vba editor will be more appropriate for hard coding anything. A Const variable is sufficient for handling situations like this. But still with your case code, how do you plan on executing the code since it's saved as string in the label's caption? Eval()?

 

[Mike375]

Interesting suggestion but I'm sure the vba editor will be more appropriate for hard coding anything. A Const variable is sufficient for handling situations like this. But still with your case code, how do you plan on executing the code since it's saved as string in the label's caption? Eval()?

What are you talking about? You have lost me.

I have given him something that easy to do to get the job done. He can refine it later but in the meantime if he "clicks here" a set of queries run and if he "clicks there" another set of queries run

But what does the label's caption have do with anything. He can make the caption whatever he wants.

 

[vbaInet]

This was what prompted my response -->

On the side of your forum put a button or a label to represent the group of queries you wish to run. Then behind each label put the code to run the group of queries.

What did you mean by that? It sounded to me that you suggested putting the code to run the queries in the caption property of the label.

 

[vbaInet]

What are you talking about? You have lost me.

I have given him something that easy to do to get the job done. He can refine it later but in the meantime if he "clicks here" a set of queries run and if he "clicks there" another set of queries run

But what does the label's caption have do with anything. He can make the caption whatever he wants.

Ah, do you mean the Click event of the label? I think I've got you now.

 

[Mike375]

Maybe some misunderstanding on "put behind the label" or similar. But I think you find on the forum that way of expressing is used a lot. In fact I got it from being on the forum. For example you will see "just put the code I have given you behind a button"

But just so we know where we are


I think he said he wanted to run different sets of queries and with "a click" or "a double click"

So the code behind label/button 1 runs the code/macro that runs query 1, 8, 11, 7 or whatever. Code/macro behind label/button 2 runs query 8,17, 23 etc.

In the label he can have his caption like

Click here to list alll people who are non smokers.....or whatever

Click here to list all people etc

Andlabel look much better if you have a few and need a bit of writing in them.

 

[Mike375]

We posted at the same time

Lets all go back to what we were doing

 

[Mike375]

I think it is Bob Larson's fault as he uses that "put the code behind the button/label"

 

[vbaInet]

Great minds!!

Yeah, I would normally think Click event if you said put it behind a button but with a label I would think caption lol.

What I was doing was having a cup of hot choco and some nice biscuits. I will get back to that

 

[Mike375]

I think the reason for "put the code behind the button" is so the person gets to experience at least a little bit of being on the keboard as the sun comes up....as they finally get the code to work

Actually a lot of people don't realise that some of the answers they get are because the "answerer" saw many sun rises to do the learning.

 

[vbaInet]

Sadly, not many posters want to do any work. They want straight answers. If they were paying for it then yes they will get straight answers, but this is a forum where we are sharing knowledge so they should be willing to put in some effort too and at least learn from it.

Right, let's get back to the OP before he thinks we've stolen his thread

 

[Mike375]

Yes.

In my opinion he should start with my suggestion because it is easy and gets what he wants working.

He can refine later.