Move to next record

[Adam]

Hi,

Main form's subform shows a set of filtered records.

I am trying to write a VB instruction to perform a task on
each row in a specific field (a checkbox field
called "Card Printed" which if printed, yes, else, no). I
am able to carry out the task on the first record, but I
am unable to make it go to the next record,ie 2nd, 3rd,
etc.

Is there a simple vb instruction to go to the next record
on a SUBFORM? Ideally, I would put this in a For..next
statement which would process each of all available
records showing in the subform. I have tried the
DoCmd.GoToRecord, but to no avail.

Can you please help?

Thanks heaps.

Adam

 

[Steve Schapel]

Adam,

It sounds like you are running this procedure from the main form.
Right? Possibley the simplest approach woulld be to clone a recordset
from the subform, and work with it. Code might look like...

Dim rst As DAO.Recordset
Set rst = Me.NameOfSubform.Form.RecordsetClone
With rst
Do Until .EOF
If whatever Then
!Card_Printed = -1
Else
!Card_printed = 0
EndIf
.MoveNext
Loop
End With
Set rst = Nothing

 

[Adam]

Thanks for you response Steve,

I tried to implement your suggested solution but it didn't
work. This is what I typed.

Dim rst As DAO.Recordset
Set rst = Me.Jobs_by_Date_Subform.Form.RecordsetClone
With rst
Do Until .EOF
If [JobCardPrinted] = False Then
![JobCardPrinted] = -1
Else
![JobCardPrinted] = 0
End If
.MoveNext
Loop
End With
' Set rst = Nothing


However this is what I changed to make it work.

'Works well
Forms![Jobs by Date]![Jobs by Date subform].SetFocus
Dim rst As DAO.Recordset
Set rst = Me![Jobs by Date subform].Form.RecordsetClone
With rst
Do Until .EOF
Me![Jobs by Date subform]![JobCardPrinted] = True
Me![Jobs by Date subform]![NumberOfPrints] = [Jobs by
Date subform]![NumberOfPrints] + 1
.MoveNext
DoCmd.GoToRecord , , acNext
Loop
End With

To further clarify,

- subform name is [Jobs by Date Subform]
- control name on subform is [JobCardPrinted]


Big thanks for your help

Adam

 

[Steve Schapel]

Adam,

Glad you got it working. On the basis of what you have said, I would
expect that this would also work for you...

Dim rst As DAO.Recordset
Set rst = Me.Jobs_by_Date_Subform.Form.RecordsetClone
With rst
Do Until .EOF
.Edit
![JobCardPrinted] = -1
![NumberOfPrints] = ![NumberOfPrints] + 1
.Update
.MoveNext
Loop
End With
Set rst = Nothing