Execute make table query in vba

[Judy]

I am trying to execute a make table query with one parameter in vba.
After much researching in this forum, I have tried to define the
parameter in code, but i still get the error message that it is
expecting a parameter. Any help would be greatly appreciated.
Judy


Dim dbs As DAO.Database

Set dbs = CurrentDb

DoCmd.SetWarnings False 'turn off warnings
dbs.TableDefs.Delete "tblmyvar" 'delete old copy of table
Err.Clear


Dim qdf As QueryDef
Set qdf = dbs.QueryDefs("qmyv")
qdf.Parameters(0) = [Forms]![Report Menu - Project
Status]![Combo77]

dbs.Execute "qmyv"
dbs.Close
Set dbs = Nothing

rst.Close: Set rst = Nothing
Set qdf = Nothing
DoCmd.SetWarnings True

 

[Wayne Morgan]

You're not executing the same copy of the query as you have applied the
parameter value to. Instead of

dbs.Execute "qmyv"

use

qdf.Execute

Also, I would recommend a small modification to alert you if an error
occurs:

qdf.Execute dbFailOnError

 

[Joan Wild]

Dim dbs As DAO.Database, qdf As QueryDef
Dim prm As DAO.Parameter
Set dbs = CurrentDb()

DoCmd.SetWarnings False 'turn off warnings
dbs.TableDefs.Delete "tblmyvar" 'delete old copy of table
Err.Clear

Set qdf = dbs.QueryDefs("qmyv")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute dbFailOnError

' dbs.Close - this isn't needed - you didn't open a database, so don't
close it
Set dbs = Nothing

' rst.Close: Set rst = Nothing - this isn't needed - there is no rst
Set qdf = Nothing
DoCmd.SetWarnings True

Instead of deleting the table, and running a make table query, you could run
a delete query, followed by an append query
Replace
DoCmd.SetWarnings False 'turn off warnings
dbs.TableDefs.Delete "tblmyvar" 'delete old copy of table
Err.Clear
with
dbs.Execute ("DELETE * FROM tblmyvar), dbFailOnError
and remove the Docmd.SetWarnings True line

Change your qmyv query to an append query.

 

[Judy]

Thanks so much, Wayne. I used suggestions from both you and Joan and
it works fine now. I am so grateful to you both. Judy