filtering table data on a form with combo boxes

[NukeEng85]

I need to have a form that has two combo boxes that a user can select options
from, and the records that meet that criteria will appear in datasheet form
below the combo boxes.

I've gotten some really complicated coding feedback from people, but I'm so
new at A2003 (The first time I've been in Access was to try to do this, I've
gone through some basic online training classes, but they don't come close to
helping with this) I don't even know where to put the code, or how to change
it to meet my criteria.

I've also tried to do it using queries instead of coding, by copy and
changing a query someone posted, but when I create a form or subform based on
the query I get an error message "compile error. in query expression
'IIf(IsNumeric(tblStyles.StyleName),AppLoadString(tblStyles.StyleName),tbleStyles.StyleName)'"
This error appears twice, and then another box comes up that says "Invalid
use of Null" The Form Wizard continues and then says "Forms!SNM Type
Form!Combo22" and won't create a new form.

So far, all I've been able to do is create my table, and I managed to make
two combo boxes in a form that get their data from two corresponding fields
on my table. I figured out how to make them do the "SELECT DISTINCT" thing
so that they don't show repeating values when you click on the pull down tab.
I've tried "disecting" different example of forms that do what I want mine
to do, but I don't know enough about access to be able to create mine from
scratch based on someone else's form. I think I might need simple, step by
step instructions.

Thanks so much in advance

 

[Adam]

Okay im pretty new to Access, too, BUT i think i created a form this past
week that does what you want (a little more complex, but the same). So you
have a form, and in Design View, your table is in the "Detail" section? And
you want your two combo boxes to filter the table? Ill give it a try at
helping you out since no one has posted yet.

I think the first step will be go to Design View, and the right click to go
to Properties of your first combo box. Go to the Event tab, and go to
AfterUpdate and click the "..." , then click code builder to go to
VisualBasic. Place this code (inserting your combo box name and field/column
value from your table that matches your table).

Private Sub YOURcombo_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.YOURcombo) Then
strWhere = strWhere & "([field title combo matches to in table] =
""" & Me.YOURcombo & """) "
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub


I'll leave it at this so far, to see if i am helping, and to ensure you can
get this done. If all works with this step, your combo should filter the
table on your form.

Adam
Ocean ENGINEER.

 

[Wayne-I-M]

As this is your 1st form - try and simplify it but so that it still does what
you need.

1st Create 2 querys based on the table - this is better than using the table

Your main form it based on one query
Your subform is based on the other

Your main form is (I think) alredy done.

Create the subform from the 2nd query and show "all" records in the datasheet
Save the forms

Open the 2nd query in design view
In the first column you want to filter - In the criteria row - select the
build option and point the the first combo of you "main" form
In the 2nd row do the same but point to the 2nd combo on your main form
Save

Open the main form in design view
In both combos open the proerties box and in the AfterUpdate row you want to
requery the subform Me.SubFormName.Requery

That should work - for more information do a search for Query By Form (or
just QBF)

Good luck

 

[NukeEng85]

Adam,

I have entered the code as you said, but when I select something in the
combo box, nothing happens

Okay im pretty new to Access, too, BUT i think i created a form this past
week that does what you want (a little more complex, but the same). So you
have a form, and in Design View, your table is in the "Detail" section? And
you want your two combo boxes to filter the table? Ill give it a try at
helping you out since no one has posted yet.

I think the first step will be go to Design View, and the right click to go
to Properties of your first combo box. Go to the Event tab, and go to
AfterUpdate and click the "..." , then click code builder to go to
VisualBasic. Place this code (inserting your combo box name and field/column
value from your table that matches your table).

Private Sub YOURcombo_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.YOURcombo) Then
strWhere = strWhere & "([field title combo matches to in table] =
""" & Me.YOURcombo & """) "
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub


I'll leave it at this so far, to see if i am helping, and to ensure you can
get this done. If all works with this step, your combo should filter the
table on your form.

Adam
Ocean ENGINEER.

I need to have a form that has two combo boxes that a user can select options
from, and the records that meet that criteria will appear in datasheet form
below the combo boxes.

I've gotten some really complicated coding feedback from people, but I'm so
new at A2003 (The first time I've been in Access was to try to do this, I've
gone through some basic online training classes, but they don't come close to
helping with this) I don't even know where to put the code, or how to change
it to meet my criteria.

I've also tried to do it using queries instead of coding, by copy and
changing a query someone posted, but when I create a form or subform based on
the query I get an error message "compile error. in query expression
'IIf(IsNumeric(tblStyles.StyleName),AppLoadString(tblStyles.StyleName),tbleStyles.StyleName)'"
This error appears twice, and then another box comes up that says "Invalid
use of Null" The Form Wizard continues and then says "Forms!SNM Type
Form!Combo22" and won't create a new form.

So far, all I've been able to do is create my table, and I managed to make
two combo boxes in a form that get their data from two corresponding fields
on my table. I figured out how to make them do the "SELECT DISTINCT" thing
so that they don't show repeating values when you click on the pull down tab.
I've tried "disecting" different example of forms that do what I want mine
to do, but I don't know enough about access to be able to create mine from
scratch based on someone else's form. I think I might need simple, step by
step instructions.

Thanks so much in advance

 

[NukeEng85]

Wayne,

I also tried it your way, and when I choose something from the combo box, I
get this error message "Microsoft Office Access can't find the macro 'Me'.
The macro (or its macro group doesn't exist, or the macro is new but hasn't
been saved. Note that when you enter the macrogroupname.macroname syntax in
an argument, you must specify the name the macro's macro group was last saved
under."

I've saved everything, but you didn't say to create a Macro, so I didn't,
should I also have the macro that Adam suggested? and if so, how do I put
both that AND the requery Me that you gave me in the even builder?

 

[Adam]

Sorry im not one of the sites experts, but maybe in my last post the" [field
title combo matches to in table] " wasnt clear. For instance, i have a combo
named 'cboRigName' which pertains to 'Rig Name' column in my table, so i put
[Rig Name] into that line of my last posts code.

My combo boxes are in the Form Header.

your table is in your form (Detail section) though, right? For my form, i
have text boxes in the Detail section of my continuous form
(form->properties->format->default view->continuous form) which have their
control source (text box->properties->data tab->control source) set to the
column of my table i want displayed. Creating a text box for each column of
your table and placing them next to each other in the detail section, and
moving the form footer up to make the detail section small makes your form
display the table.

This may also be something i missed. In the form footer, create a text box
named 'txtFormFilter' and put this text inside of it:
=IIf(Form.FilterOn,Form.Filter,Null)

Hope im helping. Let me know whats going on, or if i need to clear anything
up.

Adam.

Adam,

I have entered the code as you said, but when I select something in the
combo box, nothing happens

Okay im pretty new to Access, too, BUT i think i created a form this past
week that does what you want (a little more complex, but the same). So you
have a form, and in Design View, your table is in the "Detail" section? And
you want your two combo boxes to filter the table? Ill give it a try at
helping you out since no one has posted yet.

I think the first step will be go to Design View, and the right click to go
to Properties of your first combo box. Go to the Event tab, and go to
AfterUpdate and click the "..." , then click code builder to go to
VisualBasic. Place this code (inserting your combo box name and field/column
value from your table that matches your table).

Private Sub YOURcombo_AfterUpdate()
Dim strWhere As String
If Not IsNull(Me.YOURcombo) Then
strWhere = strWhere & "([field title combo matches to in table] =
""" & Me.YOURcombo & """) "
End If
Me.Filter = strWhere
Me.FilterOn = True
End Sub


I'll leave it at this so far, to see if i am helping, and to ensure you can
get this done. If all works with this step, your combo should filter the
table on your form.

Adam
Ocean ENGINEER.

I need to have a form that has two combo boxes that a user can select options
from, and the records that meet that criteria will appear in datasheet form
below the combo boxes.

I've gotten some really complicated coding feedback from people, but I'm so
new at A2003 (The first time I've been in Access was to try to do this, I've
gone through some basic online training classes, but they don't come close to
helping with this) I don't even know where to put the code, or how to change
it to meet my criteria.

I've also tried to do it using queries instead of coding, by copy and
changing a query someone posted, but when I create a form or subform based on
the query I get an error message "compile error. in query expression
'IIf(IsNumeric(tblStyles.StyleName),AppLoadString(tblStyles.StyleName),tbleStyles.StyleName)'"
This error appears twice, and then another box comes up that says "Invalid
use of Null" The Form Wizard continues and then says "Forms!SNM Type
Form!Combo22" and won't create a new form.

So far, all I've been able to do is create my table, and I managed to make
two combo boxes in a form that get their data from two corresponding fields
on my table. I figured out how to make them do the "SELECT DISTINCT" thing
so that they don't show repeating values when you click on the pull down tab.
I've tried "disecting" different example of forms that do what I want mine
to do, but I don't know enough about access to be able to create mine from
scratch based on someone else's form. I think I might need simple, step by
step instructions.

Thanks so much in advance