Newbie help

[Indigo]

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.

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.

 

[pbaldy]

For starters, you'll get as many records as exist in the table MSAudit, which I doubt is what you want. I'm guessing you want a second recordset opened on the source of the subform. That would serve as your source, the existing recordset as your target. I see more, but let's start there.

 

[Indigo]

So something like....?

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 MSHistoryRS As DAO.Recordset
    Dim strQuery As String
    Dim strQuery2 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 "
        strQuery2 = "SELECT * FROM MSCheck "
        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'm still missing something, though, as it wrote 264000 records without
the checkitem or ResponseID before I closed access.

 

[pbaldy]

Well, you created a string variable for the second recordset, but didn't open a recordset with it or use it. You want your loop based on that, presuming that's the table with just your 10 records in it.

The other is because you never set those variables. Presumably you can just get them from the recordset.

 

[Indigo]

Sorry, I'm really green with all of this.....

So I add:

Set MSAuditRS = dbObject.OpenRecordset(strQuery)
[COLOR=red]Set MSHistoryRS = dbObject.OpenRecordset(strQuery2)
[/COLOR]        With MSAuditRS
               .MoveFirst

To open the record set.....(yes,this is the table with 10 records)....
but you lost me on the loop part..... What am I missing?

 

[pbaldy]

This line:

Do While Not .EOF

needs to be using the new recordset. You want 2 recordsets, one on the source table and one on the target. You want to loop through the source table, but add records to the target. For clarity and your own sanity, you may want to get rid of the With block and explicitly specify each recordset as appropriate to the above.

 

[Indigo]

Paul,

I really appreciate this and I am trying to grasp it all... but...

So I get rid of these two lines:

[COLOR=red]With MSAuditRS[/COLOR]
.
.
.
[COLOR=red]End with[/COLOR]

But I'm not clear about the "explicitly specify each recordset" part...

 

[Indigo]

I get a compile error - Invalid or unqualified reference

.MoveFirst

is Highlighted when I comment out the With Block.

 

[pbaldy]

What the With block did was let you do this:

Do While Not .EOF

instead of

Do While Not MSAuditRS.EOF

So what I'm saying is that now everywhere that formerly was within that With block and just started with a dot will need to have the recordset explicitly stated. An example is above, but each line will need to be addressed.

 

[Indigo]

Is this what you mean by explicitly specify?:

                    Do While Not MSAuditRS.EOF
                    MSAuditRS.AddNew
                    MSAuditRS.Fields("AuditDate").Value = HoldAuditDate
                    MSAuditRS.Fields("SuprLink").Value = HoldSuprLink
                    MSAuditRS.Fields("AuditorName").Value = HoldAuditorName
                    MSAuditRS.Fields("DateRange").Value = HoldDateRange
                    MSAuditRS.Fields("CheckItem").Value = HoldCheckItem
                    MSAuditRS.Fields("ResponseID").Value = HoldResponseID
                    MSAuditRS.Update

 

[Indigo]

D'oh....I'm sorry....I'm just not thinking clearly here....

Okay, I get it now......

But its still looping through 590,000 times (until I stopped it).

How do I get it to stop at 10?

 

[pbaldy]

I'm making an assumption about the data, since you haven't specified, but I suspect this

Do While Not MSAuditRS.EOF

should be

Do While Not MSHistoryRS.EOF

and of course the MoveNext would also be on that recordset.

 

[Indigo]

Paul,

Thank you, I was thinking that might be it on my drive home. I will have to investigate it in the morning when I go back to work. I appreciate your patience with me this afternoon

 

[Indigo]

Paul,

I hope you are still available to help me out here.....

Its not working properly. I'm not getting several hundred thousand entries anymore but its not collecting the CheckItem or ResponseID data. Of course since the code does not tell it what that is I need to point to it, but I'm not clear how. The values are in the subform and I tried:

    HoldCheckItem = Forms!frmMSAudit!subfrmMSCheck.Form!CheckItem.Value
    HoldResponseID = Forms!frmMSAudit!subfrmMSCheck.Form!ResponseID.Value

But I get a Type Mismatch error for the CheckItem (which I'm sure I can correct) so I commented it out to test the ResponseID and it gave me the same value all 10 times and it wasn't even the correct value. Sorry, but I'm getting more and more confused.

 

[pbaldy]

I'm still on duty!

Are those 2 coming from the MSCheck table, and thus the second recordset? If so:

MSAuditRS.Fields("ResponseID").Value = MSHistoryRS!ResponseID

Because you're looping that recordset, you'll get a different value every time (or more accurately, the value from each record). Note that I used a different syntax to refer to the recordset field, which is functionally identical to the method you used.

 

[Indigo]

FANTASTIC! That did it!

Thank you soooo much!!!!