By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,994 Members | 2,036 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,994 IT Pros & Developers. It's quick & easy.

Check if no records returned by query

P: 2
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub ProjCombo_AfterUpdate()
  2. Dim sAcctSource As String, sDeptSource As String
  3. '
  4. sAcctSource = "SELECT ComboMaster.AcctID, ComboMaster.BUnitID, ComboMaster.ProjID, " & _
  5.                 "ComboMaster.ActivityID FROM ComboMaster WHERE (((ComboMaster.AcctID) Is Not Null) " & _
  6.                 "AND ((ComboMaster.BUnitID)=""" & Me.ProjCombo.Column(3) & """) AND " & _
  7.                 "((ComboMaster.ProjID)=""" & Me.ProjCombo.Value & """) AND " & _
  8.                 "((ComboMaster.ActivityID)=""" & Me.ProjCombo.Column(2) & """))"
  9. Me.AcctCombo.RowSource = sAcctSource
  10. 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
Mar 15 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,349
Open a recordset with that SQL and use a .RecordCount.
Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2. Set rst = CurrentDb.OpenRecordset("SQL Statement")
  3. If rst.RecordCount = 0 Then ...
  4.  
Mar 16 '07 #2

P: 2
Open a recordset with that SQL and use a .RecordCount.
Expand|Select|Wrap|Line Numbers
  1. Dim rst As Recordset
  2. Set rst = CurrentDb.OpenRecordset("SQL Statement")
  3. If rst.RecordCount = 0 Then ...
  4.  
Thanks Rabbit. Worked like a champ
Mar 16 '07 #3

Rabbit
Expert Mod 10K+
P: 12,349
Thanks Rabbit. Worked like a champ
Not a problem, good luck.
Mar 16 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.