I am trying to pull together the following on my own and I got stuck. My usual support person is on vacation and I can't wait for him to return.
I am trying to write from an unbound form to a table and I need to loop
through a bound continuous subform (CheckItem & ResponseID) to pull the values into the table. When I try to run this code, I get over 60,000 entries to the table (I have to close Access through the task manager) I need only 10 items (CheckItem & ResponseID) written to the table. I know I'm missing something in my module but I'm not sure where I went wrong. Can anyone help me clean up this code to get it to work? Thank you.
Code:
Sub MSAuditCheck()
Dim HoldAuditDate As Date
Dim HoldDateRange As String
Dim HoldSuprLink As String
Dim HoldAuditorName As String
Dim HoldCheckItem As Integer
Dim HoldResponseID As Integer
Dim dbObject As DAO.Database
Dim MSAuditRS As DAO.Recordset
Dim strQuery As String
HoldAuditDate = Forms!frmMSAudit.AuditDate.Value
HoldDateRange = Forms!frmMSAudit.DateRange.Value
HoldSuprLink = Forms!frmMSAudit.SuprLink.Value
HoldAuditorName = Forms!frmMSAudit.AuditorName.Value
Set dbObject = CurrentDb
strQuery = "SELECT * FROM MSAudit "
Set MSAuditRS = dbObject.OpenRecordset(strQuery)
With MSAuditRS
.MoveFirst
Do While Not .EOF
.AddNew
.Fields("AuditDate").Value = HoldAuditDate
.Fields("SuprLink").Value = HoldSuprLink
.Fields("AuditorName").Value = HoldAuditorName
.Fields("DateRange").Value = HoldDateRange
.Fields("CheckItem").Value = HoldCheckItem
.Fields("ResponseID").Value = HoldResponseID
.Update
.MoveNext
Loop
End With
MSAuditRS.Close
End Sub
I am trying to write from an unbound form to a table and I need to loop
through a bound continuous subform (CheckItem & ResponseID) to pull the values into the table. When I try to run this code, I get over 60,000 entries to the table (I have to close Access through the task manager) I need only 10 items (CheckItem & ResponseID) written to the table. I know I'm missing something in my module but I'm not sure where I went wrong. Can anyone help me clean up this code to get it to work? Thank you.