I'm using the following code that Hans gave me for another project. For some reason the update query at the beginning of the code is not running and I'm not sure why.
Thanks!
Leesha
Code: Select all
DoCmd.OpenQuery "qryUpdateAHEBGeneric"
Const lngBatchSize = 80 ' labels to be printed in one go
Dim strSQL As String
Dim strWhere As String
Dim dbs As DAO.Database
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim lngSeqNo As Long
Dim i As Long
Set dbs = CurrentDb
strSQL = "DELETE * FROM tblTempGeneric"
dbs.Execute strSQL, dbFailOnError
strSQL = "SELECT * FROM qryAHEBGeneric ORDER BY ID, Counter"
Set rst1 = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
Set rst2 = dbs.OpenRecordset("tblTempGeneric", dbOpenDynaset)
Do While Not rst1.EOF
rst2.AddNew
lngSeqNo = lngSeqNo + 1
rst2!SeqNo = lngSeqNo
For i = 0 To rst1.Fields.Count - 1
rst2.Fields(rst1.Fields(i).Name) = rst1.Fields(i)
Next i
rst2.Update
rst1.MoveNext
Loop
rst1.close
Set rst1 = Nothing
rst2.close
Set rst2 = Nothing
Set dbs = Nothing
For i = 1 To lngSeqNo Step lngBatchSize
strWhere = "SeqNo Between " & i & " And " & (i + lngBatchSize - 1)
DoCmd.OpenReport ReportName:="rptAHEBGeneric", View:=acViewNormal, WhereCondition:=strWhere
MsgBox "Press OK to continue.", vbInformation
Next i
End Sub
