Counting filtered Records on a form using VBA 1

[Ray1127]

I have a Form in MS Access 2010 The form has a number of fields but my concern is with 2 combo boxes

The items in the 2nd combobox are subtypes of the items in the 1st combo box. When an item in the first combo box is selected the form filters the data to match the existing records. Then when an item in the second combo box is selected the form further filters the data to the sub types. This works as I want it to. The problem is that I need a count of the filtered records so that if the count is 0 It automatically goes to a new record.

I created a global variable called rcdcount but I can't get the filtered record count to populate correctly. I have 2 records and 6 choices in the first combo box so 4 are not being used when I click on a selection I know doesn't exist it still doesn't go to a new record. Here's the code

Private Sub cbo_Inc_type_Change()
Dim lngrcds As Long

Me.Filter = "Incidenttype = """ & Me.cbo_Inc_type & """"
Me.FilterOn = True
lngrcds = recordsource.count
If lngrcds = 0 Then
MsgBox "There are no current " & Me.cbo_Inc_type & " incidents. ", vbCritical, "No Data found"
Me.FilterOn = False
DoCmd.GoToRecord , , acNewRec

End If
Me.Refresh
cbo_incident_subtype.Requery
End Sub

I've tried using Count([incidenttype]) but that just gives an error. How do I get the Record count of filtered records using VBA????

 

[seaport]

Try using the code:

lngrcds = Me.Recordset.Count

 

[PHV]

I'd replace this:
lngrcds = recordsource.count
with this:
lngrcds = Me.RecordSet.RecordCount

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[seaport]

PHV is right. Sorry for my typo.