Sub AppendQueries()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qryRS As DAO.Recordset
Dim aa As Integer
Dim i As Integer
Dim j As Integer
Dim CSiD As Integer
Dim ProcessID As Integer
Dim PCD As Long
Dim b As Integer
Dim RecordCount As Long
Dim qryApp As String
Set db = CurrentDb
Set rs = db.OpenRecordset("tblCompSeriesProcessDetails")
'Debug.Print rs.RecordCount
RecordCount = 1
For i = 1 To rs.RecordCount
If RecordCount < rs.Fields("CompSeriesProcessID").Value Then
RecordCount = rs.Fields("CompSeriesProcessID").Value
End If
rs.MoveNext
Next i
rs.MoveFirst
DoCmd.SetWarnings False
For j = 4 To 15
qryApp = "qryProcess" & j
Set qryRS = db.OpenRecordset(qryApp & "_Sub")
For i = 1 To qryRS.RecordCount
strSQL = "INSERT INTO tblCompSeriesProcessDetails ( CompSeriesProcessID, CompSeriesID, ProcessID_FK, PlannedCompletionDate )" _
& "SELECT " & RecordCount + 1 & ", " & qryApp & "_Sub.CompSeriesID, " & qryApp & "_Sub.CurrentProcess, " & qryApp & "_Sub.PCD " _
& "FROM " & qryApp & "_Sub"
Debug.Print strSQL
DoCmd.RunSQL strSQL
RecordCount = RecordCount + 1
qryRS.MoveNext
Next i
Next j
For j = 17 To 33
qryApp = "qryProcess" & j
strSQL = "INSERT INTO tblCompSeriesProcessDetails ( CompSeriesProcessID, CompSeriesID, ProcessID_FK, PlannedCompletionDate )" _
& "SELECT " & RecordCount + 1 & ", " & qryApp & "_Sub.CompSeriesID, " & qryApp & "_Sub.CurrentProcess, " & qryApp & "_Sub.PCD " _
& "FROM " & qryApp & "_Sub"
Debug.Print strSQL
DoCmd.RunSQL strSQL
RecordCount = RecordCount + 1
Next j
DoCmd.SetWarnings True
Set qryRS = Nothing
Set rs = Nothing
Set db = Nothing
End Sub
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE