help with a form

[Guest]

I have a data entry form which populates a table. How do I make the form to
work so that if a user wants to look up records for a particular field, he is
able to do it?
So for a field say called FRUITS, in forms, how does a user retrieve all
records that relate to just APPLES and view it within forms. I used a combo
box but it only brings back the first instance of APPLES and not subsequent
ones. Yes I could set up a query and then runa form off that query but I
wanted it to be more flexible. Also, sometimes the user may need to look up
APPLES only grown in CALIFORNIA.

Please help.

 

[Larry Daugherty]

Have you looked into Filter By Form?

 

[Guest]

Thank you Larry. That would work, but I was trying for an easier way for a
user to filter.

 

[Guest]

Yes, I think I will use the Filter By Form - but what is happening is that my
combo box is displaying the FRUIT multiple times, for example if I have 3
records for APPLE, the drop down shows it 3 times. Is there a way to avoid
this?

 

[John W. Vinson]

I have a data entry form which populates a table. How do I make the form to
work so that if a user wants to look up records for a particular field, he is
able to do it?
So for a field say called FRUITS, in forms, how does a user retrieve all
records that relate to just APPLES and view it within forms. I used a combo
box but it only brings back the first instance of APPLES and not subsequent
ones. Yes I could set up a query and then runa form off that query but I
wanted it to be more flexible. Also, sometimes the user may need to look up
APPLES only grown in CALIFORNIA.

You can put (unbound!!! very important!) combo boxes in, say, the form's
Header, and use them to set the Filter property of the form. For instance you
could have one combo with a row source

SELECT DISTINCT Fruit FROM Fruits ORDER BY Fruit;

and another

SELECT State FROM States ORDER BY State;

or, if you don't have a table of states,

SELECT DISTINCT State FROM yourtable ORDER BY State;

In the AfterUpdate event of each combo box use code like:

Private Sub cboFilterFruits_AfterUpdate()
If Me.Filter <> "" Then
' if there is already a filter, add to it
Me.Filter = Me.Filter & " AND"
End If
Me.Filter = Me.Filter & "[FRUIT] = '" & Me.cboFilterFruits & "'"
Me.FilterOn = True
End Sub


John W. Vinson [MVP]