filter the record of opened form by using combobox.

[dr]

Hi All,
Have a good day.

I want to have records filter on my opened form.

I created an unbound combo box and set its rowSource
from a textfield of query builder of the form.
I created 3 combo box.

2 combobox data type is text while the other is number.
I want when the combo box updated,my form shows
filtered record.But it seems my code doesn't work.
What could be wrong from bellow code:

Private Sub cbo_SelectModel_AfterUpdate()
Dim strFilter As String

Me.cbo_SelectLotNo.Value = ""
Me.cbo_SelectPRSNo.Value = ""
DoCmd.Requery "cbo_SelectLotNo"
DoCmd.Requery "cbo_SelectPRSNo"

If Not IsNull(Me.cbo_SelectModel) Then
stWhere = """Outgoing_qry.ModelID = " & _
Me.cbo_SelectModel.Value & """"
Me.Form.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Dennis]

dr,

I think I was trying to do the same thing I think you are trying to do.

I have a main form and two sub-forms. The main form contains the filter
questions and the two sub-forms. The first sub-form list all of the rows
that meet the search criteria. The second sub-form contains the data entry
form for the selected row.

The top of my main form had five text boxes that are used as a filter. The
user could enter any or all of the fields and then press the Apply Filter
button.

The Apply Filter button generated an SQL statement that is used as the Row
Source to first sub-form. The first sub-form list all of the rows that meet
the criteria. When the user click on any row, the first sub-form causes the
second sub to requery the table and selected the choosen row.

If this is what you are trying to do, read this thread:

http://www.microsoft.com/communitie...&p=1&tid=b707ec65-5718-4849-9119-10cde700e27d

Good luck.

 

[dr]

Hi Dennis,
Thank's for response.
Haven't read the thread you've linked.

But I'm not sure,is it possible to relation
between main form and subform where
the main forms has no bound control.

Don't it violate the rule?

Regards,
dr

 

[Dennis]

DR,

You need to read the last message or two on the thread. Tina provides a
link to a web site where she had sample code. When you get to the web site,
you have to look at the bottom of the page and click on "DEMO". This will
enable you to download a small sample mdb with a main form, and two sub forms
with a little bit of VBA code, and a small table with with to run the main
form. Look at the table to get sample data to enter into the filter. I
believe that "The Jury" is the title of one book.

Please read through that and see if it is what you want because I'm not
quite sure what you are trying to do. That is why I wrote up what I was
doing and asking you if you are trying to do the same thing.

But to answer your question, "> But I'm not sure,is it possible to relation
between main form and subform where the main forms has no bound control."
The answer to this is Yes as you will see in the sample database.

Before I can help you any more, I need to know if you are trying to:

1. Have a couple of simple filters at the top of the form that will limit
the rows that are displayed in the bottom of the form and you will use the
Access navigation to navigate through the rows.

or


2. Have a copule of filters ar the top of the form that will LIST rows of
information on in the first sub-form. Then you will pick the entry you want
from the rows and then jump / set focus to a second sub-form where you will
enter the data.

Please let me know which of the above you are trying to do.

Thanks,

 

[dr]

Dennis,

I want to achieve option 1.