[MS_AccessPros] Re: Search anywhere in a field

 

Hi John,

Here's a screenshot of the form...

https://xa.yimg.com/df/MS_Access_Professionals/route_card_form_LI.jpg?token=I54UAmUvprCosAKj1dSthvtVbHCmywDv0wKa6p_1BfetIU8DXh7sXvFbajPBgEbOUHaB_xCxQPjqpSkbqdSwBr5UzP0Qv1f3C18lw15OHwZEWbWZ0Q&type=download

	https://xa.yimg.com/df/MS_Access_Professionals/route_card_form_LI.jpg?token=I54UAmUvprCosAKj1dSthvtV...
	View on xa.yimg.com	Preview by Yahoo

Results of the filter...

Appear in the Detail section of the form as in the screenshot

Row sources...

When the form opens the rowsources are set with the form's OnCurrent event...

    Me.CustomerFilter.RowSource = "SELECT DISTINCT Customer FROM tblRoutecard ORDER BY Customer"
    Me.PartNumberFilter.RowSource = "SELECT DISTINCT PartNumber FROM tblRoutecard ORDER BY PartNumber"
    Me.BatchNumberFilter.RowSource = "SELECT DISTINCT BatchNumber FROM tblRoutecard ORDER BY BatchNumber"

Customer combo - SELECT DISTINCT Customer FROM tblRoutecard ORDER BY Customer;
Part number combo - row source is code on the AfterUpdate event of the customer combo...

    Private Sub CustomerFilter_AfterUpdate()

    Me.PartNumberFilter.RowSource = "SELECT DISTINCT PartNumber FROM tblRoutecard " & _
    "WHERE Customer = '" & Me.CustomerFilter & "' ORDER BY PartNumber"
    End Sub -

Batch number combo - row source is code on the AfterUpdate event of the part number combo...

    Private Sub PartNumberFilter_AfterUpdate()
   
    Me.BatchNumberFilter.RowSource = "SELECT DISTINCT BatchNumber FROM tblRoutecard " & _
    "WHERE PartNumber = '" & Me.PartNumberFilter & "' ORDER BY BatchNumber"
    End Sub



And here's the code attached to the Apply Filter button...

Private Sub ApplyFiltersButton_Click()

'Set the filter string
Dim mFilter As String

'ensure the string is empty to start
mFilter = ""
'ensure the filter is off and empty to start
Me.FilterOn = False
Me.Filter = ""

'if all filter selectors are empty, do nothing
If IsNull(Me.CustomerFilter) And IsNull(Me.PartNumberFilter) And IsNull(Me.BatchNumberFilter) Then
Me.Requery
End If

If Len(Me.CustomerFilter) > 0 Then
'CustomerSupplier is selected so add it to the string...
    mFilter = "[Customer]= '" & Me.CustomerFilter & "'"
End If
       
If Len(Me.PartNumberFilter) > 0 Then
'Keyword is selected so add it to the string...
    If Len(mFilter) > 0 Then
    'String already has something in it from CustomeSupplierFilter, so add AND first...
        mFilter = mFilter & " AND "
    End If
'If CustomerSupplierFilter only following is used, if keyword also the AND bit above is also used
mFilter = mFilter & "[PartNumber]= '" & Me.PartNumberFilter & "'"
End If

If Len(Me.BatchNumberFilter) > 0 Then
'Keyword is selected so add it to the string...
    If Len(mFilter) > 0 Then
    'String already has something in it from CustomeSupplierFilter and PartNumberFilter, so add AND first...
        mFilter = mFilter & " AND "
    End If
'If CustomerSupplierFilter and PartNumberFilter only following is used, if keyword also the AND bit above is also used
mFilter = mFilter & "[BatchNumber]= '" & Me.BatchNumberFilter & "'"
End If

I'm sure there will be much tidier/simple ways to do this!!!

Nige

__._,_.___

---

Posted by: nigel@pegasusconsulting.co.uk

---

Reply via web post

•

Reply to sender

•

Reply to group

•

Start a New Topic

•

Messages in this topic (3)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• New Members 1

• Privacy • Unsubscribe • Terms of Use

.

__,_._,___