executing queries on click event (1 Viewer)

[Jman]

I seen to be having a problem with executing a query on the click event of a button on a form. I am not sure what method to use and how to use it I dont think I want to use the docmd.openquery because I want this query to execute within this module the sql for this query looks like this:
select[mastertable]*Into[temptable]
where(([mastertable].id)like[Enter the id])

any help would be great
John

 

[chrismcbride]

John
Look into the DoCmd Object. It has lots of very useful methods, one of which is the RunSql method. This will bi the ticket for you!
Chris

 

[mrabrams]

Taking the RunSql one step further, create a macro - use the RunSql action.
Then use the macro in the OnClick event.

Michael Abrams

 

[KMAN]

Using a Macro here gains nothing, just adds another layer. I don't use macros for anything except exection of Menus. I don't agree with the suggestion of using Macros, in this case, especially because it adds nothing to the app, or in most other cases as well. VBA is much more efficient.

Also, instead of DoCmd.RunSql, you could use the Execute method.
Example (DAO):

CurrentDb.execute "Select * from table"

KMAN

 

[chrismcbride]

I am with Kman. Too often macros are used when VBA is quicker and has more functionality. However I should point out that the Execute method is a Depricated method that will eventually not be supported by VBA. Stick with RunSql. It will do everything that you need.
Chris

 

[Jman]

Thanks the RunSql is the ticket I knew it was a simple command that I was looking for.

 

[Jman]

Thanks again for all the good advice. On the same subject I have what may seem to be a stupid question but I will ask anyway. If you create an application and run all your queries in VBA in Access 2000 and then create another application and run all your queries through macros will the application running queries through VBA be more efficent? O does it even mater?

 

[chrismcbride]

Never tried it, but two things will definately be true.
1. The VBA copy will afford much more functionality and flexability
2. You will always be able to see all your code laid out in front of you complete with whatever comments you care to add. This will help you when it comes time to debug a problem and will help any one else who needs to work on your DB after you have departed.
If I had to guess my answer would be that the speed would depend on how good the code you wrote was. After all MS uses code behind all the Macros, right? And that code is VBA, right again?
Just some thoughts...
Chris

 

[Jman]

Yes that's right code does come from the macro. I have looked through the Access developers hand book and this reference suggests creating and running code that is written by you, the developer because you can trap and test errors, which I feel is a duty of any IT professional nothing to me is more professionally humiliating than having your client run into the dreaded system error with the end help debug options attached. Thanks for all the advice and help you guys really know your stuff
John