How to get a row count

[GraeagleBill]

The follow segment of code works as intended. However, there are situations where the SELECT will return zero records. What can I add to the SELECT so I can detect zero recordcount?

Me.cboDescriptions.RowSource = "SELECT Description FROM tblRegister WHERE (((TDate)>#" & BegPeriod & "#) AND ((TTypeID)" & TTexp & ")) GROUP BY Description;"
                               
Me.cboDescriptions.Visible = True
Me.cboDescriptions.SetFocus
Me.cboDescriptions.Dropdown

[Bob Fitz]

Perhaps you could use the DCount() function to return the number of records

[ssanfu]

Me.cboDescriptions.RowSource = "SELECT Description FROM tblRegister WHERE (((TDate)>#" & BegPeriod & "#) AND ((TTypeID)" & TTexp & ")) GROUP BY Description;"

Is there a missing operator?? Maybe an "="???

[GraeagleBill]

Never fails, if one doesn't post enough info it makes for un-answered questions. Here's the whole function:

Private Function FreqUsed(FUID As Integer)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' From settings, get the number of months the user considers to be "Recent".  From
' that, get a list of descriptions used with the current transaction type and set
' that list as the RowSource of a combo box from which the user can chose or ignore.
' (6 months is the default if user hasn't defined otherwise.)
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim varTemp As Variant
Dim BegPeriod As Date
Dim intNumDays As Integer
Dim TTexp As String


varTemp = DLookup("[OrgRecent]", "tblSettings")
If IsNull(varTemp) Or IsEmpty(varTemp) Or Not IsNumeric(varTemp) Then varTemp = 6

BegPeriod = CDate(CLng(Date) - varTemp * 30)                  'Okay, BegPeriod now approximately varTemp months ago

    If FUID > 50 Then
        TTexp = "> 50"
    Else
        TTexp = "= " & FUID
    End If
    

Me.cboDescriptions.RowSource = "SELECT Description FROM tblRegister WHERE (((TDate)>#" & BegPeriod & "#) AND ((TTypeID)" & TTexp & ")) GROUP BY Description;"
                               
Me.cboDescriptions.Visible = True
Me.cboDescriptions.SetFocus
Me.cboDescriptions.Dropdown

End Function

The first thing I tried was to save the SELECT as a string variable and then use DCount with the string as the domain but A2013 had fits with that approach. I can open a DAO recordset and get a RecordsetCount or save the SELECT as a QueryDef and use DCount, but I was thinking there could be a better way.

[davegri]

How about

debug.print cboDescriptions.listcount

[GraeagleBill]

I sensed there had to be an easier method. (Even old geezers learn something new every day )
Thanks,
Bill

If Me.cboDescriptions.ListCount > 0 Then      'Any history for the user to pick from?
    Me.cboDescriptions.Visible = True         'Yes. Make the combo visible and drop it down
    Me.cboDescriptions.SetFocus
    Me.cboDescriptions.Dropdown
End If

[RuralGuy]

Are you ready to use the Thread Tools at the top of the Thread to mark this thread as Solved?
https://www.accessforums.net/showthread.php?t=1828

[GraeagleBill]

Sorry, totally focused on Christmas and birthdays this morning.

[RuralGuy]

Completely understand. Merry Christmas!