SQL Criteria from a Form in Code

[Jason]

I have succussfully ran the code below from a module. The code runs
SQL update statements from a memo field in a table, called tblSQL. The
field [tblSQL]![CalledFromProc] (Line 11) allows you to filter out the
records in the SQL table, this way you can run only certain groups of
SQL's/records.

Here is the problem. I created a form with a combo box that will allow
you to pick from [tblSQL]![CalledFromProc]. Then pasted the code below
into On Click in the form. Form some reason the SQL statement is not
referencing back to the Combo Box. If I type the text of the field in,
for example " CalledFromProc= 'Batch One'",

Any suggestions?

'****CODE BEGIN****
1 Sub sSomeSubRoutine ()
2 Dim lodb As Database, strSQL As String
3 Dim loSQLRS As Recordset, lngCurrent As Long
4 Dim varTmp As Variant
5 Const cERR_GRACEFUL_E XIT = vbObjectError + 20
6
7 On Local Error GoTo Err_handler
8 varTmp = SysCmd(acSysCmd SetStatus, "Starting Batch process...")
9 Set lodb = CurrentDb
10 Set loSQLRS = lodb.OpenRecord set("Select * from tblSQL where" _
11 & " CalledFromProc= Me![Combo1]",
12 dbOpenSnapshot)
13 lngCurrent = 1
14 With loSQLRS
15 .FindFirst "SQLSequenc e=" & lngCurrent
16 If .NoMatch Then Err.Raise cERR_GRACEFUL_E XIT
17 Do While Not .NoMatch
18 strSQL = adhHandleQuotes (!SQLString)
19 lodb.Execute strSQL, dbFailOnError
20 lngCurrent = lngCurrent + 1
21 .FindFirst "SQLSequenc e=" & lngCurrent
22 Loop
23 End With

[Jason]

Sorry, my message was posted prematurely.

I have successfully run the code below from a module. The code runs
SQL update statements from a memo field in a table, called tblSQL.
The
field [tblSQL]![CalledFromProc] (Line 11) allows you to filter out
the
records in the SQL table, this way you can run only certain groups of
SQL's/records.

Here is the problem. I created a form with a combo box that will
allow
you to pick from [tblSQL]![CalledFromProc]. Then pasted the code
below
into On Click in the form. For some reason the SQL statement is not
referencing back to the Combo Box. If I type the text from
[tblSQL]![CalledFromProc], for example " CalledFromProc= 'Batch One'",
it works. My only guess it that my syntax in the SQL statement it
wrong.

Any suggestions?

'****CODE BEGIN****
1 Sub sSomeSubRoutine ()
2 Dim lodb As Database, strSQL As String
3 Dim loSQLRS As Recordset, lngCurrent As Long
4 Dim varTmp As Variant
5 Const cERR_GRACEFUL_E XIT = vbObjectError + 20
6
7 On Local Error GoTo Err_handler
8 varTmp = SysCmd(acSysCmd SetStatus, "Starting Batch process...")
9 Set lodb = CurrentDb
10 Set loSQLRS = lodb.OpenRecord set("Select * from tblSQL where" _
11 & " CalledFromProc= Me![Combo1]",
12 dbOpenSnapshot)
13 lngCurrent = 1
14 With loSQLRS
15 .FindFirst "SQLSequenc e=" & lngCurrent
16 If .NoMatch Then Err.Raise cERR_GRACEFUL_E XIT
17 Do While Not .NoMatch
18 strSQL = adhHandleQuotes (!SQLString)
19 lodb.Execute strSQL, dbFailOnError
20 lngCurrent = lngCurrent + 1
21 .FindFirst "SQLSequenc e=" & lngCurrent
22 Loop
23 End With

jf***@rochester .rr.com (Jason) wrote in message news:<bb******* *************** ****@posting.go ogle.com>...

I have succussfully run the code below from a module. The code runs
SQL update statements from a memo field in a table, called tblSQL. The
field [tblSQL]![CalledFromProc] (Line 11) allows you to filter out the
records in the SQL table, this way you can run only certain groups of
SQL's/records.

Here is the problem. I created a form with a combo box that will allow
you to pick from [tblSQL]![CalledFromProc]. Then pasted the code below
into On Click in the form. For some reason the SQL statement is not
referencing back to the Combo Box. If I type the text of the field in,
for example " CalledFromProc= 'Batch One'", it works. My only guess it that my syntax in the SQL statement it wrong.

Any suggestions?

'****CODE BEGIN****
1 Sub sSomeSubRoutine ()
2 Dim lodb As Database, strSQL As String
3 Dim loSQLRS As Recordset, lngCurrent As Long
4 Dim varTmp As Variant
5 Const cERR_GRACEFUL_E XIT = vbObjectError + 20
6
7 On Local Error GoTo Err_handler
8 varTmp = SysCmd(acSysCmd SetStatus, "Starting Batch process...")
9 Set lodb = CurrentDb
10 Set loSQLRS = lodb.OpenRecord set("Select * from tblSQL where" _
11 & " CalledFromProc= Me![Combo1]",
12 dbOpenSnapshot)
13 lngCurrent = 1
14 With loSQLRS
15 .FindFirst "SQLSequenc e=" & lngCurrent
16 If .NoMatch Then Err.Raise cERR_GRACEFUL_E XIT
17 Do While Not .NoMatch
18 strSQL = adhHandleQuotes (!SQLString)
19 lodb.Execute strSQL, dbFailOnError
20 lngCurrent = lngCurrent + 1
21 .FindFirst "SQLSequenc e=" & lngCurrent
22 Loop
23 End With