How to test if RecordSource filtered out everything

[Song Su]

I have a search form. Users can type PONumber, LACCDTag, SerialNumber or
MonitorNumber in txtSearch textbox and select cboSearch to tell me which
field it is. Then I give RecordSource with strFilter.

How to test if search failed and give MsgBox?

Private Sub cboSearch_AfterUpdate()
Dim strFilter As String
Select Case cboSearch
Case "PO Number"
strFilter = " Where PO.PONumber = " & txtSearch
Case "LACCD Tag"
strFilter = " Where PODetail.LACCDTag = " & txtSearch
Case "Serial Number"
strFilter = " Where PODetail.SerialNumber = '" & txtSearch & "'"
Case "Monitor Number"
strFilter = " Where PODetail.MonitorNumber = '" & txtSearch &
"'"
End Select
Me.RecordSource = "SELECT PO.PONumber, PO.PODescription, PO.OrderDate,
PO.PayDate, PO.FundCenter, PO.OfficeID, PO.IT, PODetail.ItemDescription,
PODetail.LACCDTag, PODetail.SerialNumber, PODetail.MonitorNumber, PO.Comment
" & _
"FROM PO INNER JOIN PODetail ON PO.PONumber = PODetail.PONumber" &
strFilter
End Sub

 

[tina]

i wouldn't really call that a search, hon. a search usually takes you to a
specific record within a recordset. what you're doing is *filtering*,
restricting the records returned in the recordset according to
user-specificed criteria. when using a filter, it's pretty obvious when no
records meet the criteria, because no records are returned - not much need
there for a message box to state the obvious. maybe if you give a little
more detail on what you're trying to accomplish....?

hth

 

[Song Su]

From my RecordSource, you can see that it has mainform (PONumber,
PODescription etc) and subform (LACCDTag, SerialNumber etc).

If I provide RecordSource the first place, I can setup a cboBox only search
mainform items but not subform items.

If a single txtSearch allows users to type item either mainform or subform,
use cboSearch to tell me what you are searching for and search accordingly,
that'll be great. Is anyway to do that?

Thanks.

 

[tina]

comments inline.

From my RecordSource, you can see that it has mainform (PONumber,
PODescription etc) and subform (LACCDTag, SerialNumber etc).

okay, i didn't follow that at all. from what "RecordSource"? a RecordSource
is a property in a form or report that defines the source of the records
displayed in the form or report. looking at a RecordSource property all by
itself won't tell you whether you're in a mainform or a subform, or...

are you sure you understand what a RecordSource is? what a main form is?
what a subform is? i'm not trying to be snide; if we're not communicating
clearly, we can't work together to find a solution to the problem you
working on.

If I provide RecordSource the first place, I can setup a cboBox only search
mainform items but not subform items.

okay, maybe we better back up and talk about tables and relationships. how
about describing the tables that underly the "mainform" and the "subform",
and how those tables are related to each other.

hth

If a single txtSearch allows users to type item either mainform or subform,
use cboSearch to tell me what you are searching for and search accordingly,
that'll be great. Is anyway to do that?

we'll see if i can answer that, once i understand your tables/relationships
structure and form(s) structure.

 

[Song Su]

I have main form frmPO which based on tblPO with fields such as PONumber,
PODescription etc. Each PO has several items. So my subform frmPODetails is
based on tblPODetails with fields like ItemDescription, SerialNumber etc.
It's one to many relationship.

I can setup a cbobox to search PONumber on the mainform. How to search
SerialNumber (in the subform) if users don't know PONumber?

 

[tina]

okay, from your description, your tables/relationships structure is sound.
so is the mainform/subform setup. your problem is in the conception of how
the subform works in conjunction with the mainform. the subform's
RecordSource only returns those records that are related to the *current
record* displayed in the mainform. so, using this form setup, you can't
search for *any* record in tblPODetails that matches a specific serial
number by filtering or searching the subform records - you can only look at
the detail records related to the current PO record.

and since this is a purchase order, not a sales order, i'm assuming that a
serial number is unique to the product, but not unique to an individual item
of the product. in other words, you can order item x with serial number abc
on multiple POs. so you're going to need a different form to search by
serial number, or by any other field contained in tblPODetails, because such
a search may return multiple records that meet the criteria - each belonging
to a different PO.

bottom line - you can use your current form setup to search for a specific
PO record, or specific PO records, that meet criteria applied to one or more
fields in tblPO. to search for po detail records (and thus have access to
their "parent" PO records) that match criteria applied to one or more fields
in tblPODetails, you'll need to set up a different search form.

hth

 

[Song Su]

Got it. Now I leaned something new to me. Thank you very much.

 

[tina]

you're welcome

Got it. Now I leaned something new to me. Thank you very much.