RE: Filtering a subform using many combo boxes

Thank you so much Steve!

Everything works as it should now.

I will let you know if I have any additional issues as a result of this fix.
I am releasing this DB to the users now and they may find something that I
missed in testing.

Thanks,

Wendy

"Steve Sanford" wrote:

For the combo box "cboAssignedPriorityChoice", change the Row Source to:

SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority
ORDER BY AssignedPriorityDetail;

In the properties dialog for the combo box, set these properties:

On the FORMAT Tab:
--------------------------
Column Count .......... 2
Column Width........... 0"; 0.5"
Width....................... 0.75" (at least)


On the DATA Tab:
--------------------------
Bound Column......... 1
Limit To List............YES


This will give you a combo box with a two field row source, sorted by the
second field, that searches using the first field ("2"), but,in the combo
box, displays the second field ("Priority 2")

Note that if you have a "Priority 10", it will sort between "Priority 1" and
"Priority 2", *not* after "Priority 9".


(BTW, you should (I would) probably have the "Limit To List" property set to
YES for all of the combo boxes.)

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Wendymel" wrote:

To answer your questions:

1 - The field that stores "Priority Choice" in the tblProjectData table is
called PROJAssignedPriority. It is a text type field. I am storing the
values of the priorities as a 1 or 2 or 3 etc.

2 - In the table "tblAssignedPriority" the name of the second field is
"AssignedPriorityDetail"

3 - You have the structure of the table correct except the AssignedPriority
field is a text field. You also have the look of the data correct.

4 - I would like the combo box to display the priority as "Priority 1".

The only reason that I have the two different references to the priority is
because it is required for some of the reporting formats. These reports have
been recreated to duplicate exactly some reports that were written from
another, older database (Filemaker Pro).

Thanks Steve,

Wendy

"Steve Sanford" wrote:

Wendy,

Q - In table "tblProjectData", what is the name of the field that stores the
"Priority Choice"? And what is its data type - Text or Number(Integer or
Long) ?
(Are you storing "Priority 1" or just a 1?)


In the table "tblAssignedPriority", what is the name of the second field?

Does the structure of table "tblAssignedPriority" look like:

--- Name ----- ---Data Type----
AssignedPriority Long (PK)
AssignedPriorityDetail Text


and the data looks like:

AssignedPriority AssignedPriorityDetail
------------------- -------------------------
1 "Priority 1"
2 "Priority 2"
3 "Priority 3"
etc.

??


Do you want the combo box "cboAssignedPriorityChoice" to display the string
"Priority 1" or just 1?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Wendymel" wrote:

Steve,

Thank you so much for your help. Here is what I have now.

I followed you instructions to the letter and every one of the criteria
worked with the exception of this one.

Like "*" & [Forms]![frmProjectList]![cboAssignedPriorityChoice] & "*" Or
[Forms]![frmProjectList]![cboAssignedPriorityChoice] Is Null

For some reason I can select a value from any combo box individually, or a
combination of values from several combo boxes and the filters work
beautifully (Again...MANY THANKS!). However, when I use the Assigned
Priority combo, it returns no lines whatsoever.

I described the typical properties of all my combo boxes in my original
request. This is the case for all combo boxes. The tables that contain the
source data for each combo box are all simple one column tables, with the
exception of the Assigned Priority table (tblAssignedPriority). The key field
contains a numeric value for the priority (1,2,3,4, etc) and is named
AssignedPriority. The second field contains the text "title", if you will,
of the priority (Priority 1, Priority 2 etc). I had to do this because some
of the reports required it.

The properties for this combo box are as follows:

Row Source Type = table/query
Row Source = SELECT AssignedPriorityDetail FROM tblAssignedPriority
ORDER BY AssignedPriorityDetail;

After Update Event Procedure:

Private Sub cboAssignedPriorityChoice_AfterUpdate()
Me.frmSubProjectList.Requery
End Sub

On Change Event Procedure:

Private Sub cboAssignedPriorityChoice_Change()
DoCmd.Requery "frmSubProjectList"
End Sub

