Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    New Hampshire Coast
    Posts
    18

    Unanswered: how to notify when query returns no records

    hi, I have a form that has 22 drop downs that allows a user to search data. There are circumstances where 2 (or more) of the drop downs selected in conjunction will return no data. When this happens...the form goes blank...and it looks as though the database freezes. This can be corrected by clicking the "unfilter" icon...but most of my users aren't that Access savy.

    Behind the scenes my code looks like:
    Private Sub Set_Filter_Click()
    Dim strSQL As String, intcounter As Integer

    'Build SQL String
    'chr(34) is an open or close quotation mark
    For intcounter = 1 To 22
    If Me("Filter" & intcounter) <> "" Then
    strSQL = strSQL & "[" & Me("Filter" & intcounter).Tag & "] " & " = " _
    & Chr(34) & Me("Filter" & intcounter) & Chr(34) & " And "
    End If
    Next


    If strSQL <> "" Then
    'strip last " And "
    strSQL = Left(strSQL, (Len(strSQL) - 5))

    'set the filter property
    Forms![frmsupervisorssearch].Filter = strSQL
    Forms![frmsupervisorssearch].FilterOn = True

    End If

    My question is...how can I create a message box that pops up that tells the user there is no data. Also...how can I prevent the form from going blank. I was thinking I could sort of pre-process the query and not set the filter if the record count was 0...but I haven't had luck with that.

    Thanks in advance for your help.

  2. #2
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    You can use code like this in the OnCurrent event to test to see if there are no records. If the norecord condition is found, then you can change the filterOnto be False, or whatever you would like to do at that point.
    HTH,

  3. #3
    Join Date
    Mar 2004
    Location
    New Hampshire Coast
    Posts
    18

    follow up

    Hey Vic,
    thanks for the advise. this is the code I added to the existing code to find where a query returned no records:

    ' recordset variables
    Dim rst As ADODB.Recordset
    Dim con As ADODB.Connection
    Dim strQuery As String

    ' open connection
    Set con = CurrentProject.Connection
    Set rst = New ADODB.Recordset

    If strSQL <> "" Then

    strQuery = "SELECT * from [qryAuditReportPg1] where" & strSQL

    rst.Open strQuery, con, adOpenStatic, adLockOptimistic, 0

    If rst.RecordCount = 0 Then
    MsgBox "No Records, please select again"
    Else

    'set the filter property
    Forms![frmsupervisorssearch].Filter = strSQL
    Forms![frmsupervisorssearch].FilterOn = True

    End If

    rst.Close
    Set rst = Nothing
    Set con = Nothing

    Else
    MsgBox "please select criteria"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •