Run Make-Table Query at Form_Open

[Bill Bisco]

Hi all,

Basically I'd like to run my Make-Table Query as soon as a Form is Opened; however, I have only found the following.

Dim SQL As String          

SQL = "UPDATE Employees " & _
"SET Employees.Title = 'Regional Sales Manager' " & _           
"WHERE Employees.Title = 'Sales Manager'"      

DoCmd.RunSQL SQL

Is there any way to tell VBA just to run my query rather than having to copy my query's SQL code?

Thanks,
Bill

 

[pbaldy]

You can run the query with

CurrentDb.Execute

or

DoCmd.OpenQuery

More info on both in VBA help

 

[boblarson]

Just use

DoCmd.OpenQuery "YourMakeTableQueryNameHere"

And no need to close it as it doesn't stay open on a make table query.

 

[boblarson]

Actually, if you want to avoid the warnings, use the first one Paul gave (CurrentDb.Execute "YourQueryNameHere", dbFailOnError)

 

[Bill Bisco]

Thanks pbaldy; exactly what I needed. Now I just need to figure out how to make Access just run the code without all the warning messages.

Sincerely,
Bill

 

[boblarson]

Thanks pbaldy; exactly what I needed. Now I just need to figure out how to make Access just run the code without all the warning messages.

Sincerely,
Bill

See my message just before the one you posted here

 

[Bill Bisco]

Thanks Bob; Guess that's what happens when I take too long to respond

I'm getting an Error though still. It says "Table 'tmpStationOptions' already exists."

Basically I want it to overwrite the Current Table without trouble.

Sincerely,
Bill

 

[pbaldy]

Personally I would empty/populate the table rather than delete/create it (presuming the structure doesn't change). It would be more efficient.

 

[Bill Bisco]

My current method for solving this problem is

Dim qryName As String

DoCmd.SetWarnings False

qryName = "qmakStationOptions"

DoCmd.OpenQuery qryName

DoCmd.SetWarnings True

pbaldy, thanks for the post. How would I empty the Table though? I assume Append could add records.

 

[boblarson]

My current method for solving this problem is

Dim qryName As String

DoCmd.SetWarnings False

qryName = "qmakStationOptions"

DoCmd.OpenQuery qryName

DoCmd.SetWarnings True

pbaldy, thanks for the post. How would I empty the Table though? I assume Append could add records.

1. To reiterate - I would NOT use the DoCmd.SetWarnings and INSTEAD use the

CurrentDb.Execute method which requires no turning off of warnings.

2. CurrentDb.Execute "Delete * FROM TableName", dbFailOnError
will empty the table.

 

[Bill Bisco]

Thanks Bob.

When I try the Delete then Append Method using this code:

Dim qryName As String
qryName = "qappStationOptions"

CurrentDb.Execute "Delete * FROM tmpStationOptions", dbFailOnError
CurrentDb.Execute qryName, dbOnError

I get the following error message: "Too few parameters. Expected 1"

I get the feeling that Access doesn't like to append an empty table or something.

 

[boblarson]

I get the feeling that Access doesn't like to append an empty table or something.

No, that isn't it. What is likely is that you have a parameter on your query which refers to a form object and doesn't have quotes around it.

So, if using the DoCmd.SetWarnings and DoCmd.OpenQuery work instead that is fine, just make sure to put an error handler in the procedure and for the first line put
DoCmd.SetWarnings True

or else if it errors out before it can reset them you will find yourself without any (including those that ask if you want to save items that have been modified like forms in design view. They will just close and not save).

 

[Bill Bisco]

Bob,

Yeah, it was referring to a combo box on the form for a criteria without quotes. Interesting, I've just found a limitation of Access !

I've added the extra code to set warnings to true and the error handling stuff. Thanks a bunch.

Sincerely,
Bill