Check if no records returned by query

[kpdgsyi]

I have a child combo box whose values depend on a user selected value in the parent combo box. If the child row source query returns no rows, the listcount property still is set to 1 in Access 2003. And if the query returns 1 valid row, the list count is set to 1.

I need to set the enabled = False on the child combo box if there are no valid rows from the query. see vba below.

thks in advance.

Private Sub ProjCombo_AfterUpdate()
Dim sAcctSource As String, sDeptSource As String
'
sAcctSource = "SELECT ComboMaster.AcctID, ComboMaster.BUnitID, ComboMaster.ProjID, " & _
                "ComboMaster.ActivityID FROM ComboMaster WHERE (((ComboMaster.AcctID) Is Not Null) " & _
                "AND ((ComboMaster.BUnitID)=""" & Me.ProjCombo.Column(3) & """) AND " & _
                "((ComboMaster.ProjID)=""" & Me.ProjCombo.Value & """) AND " & _
                "((ComboMaster.ActivityID)=""" & Me.ProjCombo.Column(2) & """))"
Me.AcctCombo.RowSource = sAcctSource
Me.AcctCombo.Requery

'
it's here where i check the listcount of acctcombo to determine if i need to hide account combo. problem is that the listcount returns 1 regardless if there are no rows returned by query or 1 valid row.
End Sub

[Rabbit]

Open a recordset with that SQL and use a .RecordCount.

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SQL Statement")
If rst.RecordCount = 0 Then ...
 

[kpdgsyi]

Open a recordset with that SQL and use a .RecordCount.

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SQL Statement")
If rst.RecordCount = 0 Then ...
 

Thanks Rabbit. Worked like a champ

[Rabbit]

Thanks Rabbit. Worked like a champ

Not a problem, good luck.