SQL syntax issues for VBA

[JoshuaAnthony]

The following code works fine because I explicitly reference "[Forms]![Main Page]![Program]"

strSQL = "SELECT * INTO " & strUserTableName & " FROM [Current] WHERE [Program] = '" & [Forms]![Main Page]![Program] & "'"

The PROBLEM is that I actually want to define a make-table query based on my recordSource as the following SQL statement shows:

strSQL = "SELECT * INTO " & strUserTableName & " FROM [" & Me.RecordSource & "]

In this case the "WHERE" condition has already been predefined (including the syntax) based on an existing permanent query.

To combat this and not get "run-time error 3061: Too few arguments. Expected ...", I tried to use some code found at www.support.microsoft.com:

strSQL = "SELECT * INTO " & strUserTableName & " FROM [" & Me.RecordSource & "]
Set querydef = CurrentDb.CreateQueryDef(strUserQueryName, strSQL)
If Me.RecordSource = "Current (Program Search)" Then
    querydef![Forms!Main!Program] = [Forms]![Main]![Program]
End If
CurrentDb.Execute querydef.Name

I added a 'watch' on both 'querydef![Forms!Main!Program]' AND '[Forms]![Main]![Program]'.
They both have the correct value before I try to execute the query.
Unfortunately though, I STILL get "run-time error 3061: Too few arguments. Expected 1"

Can anybody help? I've done loads of searches in google and on this and other forums but nobody seems to have a problem with my specific case!

Thanks,

Joshua

 

[Mile-O]

Don't use QueryDef as a name for a QueryDef - it's a reserved word. qdf is a common name for a QueryDef object variable.

You need to set the parameter you want with querydefs.

i.e.

qdf.Parameter(0) = MyValue

 

[JoshuaAnthony]

I changed all my query Object variable names to qdf and still had the same error

 

[Mile-O]

From the code you've posted you haven't closed the quotes off at the end of the SQL and you haven't put the semi-colon that ends an SQL statement either.

 

[JoshuaAnthony]

From the code you've posted you haven't closed the quotes off at the end of the SQL and you haven't put the semi-colon that ends an SQL statement either.

Yeah sorry, I forgot the quotes in this post (they are there in the application) just because I cut and pasted without the "ORDER BY" section because it was irrelevant. I've tried this with or without the semilcolon and it still doesn't work. I don't think the semicolon is actually required for VBA though because when the recordSource that I use has no "WHERE" section or when the items in the "WHERE" section don't reference a field in a form, everything works fine!

Would you like me to post the options that the record source could be?

Also, I think the STRANGEST thing is that if I just create the make-table query in VBA and then execute it manually, the table is created... It's only when I try to EXECUTE the table in VBA that there is a problem (and of course, as I said I can still execute the table in VBA correctly as long as there is no reference in the where section of the query to any form fields)!!!

 

[JoshuaAnthony]

Thanks for your help.

The following code works (somebody showed it to me on another board)

Dim strSQL As StringDim strTbl As String    
strTbl = "myusertable"        
strSQL = "SELECT " & Me.RecordSource & ".* INTO " & strTbl & " FROM " & Me.RecordSource
docmd.SetWarnings False        
docmd.RunSQL strSQL        
docmd.SetWarnings TrueEnd Sub

Thanks for your help Mile-O-Phile Appreciate it.

If nothing else I guess this page would make a great result for somebody having problems creating make-table queries... 3 different solutions!