Records Affected - Member Not Found 1

[MwTV]

Assistance needed.

Receiving "Member Not Found" error upon trying to run following function with a macro. Specifically, "Records Affected" is highlighted and "Member Not Found" is displayed.

(Note, function is used to run 10 make-table queries in sequence using Windows Scheduler)


Public Function fQueryBatch() As Byte

Dim strSQL As String
strSQL = "SELECT * FROM tblQueriesToRun ORDER BY QueryOrder ASC"
On Error GoTo Err_fQueryBatch
With CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

'Loop through the queries to run
Do Until .EOF
'Open the record that tracks the query and record the start
.Edit
.Fields("DateTimeQueryStarted") = Now()
'Run the query
DoCmd.SetWarnings False
CurrentDb.Execute .Fields("QueryName"), dbFailOnError
DoCmd.SetWarnings True
'Record more stats and save the record
.Fields("DateTimeQueryEnded") = Now()
.Fields("RecordsReturnedByQuery") = .RecordsAffected
.Update
'Move to the next record
.MoveNext
Loop
End With
fQueryBatch = True
Exit Function
Err_fQueryBatch:
fQueryBatch = False
End Function


'Incorporate the following five lines to check to see if all queries were run successfully

'If RunQuery("QueryName") = True Then
' MsgBox "Query was successful", vbInformation
'Else
' MsgBox "Query was not successful", vbExclamation
'End If

 

[PHV]

What about this instead ?
.Fields("RecordsReturnedByQuery") = CurrentDb.RecordsAffected

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MwTV]

That appears to work.

Upon stepping into the code, no error messages occurr.

Another question - I am currently modifying the function above so that I can capture errors in a table. Note, I am interested in determining which one of the make-table queries caused an error.

What modifications should be made if I am interested in capturing errors in a table,tblError, with the following fields?

ErrorLogID
ErrNumber
ErrDescription
ErrDate (Date/Time)
CallingProcedure
UserName
Parameters