Error In RecordSet Loop . .

[Evagrius]

Hi,

I keep getting an "item not found in collection error"! Can anyone please spot my mistake? I do have a textbox name "Building NO". When I step through, it seems that this line is causing the error. Thank you for any help.

MyRecordset!Building_NO

Private Sub Form_Current()
Dim MyRecordset As DAO.Recordset
Set MyRecordset = Me.RecordsetClone
Dim i As Long, iCounter As Long
MyRecordset.MoveFirst
    Do Until MyRecordset.EOF
        If MyRecordset!Building_NO = Me.Building_NO Then iCounter = iCounter + 1
        MyRecordset.MoveNext
    Loop

MyBldLabel.Caption = "NO. Of Records for Bldg" & " " & Me.Building_NO.Value
Me.BldCount = iCounter
End Sub

 

[boblarson]

You have a text box named that but do you have a FIELD in the table named that?

 

[Evagrius]

Hi Bod,

Yes, I do! When I step through the code and I place my curser on,

Me.Building_NO Then iCounter = iCounter + 1

I get a value for what is in the Building_NO Field. The actual field name is "Building NO" - I don't know if that makes a difference.

 

[boblarson]

Hi Bod,

Yes, I do! When I step through the code and I place my curser on,

Me.Building_NO Then iCounter = iCounter + 1

I get a value for what is in the Building_NO Field. The actual field name is "Building NO" - I don't know if that makes a difference.

No, Me.Building_NO is the CONTROL NAME, not the field name in the underlying recordset. You need to change THIS part to:

If MyRecordset![Building NO] = Me.Building_NO Then iCounter = iCounter + 1

withOUT the underscore and using square brackets because there is a space (another reason to leave spaces OUT of field and object names - it just confuses matters).

 

[Evagrius]

Bob - as usual you have resolved my issue with expert ease

Going forward I will no longer use spaces in Field Names. Thank you Sir!!