no records

[slimjen]

All, I have a search form. Its working fine. But I need to put a msgbox when a record is not found. Here is my code:

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    varWhere = Null  ' Main filter
  
    ' Check for LIKE custid
    If Me.txtCustID > "" Then
        varWhere = varWhere & "[custid] LIKE """ & Me.txtCustID & "*"" AND "
    End If
   
'     Check for LIKE Description
    If Me.txtDesc > "" Then
        varWhere = varWhere & "[name1] LIKE """ & Me.txtDesc & "*"" AND "
    End If
    ' Check for State
    If Me.txtSTATE > "" Then
        varWhere = varWhere & "[state_abbr] LIKE """ & Me.txtSTATE & "*"" AND "
    End If
 
    ' Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
   
    BuildFilter = varWhere
   
'    If varWhere.NoMatch Then
'        MsgBox "Record not found"
'    End If

I tried the last piece but no msg box was presented. this is my code for the button:

Private Sub btnSearch_Click()
   
    ' Update the record source
   
Me.sfrmform.SearchForm.RecordSource = "SELECT * FROM qrySearch " & BuildFilter
 
    ' Requery the subform
   
    Me.sfrmform.SearchForm.Requery
   
End Sub

Where do I put the msgbox??

[JeffChr]

At the end of your btnSearch_Click() sub, after me.sfrmform.SearchForm.Requery statement you can try:

If me.sfrmform.SearchForm.RecordCount<1 then msgbox(........

[slimjen]

Thanks for replying; but this didn't do anything???

[ssanfu]

In the Function BuildFilter, this code is invalid:
' If varWhere.NoMatch Then
' MsgBox "Record not found"
' End If

varWhere is a variant variable. It does not have a NoMatch property. It just holds data, so the msgbox line would never execute.

Looking at the code:

Private Sub btnSearch_Click()
   
    ' Update the record source
   
Me.sfrmform.SearchForm.RecordSource = "SELECT * FROM qrySearch " & BuildFilter
 
    ' Requery the subform
   
    Me.sfrmform.SearchForm.Requery
   
End Sub

You could use

Private Sub btnSearch_Click()
   Dim MyFilter as Variant
 
   MyFilter = BuildFilter
  
    ' Update the record source
    Me.sfrmform.SearchForm.RecordSource = "SELECT * FROM qrySearch " & MyFilter 
 
    ' Requery the subform
    Me.sfrmform.SearchForm.Requery
   
End Sub

Now you can see the Filter property returned by the function (if you single step thru the code). But that won't tell you if the SQL returns records.

I am confused on the syntax of the lines that set the form record source and the re-query. I can't tell which is the main form (name) and which is the subform.

Is "sfrmform" the subform name?


This is the reference I use when trying to check form/subform properties (I have it printed out - it is available in DOC format to download)

Refer to Form and Subform properties and controls
http://access.mvps.org/access/forms/frm0031.htm



You could check the recordsetclone property of the subform to see if there are/were records returned after the requery. Then use the message boxif no records are in the subform.

[slimjen]

In the Function BuildFilter, this code is invalid:
' If varWhere.NoMatch Then
' MsgBox "Record not found"
' End If

varWhere is a variant variable. It does not have a NoMatch property. It just holds data, so the msgbox line would never execute.

Looking at the code:

Private Sub btnSearch_Click()
   
    ' Update the record source
   
Me.sfrmform.SearchForm.RecordSource = "SELECT * FROM qrySearch " & BuildFilter
 
    ' Requery the subform
   
    Me.sfrmform.SearchForm.Requery
   
End Sub

You could use

Private Sub btnSearch_Click()
   Dim MyFilter as Variant
 
   MyFilter = BuildFilter
  
    ' Update the record source
    Me.sfrmform.SearchForm.RecordSource = "SELECT * FROM qrySearch " & MyFilter 
 
    ' Requery the subform
    Me.sfrmform.SearchForm.Requery
   
End Sub

Now you can see the Filter property returned by the function (if you single step thru the code). But that won't tell you if the SQL returns records.

I am confused on the syntax of the lines that set the form record source and the re-query. I can't tell which is the main form (name) and which is the subform.

Is "sfrmform" the subform name?


This is the reference I use when trying to check form/subform properties (I have it printed out - it is available in DOC format to download)

Refer to Form and Subform properties and controls
http://access.mvps.org/access/forms/frm0031.htm



You could check the recordsetclone property of the subform to see if there are/were records returned after the requery. Then use the message boxif no records are in the subform.

Thank you for your reply. I was just trying something but that part of the code was commented out. I didn't put the name of the form I am using. Moving too fast but I am reference the form correctly. Just a little background: I am using an unbound form with txtboxes to search with 2 subforms. The main subform uses the query reference in the filter. Everything works fine but if when the user inputs a custid that's not valid; nothing happens. I want a msgbox to pop up that the custid there are searching for is not valid. I will try your suggestion tomorrow when I return to work. Thanks

[ssanfu]

the user inputs a custid that's not valid

How about using a combo box?? Then they could only select a (valid) CustID from the list.

[slimjen]

Ok using some of you suggestions and a little more brain storming I came up with this:

Private Sub btnSearch_Click()
    Dim MyFilter As Variant
    MyFilter = BuildFilter
   
    ' Update the record source
   
Me.sfrmCustSearch.Form.RecordSource = "SELECT * FROM qryCustSearch " & MyFilter
 
If Me.sfrmCustSearch.Form.Recordset.EOF And Me.sfrmCustSearch.Form.Recordset.BOF Then
MsgBox "No records found"
 
End If
   
End Sub

This worked the way I wanted. Yay!

Ok now I noticed that when I search on anything other than the CustID like Cust Name the other subforms do not return any records. Probably because the name is not in the recordsource for the other subforms?? I thought about trying to reference the custid of the main subform but how do I do this? I thought about maybe putting code in the buildfilter. Partial Code:

'     Check for LIKE Description
    If Me.txtDesc > "" Then
        varWhere = varWhere & "[name1] LIKE """ & Me.txtDesc & "*"" AND "
    End If

?? Any suggestions?

[ssanfu]

Yes, the field you are searching for must be in the record source (the FROM clause source - be it a table or query).
You are using a query "qryCustSearch", so "name1" would have to be in the query. Think of the query as a virtual table. If you were to substitute a table without the field "name1" for the query, would you expect to be able to return records searching on "name1"?

Probably because the name is not in the recordsource for the other subforms??

How many subforms are there??
What is the SQL of the queries?

I thought about trying to reference the custid of the main subform but how do I do this?

Are the search fields on the MainForm? And the button "btnSearch"? I would reference control for "custid" using Me.custid (if that is the name of the control. I always rename to control different from the bound field so Access doesn't get confused. (Mostly so I don't get confused on whether it is the field name or the control name I am referring to.)

Since I have not seen what your search form looks like, and haven't seen your dB, I am starting to get confused.

The build filter sub already has lines to search by name......