Drop Down Combo Filtering

[Dev1]

All-

I'm new to this forum and i've been working with acces for about 2 days now. I have a form in which I have two combo boxes and depending on what is selected on the first dropdown the second one gets populated accordingly, which is great! There are two problems;

First the when I select the value from the second drop down the value in the first and second combo box go away!!

Second when I remove the filter the values for all the other text fields show up and I can scroll through each record. So it appears that my filter is not working in the way I want it to which is when the Second value is picked from the combo box all subsequent text fields will be popluated. A little back ground: there is only one table, I have include the following in the first combo box event procedure:

Private Sub Combo81_AfterUpdate()
    If IsNull(Me.Combo81) Then
        Me.FilterOn = False
     Else
        Me.Filter = "Company= """ & Me.Combo81 & """"
        Me.FilterOn = True
    End If
End Sub

to incorporate the filtering. The second combo box is bieng fed via a query as such:

SELECT DISTINCT [Sales Funnel1].Company
FROM [Sales Funnel1]
WHERE ((([Sales Funnel1].[ASC Rep Reporting])=Forms!Opportunity!Combo83))
ORDER BY [Sales Funnel1].Company;

Any thoughts would be greatly apprieciated!!

-Dev1

[sierra7]

Hi
I understand what you are trying to do and Access is very good at this sort of thing, but I have got lost trying to follow your code.

I take it that your first combo box is Combo81 but why does your Select statement refere to Combo83 ? It looks like it is on a separate form ?

Also, Combo81 has already selected for 'Company', so presumably your second combo box will be selecting on some other field but it seems to be selecting 'Company' again. I give up.

Give a bit more detail and I (or someone) can try again

S7

[NeoPa]

Try checking out Example Filtering on a Form.

PS. Quotes withIN a SQL string (Filters etc) should ideally be single-quotes ('). This is also easier to work with as they are not confused with VBA string delimiters ("). Check out Quotes (') and Double-Quotes (") - Where and When to use them.