SQL syntax 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

http://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

 

[Rolliee]

Joshua,

Why not do all of this in vb?

rollie@bwsys.net

 

[JoshuaAnthony]

I was! It works perfectly when I do it manually. The PROBLEM occurs when I use the VBA code that I posted above.

I've actually been shown a good way around my problem that works (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 True

 

[Rolliee]

Joshua,

Do a Msgbox on strSQL! I believe you will find you do not have what you think. For instance, you have, I beleive, TWO not ONE "SELECT" statements combined.

rollie@bwsys.net

 

[Rolliee]

Yes,

strSQL = "SELECT * INTO " & strTbl & " FROM " & Me.RecordSource & ";"


works fine.

Rollie