Calling a SQL query

[michaelc80]

Hello,

I'm working with an existing Access database trying to do some automation. Fair warning-I know very little about VBA-I usually work with SQL.

The Access database has a table in it called Data_Scrub_SQL. There are 124 rows, one of the fields (called SQL) is a SQL query SELECT string that can be copied/pasted into an Access query to determine problems and integrity issues with data in other tables. There is another field called Error_Id that contains a unique error number.

What I'd like to do is this-create a form with a text box and a command button. The user could fill out the text box with the error number and press the command button, returning a query with the recordset in it.

I can get close with the following VBA code for the command button. The query referenced is simply an empty query named "qryResults". The "SELECT * FROM SQL_TABLE" is the sql copied and pasted-1 button, 1 sql query.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT * FROM SQL_TABLE"

Set db = CurrentDb
Set qdf = db.QueryDefs("qryResults")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryResults"


However I'd prefer to avoid having a form with 124 buttons on it if at all possible. Is there a way to put in the Error_Id number in the text box, and have the VBA look to the appropriate SQL field and pull the SQL from there and run it in the query?

Thanks!

 

[vbJupiter]

Hello,

I'm working with an existing Access database trying to do some automation. Fair warning-I know very little about VBA-I usually work with SQL.

The Access database has a table in it called Data_Scrub_SQL. There are 124 rows, one of the fields (called SQL) is a SQL query SELECT string that can be copied/pasted into an Access query to determine problems and integrity issues with data in other tables. There is another field called Error_Id that contains a unique error number.

What I'd like to do is this-create a form with a text box and a command button. The user could fill out the text box with the error number and press the command button, returning a query with the recordset in it.

I can get close with the following VBA code for the command button. The query referenced is simply an empty query named "qryResults". The "SELECT * FROM SQL_TABLE" is the sql copied and pasted-1 button, 1 sql query.

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT * FROM SQL_TABLE"

Set db = CurrentDb
Set qdf = db.QueryDefs("qryResults")
qdf.sql = strSQL
Set qdf = Nothing
Set db = Nothing

DoCmd.OpenQuery "qryResults"


However I'd prefer to avoid having a form with 124 buttons on it if at all possible. Is there a way to put in the Error_Id number in the text box, and have the VBA look to the appropriate SQL field and pull the SQL from there and run it in the query?

Thanks!

Is "SQL_table" the same as Data_Scrub_SQL??? If so, I would suggest the following modification: (the text box that user types the error message into is referred to as txtErrorNumber)


Private Sub YourCommandButton_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

strSQL = "SELECT * FROM SQL_TABLE " & _
"WHERE Error_ID = Forms!YourFormName!txtErrorNumber;"

Set db = CurrentDb
Set qdf = db.QueryDefs("qryResults")
qdf.sql = strSQL
DoCmd.OpenQuery "qryResults", acViewNormal, acReadOnly
Set qdf = Nothing
Set db = Nothing

End Sub

Hope that helps!
AA