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 a 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!

Hiya,

   Do this:

Create the form for your combo box and button and call it: frmMyForm

Create a combo box using the wizard that looks up the error code column in your table. Let's say ts called: cboError

Create a query in query designer that uses your table, with the error column, and any other columns you want. In the criteria line in the error column, add this line:

[Forms]![frmMyForm].[cboError]

Create the button on frmMyForm using the wizard to open the query. Done.

You can also create a report based on your query, then call the report from the button instead.

HTH

mmcdonal

Unfortunately, unless I'm doing something wrong, it only calls up the SQL code, but doesn't run it and return the recordset. I need it to actually run the SQL code and show the recordset based on the SQL code results, which could be from several different tables. That's what the code I posted

The Access database is used as a "middleman" for a data conversion from a delimited text file to tables that are then imported into a SQL database.

If you just want to run your query:

    DoCmd.RunSQL strSQL


Or if you want to do something with each record of the results:

    Set db = CurrentDb()
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    rs.MoveLast
    nRecords = rs.RecordCount
    rs.MoveFirst

    For n = 1 To nRecords
    ' Process each record
    Next n

    rs.Close
    db.Close


-Phil-

And for completeness sake:

CurrentDB.Execute strSQL.

Bypasses all those annoying warning messages if your SQL statement is some sort of action query. Works with select statements too, of course.

You can also turn off the warnings (before doing DoCmd.RunSQL strSQL) with:

     DoCmd.SetWarnings False

and turn them back on with

     DoCmd.SetWarnings True


-Phil-