Loop trough a recordset

[GK22]

I made a form based on a recordsource named "ProjectList"
On this form is a button "CountOff".

I wrote next code:
me.countoff.caption = Dcount("[worknr]","[qrysubform1]",
"[leadnr] = " Me!leadnr)
In this case: the button has the same number in the caption
for each record
but I want to loop through the recordset, so each button had an other number on it.
Which code can loop through the recordset;
For each .... in ......
me.countoff.caption = Dcount ("[worknr]","[qrysubform1]", "[leadnr] = " Me!leadnr)
Next ....

something like this?
If it's possible can somebody help me?

Thanx in advance,

Gerard

 

[AlexMidd]

To loop through a recordset, use code like this

With Rs1
.movefirst
While not .EOF
... put code to be actioned here ...
.movenext
Wend
End With

This will loop through the records until EOF (end of file) is reached. You could use a while loop (as above) or a do unitl depending on your needs. Have fun! )

Alex Middleton

 

[MoGryph]

GK22:
The problem with your count, is that you have to decide on how you want the numbering to go. If you want a unique number in there, you might as well just add an AutoNumber field to your table, and set your caption to this.

Otherwise, I'm not sure how you want your numbering to go. Do you want it in a chronological order of when the record was created? In that case, you're going to need some field in your table that has the create date. If not, you have to decide on some order in which these record should normally be sorted. As you probably know already, the user can override the sorting order of the form, most likely breaking your numbering system.

If all you care about, is having the number of the currently selected record, in the whatever the current sort order is, you should be able to use the CurrentRecord property of the form. (The same as you see on the bottom of your form, for your Record # of # if you have Navigators Buttons enabled.)

Private Sub Form_Current()
Me.countoff.Caption = Me.CurrentRecord
End Sub

If you're worried about a count, in relation to any other field, it's going to take a bit more work to make sure that the numbering stays correct. I've figured out how to do it, but it's fairly complicated. You're going to have to somehow uniquely identify the current record (for my example, I'm using the primary key of my table "ID", create a recordsetclone of the "Me" (my current form)'s recordset, change the Sort property of the new recordset, and create another recordset, based off the recordsetclone (sorted). Enumerate through the newest, while incrementing a counter, and exiting the enumeration loop when you've found your unique field.

If that was too confusing, perhaps my code will make it look clearer.

Private Sub Form_Current()
    Dim lCount As Long             ' My count variable
    Dim rst As Recordset           ' Recordset object to hold a clone of Me
    Dim sRst As Recordset          ' new Recordset, based off rst sorted.
    Set rst = Me.RecordsetClone    ' Create the Clone
    
    ' Set the Sort property *NOTE: This does not actually sort it.
    '  But any recordsets created from it will be.
    rst.Sort = "sortedField"
    
    ' Create the new recodset
    Set sRst = rst.OpenRecordset()
    
    ' Initialize the counter
    lCount = 0
    
    ' Move to the first record in the sorted recordset
    sRst.MoveFirst
    
    ' Enumerate through all records, until last record.
    Do While Not (sRst.EOF)
        lCount = lCount + 1 ' Inc counter
        If sRst!ID = Me.ID.Value Then Exit Do ' Leave if found
        sRst.MoveNext ' Go to next record
    Loop
    
    Me.btnNum.Caption = lCount ' Modify the caption of the button
    Set rst = Nothing
    Set sRst = Nothing
End Sub

-MoGryph
[8O)