Checking recordset for null

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that asks the user for filter information. When the user clicks
"OK" that form closes and another form opens showing the data with the filter
applied, and the user can update data, etc. on the second form. Sometimes
users search for items that do not exist. When this happens, currently the
second form opens and is blank.

I'd like the Event Procedure on the first form's OK Button On-Click event to
check for null values before the second form is opened, and if it finds that
there is no data to display, to inform the user that their search yielded no
results and then abort the procedure. I can't figure out how to test the
filtered recordset for null values to give the user this warning. Any help is
appreciated.

Thanks,
RB
 
Hi,
there is a IsNull() function, which you can use. If you want to check if
recordset has some records - then you can use it .EOF or .RecordCount
properties
 
Presumably you are building a Filter string, and applying it to the form?

If so, you could DLookup() your table to see if you get a match, and if not,
notify the user instead of opening the form. Use the Filter string as the
3rd argument of DLookup(), and the same table or query as the 2nd argument.

Alternatively, cancel the Open event of the search form if there are no
matches:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No Matches"
End If
End Sub

Personally, I find it easier to show the results in the search form. Basic
downloadable example for Access 2000 and later:
http://allenbrowne.com/unlinked/Search2000.zip
 
Sometimes
users search for items that do not exist. When this happens, currently
the second form opens and is blank.
Click to expand...

private sub cmdOKButton_Click()

dim criterion as string

' do what ever is needed to build the filter string
criterion = BuildTheCriterionString()

' now see if there are any records
dim recordCount as long
recordCount = DCount("*", "MyTable", criterion)

If recordCount > 0 then
docmd.openform "TheOtherForm",,,,criterion,,etc

else
' nothing found
msgbox "Sorry, nothing matches your search"

end if

end sub



Hope that helps


Tim F
 
Back
Top