I am not sure if this information will give you something to work with.
Please let me know if I can supply any other details.

Thanks,

Wendy

"Steve Sanford" wrote:

Hi Wendy,

Since I don't have a copy of your database and I can't see it, I'll try to
give you a few things to try to troubleshoot the problem.

OK, start by making a copy of your database. I say again, make a *COPY* of
your database. We'll use the copy to do the troubleshooting.

First, open the query "qryProjects" in design view. Delete all of the
criteria rows. In SQL view, there should only be the SELECT clause, the FROM
clause and the ORDER BY clause.

Run the query. You should have the total number of rows (records).
Now open the table "TblProjManager" and copy the manager's name , ie
"R.Johnson". We copy from the table to get the exact text that you would get
from selecting his name from the combo box. Paste the manages's name in the
criteria row for the field "ProjectManager". It should look like "R.Johnson"
(with quotes because it is a string).

Run the query. Did it return the expected number of records (13)?

If so, this is good. If not, now you have to determine why the correct
number of records was not returned.

Next, open the query in design view and delete the name in the criteria row.
Copy the following and paste it in the criteria for the field
"ProjectManager":


Like "*" & [Forms]![frmProjectList]![cboProjectManagerChoice] & "*" Or
[Forms]![frmProjectList]![cboProjectManagerChoice] Is Null


Save the query. Now open the form "frmProjectList". DO NOT select anything
in the combo boxes. Run the query "qryProjects". All records should be
returned.

Switch to query design view.

On the form "frmProjectList", select the name "R.Johnson" in the combo box
"cboProjectManagerChoice". Go back to the query and run it. Was the same
number of records returned as when you typed (pasted) in the actual name? Or
the expected number of records.



I was wondering. It looks like you are storing the manager's name in the
table "tblProjectData". How do you know which R.Johnson you are looking for -
Robert, Ron, or Roy?? Or what if there are two managers named Robert
Johnson???


Back to the problem.

So we know the proj manager name combo box works. DO NOT select anything in
any of the other combo boxes on the form, the manager's name should be the
only criteria selected!

Now add one more criteria:

Like "*" & [Forms]![frmProjectList]![DescriptionSearch] & "*" or is null


Execute the query. Were the expected number of records returned?

Keep adding criteria one at a time, executing the query each time a criteria
is added until you don't get the correct number of records. Then you have to
find out why that specific criteria limited the records. Continue adding
until all criteria (8) have been added and the correct number of records for
the selected manager is erturned.

Once you find the problem and fix it, you will know how to fix the
production databse.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"Wendymel" wrote:

OK, I have posted on this site many times and all of you have been a huge
help! I am at the

end of my rope with this particular database. I REALLY need some help for
what I am hoping is

the last time. Hopefully, some day I will know enough to "pay it forward".
Please keep in

mind that I am not a seasoned pro at this! Thanks!

Here goes....

I have a database that contains information concerning capital projects.

I have a main table named tblProjectData that contains all the data for each
record. I have

several other tables containing lists of specific data (tblProjManager,
tblLocation,

tblBudgetYear, tblProjStatus etc.). All of the data specific tables are
joined to the main

table using one-to-many relationships.

I have a form in the db named frmProjectList. It contains a sub form named
frmSubProjectList.

The sub form is located in the forms Detail section.

The form has no record source. The form header contains 7 unbound combo
boxes, an unbound text

box, and 6 command buttons. The command buttons are not an issue at all, I
just mentioned them

for informational purposes.

I put the combo boxes in form to allow a user to filter through the records
contained in the

sub form. Each combo box is basically the same so for the sake of
arguement, I will use one

called cboProjectManagerChoice. It is designed to filter through the
project managers names in

the subform and filter out the users choice. Some of the properties of this
combo box are:

Row Source Type = table/query
Row Source = SELECT [ProjManagerName] FROM tblProjManager ORDER BY
[ProjManagerName];
After Update Event Procedure:

.

Loading