Loop through DAO recordset 1

[nycbigapple]

Hi, i have a subform that has a command button. When i click on the command button it is going to update a query called qsponser. However, the record is only updating the first record in the subform and not iterating to the next record. When I debugg, the mailinglistid is always 4.

Thanks

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim intI As Integer

    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!qsponser'Query where update is going to be 
   Set rst = qd.OpenRecordset
   
    rst.MoveFirst
    Do Until rst.EOF

        rst.AddNew
        rst!MailingListID = MailingListID
        rst!Date = Me.txtToday
        rst!Type = Me.type
        rst!sponserID = Me.sponserID
        rst.Update
        rst.MoveNext

    
Loop

 

[AlexCuse]

How are you planning to have your MailingListID = anything but 4? I do not see anywhere that you are changing the value of your MailingListID variable.

Ignorance of certain subjects is a great part of wisdom

 

[nycbigapple]

I had int = int +1 that made the next record 5, however It was getting an unrelated errors for the table. What is in this table updates a joined table. The next mailinglistid is = 9 for this particular subform record.

How do I then get to the next record?

 

[PHV]

...
Set rst = qd.OpenRecordset
With Me.Recordset
.MoveFirst
Do Until .EOF
rst.AddNew
rst!MailingListID = Me!MailingListID
rst!Date = Me!txtToday
rst!Type = Me!type
rst!sponserID = Me!sponserID
rst.Update
.MoveNext
Loop
rst.Close
Set rst = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nycbigapple]

PHV,
I have tried looping with the With Me.recordset but it never goes to the next record. It's not iterating to the second and so on record on the subform.

It does the first record loops and still shows that it is looping back to the first record.

 

[jebry]

Hi!

You need two recordsets open, one for the source of the subform and one to add the records to.

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstAdd As DAO.Recordset
Dim qd As DAO.QueryDef
Dim intI As Integer

Set dbs = CurrentDb
Set qd = dbs.QueryDefs!qsponser'Query where update is going to be
Set rstAdd = qd.OpenRecordset
Set rst = Me.RecordsetClone

rst.MoveFirst
Do Until rst.EOF

rstAdd.AddNew
rstAdd!MailingListID = MailingListID
rstAdd!Date = Me.txtToday
rstAdd!Type = Me.type
rstAdd!sponserID = Me.sponserID
rstAdd.Update
rst.MoveNext

Your code doesn't indicate where MailingListID is coming from so it is still uncertain if that will be changing or not.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[nycbigapple]

Okay, I see thanks

 

[PHV]

looping with the With Me.recordset but it never goes to the next record
Could you please post the code exhibiting this behaviour.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886