DoCmd.RunSQL not finding SQL statement 2

[cdgeer]

I am trying to have a Textbox auto populate based on a query but I get a error "DoCmd.RunSQL must have argument consisting of a SQL statement"... but there IS a SQL statement. What am I doing wrong? Here's code. The Debug stops at "DoCmd.RunSQL SQLB"

Private Sub cmdCreateADdistinct_Click()
Dim strADTableName As String
Dim strSQL As String
Dim strSQLB As String

strADTableName = Forms![AD_Processor].[txtNewADTableName]
strSQL = "SELECT DISTINCT [" & strADTableName & "].DataSource, [" & strADTableName & "].AccountName, [" & strADTableName & "].LastName, [" & strADTableName & "].FirstName, [" & strADTableName & "].EEID, [" & strADTableName & "].[SYS/GenericAcct], [" & strADTableName & "].Notes, [" & strADTableName & "].Status, [" & strADTableName & "].Created INTO " & strADTableName & "DistinctAccts FROM " & strADTableName & ";"
strSQLB = "SELECT y_LatestExtracts.TableName FROM y_LatestExtracts WHERE (((Right(([Forms]![AD_Processor]![txtNewADdistinct]),31))=([y_LatestExtracts].[TrimmedTableName])));"

DoCmd.RunSQL strSQL
Me!txtNewADdistinct = "" & strADTableName & "DistinctAccts"

DoCmd.RunSQL strSQLB

Me!txtOldADdistinct = "" & strSQLB & ""

End Sub

 

[randy700]

If I remember right, the DoCmd.RunSQL only works with ACTION queries.

Randy

 

[MajP]

There is lots of problems with this. As stated Runsql requires an action query. You can not set a control to a value in a query even if the query returns a single field and value.

Look at using a dlookup.

 

[cdgeer]

Thanks! I didn't know that couldn't just return a value from running the SQL and use it right away. So, I just used the SQL statement to make a tmp table and did the DLookup thing to populate the textbox. When the form is closed the tmp table is deleted.

Thanks again both of you!

 

[boblarson]

Why make a temp table (not only will it bloat your database but it is completely not necessary).

If you want to use the SQL in code instead of using a saved query, all you need is to use a recordset with it instead.

Plus you don't need a separate SQLB variable. You can reuse the original. So, if I have gotten the right output you wanted, it should be something like this (and I'm assuming that each query only brings back ONE record):

Private Sub cmdCreateADdistinct_Click()    
    Dim strADTableName As String
    Dim strSQL As String
    Dim rst As DAO.Recordset

strADTableName = Forms!AD_Processor.txtNewADTableName    

strSQL = "SELECT DISTINCT [" & strADTableName & "].DataSource, [" & strADTableName & "].AccountName, [" & strADTableName & "].LastName, [" & strADTableName & "].FirstName, [" & strADTableName & "].EEID, [" & strADTableName & "].[SYS/GenericAcct], [" & strADTableName & "].Notes, [" & strADTableName & "].Status, [" & strADTableName & "].Created


Set rst = CurrentDb.OpenRecordset(strSQL)

Me!txtNewADdistrict = rst(strADTableName & "DistinctAccts")

rst.Close

    strSQL = "SELECT y_LatestExtracts.TableName FROM y_LatestExtracts WHERE (((Right(([Forms]![AD_Processor]![txtNewADdistinct]),31))=([y_LatestExtracts].[TrimmedTableName])));"

rst.OpenRecordset(strSQL)

    
Me!txtOldADdistinct = rst!TableName
 
rst.Close

Set rst = Nothing

End Sub

That way you don't need a temp table and you can keep your database from bloating.

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com

 

[boblarson]

Oh, and for this one:

Me!txtNewADdistrict = rst(strADTableName & "DistinctAccts")


You might need to use a variable (string) to concatenate first. I think it doesn't like concatenating on the fly for this type of thing.

So

strField = strADTableName & "DistinctAccts"

Me!txtNewADdistrict = rst(strField)

Bob Larson
Free Access Tutorials and Samples:

http://www.btabdevelopment.com