Combo Box filter

[molly]

I have another combo box for a customers affiliation. It works fine. I would like to refine the lookup to give me only the records that apply.

For example, I may lookup an Affiliate named Jones Corporation and get 30 correct records. However the rest of the records show from 31 to 1800. I would like it to filter and show just the 30 records and force me to hit my Reset button when i am done. This would prevent me from accidently making changes on the 31st and out records which do not apply.

The After Update below works. Except for picking ONLY the desired AffiliateName records.

Secondly, this example is for a text field name AffiliateName. Can you also tell me the code to make similar code for a number field named AffiliateID ?

Thanks
Molly


===============================================

Private Sub cboAffiliateNameFilter_AfterUpdate()

' Find Affiliate Name which is Text
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone

' If AffiliateName is text per PHV of Tek-Tips for apostrophe situations.

rs.FindFirst "[AffiliateName]='" & Replace(Me![cboAffiliateNameFilter], "'", "''") & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

' Set value of combo box equal to an empty string
Me!cboAffiliateNameFilter.Value = ""

End Sub

 

[PHV]

Private Sub cboAffiliateNameFilter_AfterUpdate()
Me.FilterOn = False
Me.Filter "AffiliateName='" & Replace(Me!cboAffiliateNameFilter, "'", "''") & "'"
' Me.Filter "AffiliateID=" & Me!cboAffiliateIDFilter
Me.FilterOn = True
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[molly]

Hi PHV. I tried this and it gave me an error where you see in row 3, the Me.Filter. The word Filter was highlighted in the debug mode.

Also, i remmed out my next to last line, which would clear out the combo box after it found my record. Does your Me.FilterOn = True take its place ?

thanks
Molly


Private Sub cboAffiliateNameFilter_AfterUpdate()

Me.FilterOn = False
Me.Filter "AffiliateName='" & Replace(Me!cboAffiliateNameFilter, "'", "''") & "'"

'03-05-2009 if this were a number field, then use - per PHV of tek-tips
' Me.Filter "AffiliateID=" & Me!cboAffiliateIDFilter

Me.FilterOn = True

' Set value of combo box equal to an empty string
'Me!cboAffiliateNameFilter.Value = ""

End Sub

 

[PHV]

Sorry for the typo:
Me.Filter {!]=[/!] "AffiliateName='" & Replace(Me!cboAffiliateNameFilter, "'", "''") & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886