Filtering a subform using many combo boxes

  • Thread starter Thread starter Wendymel
  • Start date Start date
W

Wendymel

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:

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

On Change Event Procedure:

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


Now for the subform. It has a record source query named qryProjects. It is
a continuous form.

It has column headers in the form header section that are designed to sort
ascending and

descending on click or double click. The records are displayed in the
subforms detail section.

So continuing with the project manager example, there is a column for that
information. The

designated field has the following properties:

Name = ProjectManager
Control Source = ProjManagerName (this is from the main table tblProjectData)

Now for the subform record source query. Again, the query name is
qryProjects. It is designed

to pull all the information needed to populate the subform and it does that
successfully. This

is the query:

SELECT tblProjectData.ProjectUNID, tblProjectData.PROJDescription,
tblProjectData.PROJBudget,

tblProjectData.CurrentProjAuth, tblProjectData.ProjectNumber,
tblProjectNumber.ProjectNumber,

tblProjectData.PROJBudgetYear, tblBudgetYear.BudgetYear,
tblProjectData.PROJLocation,

tblLocation.Location, tblProjectData.ProjectStatus,
tblProjStatus.ProjectStatusName,

tblProjectData.PROJFundingType, tblFundingType.FundingSourceType,

tblProjectData.PROJAssignedPriority,
tblAssignedPriority.AssignedPriorityDetail,

tblProjectData.ProjectManager, tblProjManager.ProjManagerName
FROM tblFundingType RIGHT JOIN (tblBudgetYear RIGHT JOIN (tblProjectNumber
RIGHT JOIN

(tblAssignedPriority RIGHT JOIN (tblProjStatus RIGHT JOIN (tblProjManager
RIGHT JOIN

(tblLocation RIGHT JOIN tblProjectData ON tblLocation.Location =
tblProjectData.PROJLocation)

ON tblProjManager.ProjManagerName = tblProjectData.ProjectManager) ON

tblProjStatus.ProjectStatusName = tblProjectData.ProjectStatus) ON

tblAssignedPriority.AssignedPriority = tblProjectData.PROJAssignedPriority)
ON

tblProjectNumber.ProjectNumber = tblProjectData.ProjectNumber) ON
tblBudgetYear.BudgetYear =

tblProjectData.PROJBudgetYear) ON tblFundingType.FundingSourceType =

tblProjectData.PROJFundingType
WHERE (((tblProjectData.PROJDescription) Like "*" & [Forms]![frmProjectList]!

[DescriptionSearch] & "*") AND ((tblProjectNumber.ProjectNumber) Like "*" &
[Forms]!

[frmProjectList]![cboProjectNumberChoice] & "*") AND
((tblBudgetYear.BudgetYear) Like "*" &

[Forms]![frmProjectList]![cboBudgetYearChoice] & "*") AND
((tblLocation.Location) Like "*" &

[Forms]![frmProjectList]![cboLocationChoice] & "*") AND
((tblProjStatus.ProjectStatusName) Like

"*" & [Forms]![frmProjectList]![cboStatusChoice] & "*") AND
((tblFundingType.FundingSourceType)

Like "*" & [Forms]![frmProjectList]![cboFundingChoice] & "*") AND

((tblAssignedPriority.AssignedPriorityDetail) Like "*" &
[Forms]![frmProjectList]!

[cboAssignedPriorityChoice] & "*") AND ((tblProjManager.ProjManagerName)
Like "*" & [Forms]!

[frmProjectList]![cboProjectManagerChoice] & "*")) OR
((([Forms]![frmProjectList]!

[DescriptionSearch]) Is Null) AND
(([Forms]![frmProjectList]![cboProjectNumberChoice]) Is Null)

AND (([Forms]![frmProjectList]![cboBudgetYearChoice]) Is Null) AND
(([Forms]![frmProjectList]!

[cboLocationChoice]) Is Null) AND
(([Forms]![frmProjectList]![cboStatusChoice]) Is Null) AND

(([Forms]![frmProjectList]![cboFundingChoice]) Is Null) AND
(([Forms]![frmProjectList]!

[cboAssignedPriorityChoice]) Is Null) AND
(([Forms]![frmProjectList]![cboProjectManagerChoice])

Is Null))
ORDER BY tblProjectData.ProjectNumber;


Now for my problem, the filters do not work properly. If a user selects a
value from any of

the combo boxes individually, they should see the list in the subform reduce
to just the

records containing the selected value. They should be able to further
filter their selection

by using any combination of combo boxes they wish. I have made this work
for 3 other databases

I have designed using all the same logic in the above query and all of the
same properties in

the tables, combo boxes etc. It just wont work for this db.

I have not been able to find a common denominator. The only thing unique to
this db from all

the others is that I was asked to design it so that the users could enter
data in a record and

leave some of the fields blank, then potentially go back later and fill in
the fields with data

once it is known to them. Thus, no validation rules are set, required is
set to NO and allow

zero length is set to yes in all the fields in all the tables. In the data
entry form, the

user enters data from combo boxes and tabs to the next field and does the
same.


Back to the frmProjectList and the project manager example; If a user
selects a project

managers name from the drop down list of the combo box on the form, they
should see the list in

the subform reduce from 300 plus records, down to the number of records that
contain the name

of the selected project manager in the project managers field. This happens,
but it always

shows the incorrect amount of records. For example, if the user selects
R.Johnson from the

combo box, they should see all 13 of the records containing his name in the
project managers

field. Instead, it only filters to 5. I can not find a common denominator
in the missing

records. Some of them are incomplete records, some of them are complete.
They all have

different values in each of the other fields so I am completely stumped.

Some of the things I have looked at:

Possible blank vs null values in fields
When I queried for nulls in the main table, it showed the blank fields so I
am assuming

all the blank fields are null, not ''.

I have torn the query apart and rebuilt it MANY TIMES.....no luck.

I have removed all but 2 of the combo boxes and rewrote the query to
accomodate this, no luck.

I have tried some visual basic to make this work and it was a disaster, I am
not good at visual

basic.

I even tried to rebuild the database from scratch...no luck.


Bottom line is....I need help. I have no idea what is wrong and have
exhausted my resources

and knowledge. Please let me know if I can add any information to this
rather exhaustive

explaination.

Thank you so much in advance!!!!

Wendy
 
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 said:
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:

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

On Change Event Procedure:

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


Now for the subform. It has a record source query named qryProjects. It is
a continuous form.

It has column headers in the form header section that are designed to sort
ascending and

descending on click or double click. The records are displayed in the
subforms detail section.

So continuing with the project manager example, there is a column for that
information. The

designated field has the following properties:

Name = ProjectManager
Control Source = ProjManagerName (this is from the main table tblProjectData)

Now for the subform record source query. Again, the query name is
qryProjects. It is designed

to pull all the information needed to populate the subform and it does that
successfully. This

is the query:

SELECT tblProjectData.ProjectUNID, tblProjectData.PROJDescription,
tblProjectData.PROJBudget,

tblProjectData.CurrentProjAuth, tblProjectData.ProjectNumber,
tblProjectNumber.ProjectNumber,

tblProjectData.PROJBudgetYear, tblBudgetYear.BudgetYear,
tblProjectData.PROJLocation,

tblLocation.Location, tblProjectData.ProjectStatus,
tblProjStatus.ProjectStatusName,

tblProjectData.PROJFundingType, tblFundingType.FundingSourceType,

tblProjectData.PROJAssignedPriority,
tblAssignedPriority.AssignedPriorityDetail,

tblProjectData.ProjectManager, tblProjManager.ProjManagerName
FROM tblFundingType RIGHT JOIN (tblBudgetYear RIGHT JOIN (tblProjectNumber
RIGHT JOIN

(tblAssignedPriority RIGHT JOIN (tblProjStatus RIGHT JOIN (tblProjManager
RIGHT JOIN

(tblLocation RIGHT JOIN tblProjectData ON tblLocation.Location =
tblProjectData.PROJLocation)

ON tblProjManager.ProjManagerName = tblProjectData.ProjectManager) ON

tblProjStatus.ProjectStatusName = tblProjectData.ProjectStatus) ON

tblAssignedPriority.AssignedPriority = tblProjectData.PROJAssignedPriority)
ON

tblProjectNumber.ProjectNumber = tblProjectData.ProjectNumber) ON
tblBudgetYear.BudgetYear =

tblProjectData.PROJBudgetYear) ON tblFundingType.FundingSourceType =

tblProjectData.PROJFundingType
WHERE (((tblProjectData.PROJDescription) Like "*" & [Forms]![frmProjectList]!

[DescriptionSearch] & "*") AND ((tblProjectNumber.ProjectNumber) Like "*" &
[Forms]!

[frmProjectList]![cboProjectNumberChoice] & "*") AND
((tblBudgetYear.BudgetYear) Like "*" &

[Forms]![frmProjectList]![cboBudgetYearChoice] & "*") AND
((tblLocation.Location) Like "*" &

[Forms]![frmProjectList]![cboLocationChoice] & "*") AND
((tblProjStatus.ProjectStatusName) Like

"*" & [Forms]![frmProjectList]![cboStatusChoice] & "*") AND
((tblFundingType.FundingSourceType)

Like "*" & [Forms]![frmProjectList]![cboFundingChoice] & "*") AND

((tblAssignedPriority.AssignedPriorityDetail) Like "*" &
[Forms]![frmProjectList]!

[cboAssignedPriorityChoice] & "*") AND ((tblProjManager.ProjManagerName)
Like "*" & [Forms]!

[frmProjectList]![cboProjectManagerChoice] & "*")) OR
((([Forms]![frmProjectList]!

[DescriptionSearch]) Is Null) AND
(([Forms]![frmProjectList]![cboProjectNumberChoice]) Is Null)

AND (([Forms]![frmProjectList]![cboBudgetYearChoice]) Is Null) AND
(([Forms]![frmProjectList]!

[cboLocationChoice]) Is Null) AND
(([Forms]![frmProjectList]![cboStatusChoice]) Is Null) AND

(([Forms]![frmProjectList]![cboFundingChoice]) Is Null) AND
(([Forms]![frmProjectList]!

[cboAssignedPriorityChoice]) Is Null) AND
(([Forms]![frmProjectList]![cboProjectManagerChoice])

Is Null))
ORDER BY tblProjectData.ProjectNumber;


Now for my problem, the filters do not work properly. If a user selects a
value from any of

the combo boxes individually, they should see the list in the subform reduce
to just the

records containing the selected value. They should be able to further
filter their selection

by using any combination of combo boxes they wish. I have made this work
for 3 other databases

I have designed using all the same logic in the above query and all of the
same properties in

the tables, combo boxes etc. It just wont work for this db.

I have not been able to find a common denominator. The only thing unique to
this db from all

the others is that I was asked to design it so that the users could enter
data in a record and

leave some of the fields blank, then potentially go back later and fill in
the fields with data

once it is known to them. Thus, no validation rules are set, required is
set to NO and allow

zero length is set to yes in all the fields in all the tables. In the data
entry form, the

user enters data from combo boxes and tabs to the next field and does the
same.


Back to the frmProjectList and the project manager example; If a user
selects a project

managers name from the drop down list of the combo box on the form, they
should see the list in

the subform reduce from 300 plus records, down to the number of records that
contain the name

of the selected project manager in the project managers field. This happens,
but it always

shows the incorrect amount of records. For example, if the user selects
R.Johnson from the

combo box, they should see all 13 of the records containing his name in the
project managers

field. Instead, it only filters to 5. I can not find a common denominator
in the missing

records. Some of them are incomplete records, some of them are complete.
They all have

different values in each of the other fields so I am completely stumped.

Some of the things I have looked at:

Possible blank vs null values in fields
When I queried for nulls in the main table, it showed the blank fields so I
am assuming

all the blank fields are null, not ''.

I have torn the query apart and rebuilt it MANY TIMES.....no luck.

I have removed all but 2 of the combo boxes and rewrote the query to
accomodate this, no luck.

I have tried some visual basic to make this work and it was a disaster, I am
not good at visual

basic.

I even tried to rebuild the database from scratch...no luck.


Bottom line is....I need help. I have no idea what is wrong and have
exhausted my resources

and knowledge. Please let me know if I can add any information to this
rather exhaustive

explaination.

Thank you so much in advance!!!!

Wendy
Click to expand...
 
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 said:
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 said:
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:

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

On Change Event Procedure:

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


Now for the subform. It has a record source query named qryProjects. It is
a continuous form.

It has column headers in the form header section that are designed to sort
ascending and

descending on click or double click. The records are displayed in the
subforms detail section.

So continuing with the project manager example, there is a column for that
information. The

designated field has the following properties:

Name = ProjectManager
Control Source = ProjManagerName (this is from the main table tblProjectData)

Now for the subform record source query. Again, the query name is
qryProjects. It is designed

to pull all the information needed to populate the subform and it does that
successfully. This

is the query:

SELECT tblProjectData.ProjectUNID, tblProjectData.PROJDescription,
tblProjectData.PROJBudget,

tblProjectData.CurrentProjAuth, tblProjectData.ProjectNumber,
tblProjectNumber.ProjectNumber,

tblProjectData.PROJBudgetYear, tblBudgetYear.BudgetYear,
tblProjectData.PROJLocation,

tblLocation.Location, tblProjectData.ProjectStatus,
tblProjStatus.ProjectStatusName,

tblProjectData.PROJFundingType, tblFundingType.FundingSourceType,

tblProjectData.PROJAssignedPriority,
tblAssignedPriority.AssignedPriorityDetail,

tblProjectData.ProjectManager, tblProjManager.ProjManagerName
FROM tblFundingType RIGHT JOIN (tblBudgetYear RIGHT JOIN (tblProjectNumber
RIGHT JOIN

(tblAssignedPriority RIGHT JOIN (tblProjStatus RIGHT JOIN (tblProjManager
RIGHT JOIN

(tblLocation RIGHT JOIN tblProjectData ON tblLocation.Location =
tblProjectData.PROJLocation)

ON tblProjManager.ProjManagerName = tblProjectData.ProjectManager) ON

tblProjStatus.ProjectStatusName = tblProjectData.ProjectStatus) ON

tblAssignedPriority.AssignedPriority = tblProjectData.PROJAssignedPriority)
ON

tblProjectNumber.ProjectNumber = tblProjectData.ProjectNumber) ON
tblBudgetYear.BudgetYear =

tblProjectData.PROJBudgetYear) ON tblFundingType.FundingSourceType =

tblProjectData.PROJFundingType
WHERE (((tblProjectData.PROJDescription) Like "*" & [Forms]![frmProjectList]!

[DescriptionSearch] & "*") AND ((tblProjectNumber.ProjectNumber) Like "*" &
[Forms]!

[frmProjectList]![cboProjectNumberChoice] & "*") AND
((tblBudgetYear.BudgetYear) Like "*" &

[Forms]![frmProjectList]![cboBudgetYearChoice] & "*") AND
((tblLocation.Location) Like "*" &

[Forms]![frmProjectList]![cboLocationChoice] & "*") AND
((tblProjStatus.ProjectStatusName) Like

"*" & [Forms]![frmProjectList]![cboStatusChoice] & "*") AND
((tblFundingType.FundingSourceType)

Like "*" & [Forms]![frmProjectList]![cboFundingChoice] & "*") AND

((tblAssignedPriority.AssignedPriorityDetail) Like "*" &
[Forms]![frmProjectList]!

[cboAssignedPriorityChoice] & "*") AND ((tblProjManager.ProjManagerName)
Like "*" & [Forms]!

[frmProjectList]![cboProjectManagerChoice] & "*")) OR
((([Forms]![frmProjectList]!

[DescriptionSearch]) Is Null) AND
(([Forms]![frmProjectList]![cboProjectNumberChoice]) Is Null)

AND (([Forms]![frmProjectList]![cboBudgetYearChoice]) Is Null) AND
(([Forms]![frmProjectList]!

[cboLocationChoice]) Is Null) AND
(([Forms]![frmProjectList]![cboStatusChoice]) Is Null) AND

(([Forms]![frmProjectList]![cboFundingChoice]) Is Null) AND
(([Forms]![frmProjectList]!

[cboAssignedPriorityChoice]) Is Null) AND
(([Forms]![frmProjectList]![cboProjectManagerChoice])

Is Null))
ORDER BY tblProjectData.ProjectNumber;


Now for my problem, the filters do not work properly. If a user selects a
value from any of

the combo boxes individually, they should see the list in the subform reduce
to just the

records containing the selected value. They should be able to further
filter their selection

by using any combination of combo boxes they wish. I have made this work
for 3 other databases

I have designed using all the same logic in the above query and all of the
same properties in

the tables, combo boxes etc. It just wont work for this db.

I have not been able to find a common denominator. The only thing unique to
this db from all

the others is that I was asked to design it so that the users could enter
data in a record and

leave some of the fields blank, then potentially go back later and fill in
the fields with data

once it is known to them. Thus, no validation rules are set, required is
set to NO and allow

zero length is set to yes in all the fields in all the tables. In the data
entry form, the

user enters data from combo boxes and tabs to the next field and does the
same.
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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:

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

On Change Event Procedure:

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


Now for the subform. It has a record source query named qryProjects. It is
a continuous form.

It has column headers in the form header section that are designed to sort
ascending and

descending on click or double click. The records are displayed in the
subforms detail section.

So continuing with the project manager example, there is a column for that
information. The

designated field has the following properties:

Name = ProjectManager
Control Source = ProjManagerName (this is from the main table tblProjectData)

Now for the subform record source query. Again, the query name is
qryProjects. It is designed

to pull all the information needed to populate the subform and it does that
successfully. This

is the query:

SELECT tblProjectData.ProjectUNID, tblProjectData.PROJDescription,
tblProjectData.PROJBudget,

tblProjectData.CurrentProjAuth, tblProjectData.ProjectNumber,
tblProjectNumber.ProjectNumber,

tblProjectData.PROJBudgetYear, tblBudgetYear.BudgetYear,
tblProjectData.PROJLocation,

tblLocation.Location, tblProjectData.ProjectStatus,
tblProjStatus.ProjectStatusName,

tblProjectData.PROJFundingType, tblFundingType.FundingSourceType,

tblProjectData.PROJAssignedPriority,
tblAssignedPriority.AssignedPriorityDetail,

tblProjectData.ProjectManager, tblProjManager.ProjManagerName
FROM tblFundingType RIGHT JOIN (tblBudgetYear RIGHT JOIN (tblProjectNumber
RIGHT JOIN

(tblAssignedPriority RIGHT JOIN (tblProjStatus RIGHT JOIN (tblProjManager
RIGHT JOIN

(tblLocation RIGHT JOIN tblProjectData ON tblLocation.Location =
tblProjectData.PROJLocation)

ON tblProjManager.ProjManagerName = tblProjectData.ProjectManager) ON

tblProjStatus.ProjectStatusName = tblProjectData.ProjectStatus) ON

tblAssignedPriority.AssignedPriority = tblProjectData.PROJAssignedPriority)
ON

tblProjectNumber.ProjectNumber = tblProjectData.ProjectNumber) ON
tblBudgetYear.BudgetYear =

tblProjectData.PROJBudgetYear) ON tblFundingType.FundingSourceType =

tblProjectData.PROJFundingType
WHERE (((tblProjectData.PROJDescription) Like "*" & [Forms]![frmProjectList]!

[DescriptionSearch] & "*") AND ((tblProjectNumber.ProjectNumber) Like "*" &
[Forms]!

[frmProjectList]![cboProjectNumberChoice] & "*") AND
((tblBudgetYear.BudgetYear) Like "*" &

[Forms]![frmProjectList]![cboBudgetYearChoice] & "*") AND
((tblLocation.Location) Like "*" &

[Forms]![frmProjectList]![cboLocationChoice] & "*") AND
((tblProjStatus.ProjectStatusName) Like

"*" & [Forms]![frmProjectList]![cboStatusChoice] & "*") AND
((tblFundingType.FundingSourceType)

Like "*" & [Forms]![frmProjectList]![cboFundingChoice] & "*") AND

((tblAssignedPriority.AssignedPriorityDetail) Like "*" &
[Forms]![frmProjectList]!

[cboAssignedPriorityChoice] & "*") AND ((tblProjManager.ProjManagerName)
Like "*" & [Forms]!

[frmProjectList]![cboProjectManagerChoice] & "*")) OR
((([Forms]![frmProjectList]!

[DescriptionSearch]) Is Null) AND
(([Forms]![frmProjectList]![cboProjectNumberChoice]) Is Null)

AND (([Forms]![frmProjectList]![cboBudgetYearChoice]) Is Null) AND
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.)


:

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:

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

On Change Event Procedure:

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


Now for the subform. It has a record source query named qryProjects. It is
a continuous form.

It has column headers in the form header section that are designed to sort
ascending and

descending on click or double click. The records are displayed in the
subforms detail section.

So continuing with the project manager example, there is a column for that
information. The

designated field has the following properties:

Name = ProjectManager
Control Source = ProjManagerName (this is from the main table tblProjectData)

Now for the subform record source query. Again, the query name is
qryProjects. It is designed

to pull all the information needed to populate the subform and it does that
successfully. This

is the query:

SELECT tblProjectData.ProjectUNID, tblProjectData.PROJDescription,
tblProjectData.PROJBudget,

tblProjectData.CurrentProjAuth, tblProjectData.ProjectNumber,
tblProjectNumber.ProjectNumber,

tblProjectData.PROJBudgetYear, tblBudgetYear.BudgetYear,
tblProjectData.PROJLocation,

tblLocation.Location, tblProjectData.ProjectStatus,
tblProjStatus.ProjectStatusName,

tblProjectData.PROJFundingType, tblFundingType.FundingSourceType,

tblProjectData.PROJAssignedPriority,
tblAssignedPriority.AssignedPriorityDetail,

tblProjectData.ProjectManager, tblProjManager.ProjManagerName
FROM tblFundingType RIGHT JOIN (tblBudgetYear RIGHT JOIN (tblProjectNumber
RIGHT JOIN

(tblAssignedPriority RIGHT JOIN (tblProjStatus RIGHT JOIN (tblProjManager
RIGHT JOIN

(tblLocation RIGHT JOIN tblProjectData ON tblLocation.Location =
tblProjectData.PROJLocation)

ON tblProjManager.ProjManagerName = tblProjectData.ProjectManager) ON

tblProjStatus.ProjectStatusName = tblProjectData.ProjectStatus) ON
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

:

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.)


:

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:

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

On Change Event Procedure:

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


Now for the subform. It has a record source query named qryProjects. It is
a continuous form.

It has column headers in the form header section that are designed to sort
ascending and

descending on click or double click. The records are displayed in the
subforms detail section.

So continuing with the project manager example, there is a column for that
information. The

designated field has the following properties:

Name = ProjectManager
Control Source = ProjManagerName (this is from the main table tblProjectData)

Now for the subform record source query. Again, the query name is
qryProjects. It is designed

to pull all the information needed to populate the subform and it does that
successfully. This

is the query:

SELECT tblProjectData.ProjectUNID, tblProjectData.PROJDescription,
tblProjectData.PROJBudget,

tblProjectData.CurrentProjAuth, tblProjectData.ProjectNumber,
tblProjectNumber.ProjectNumber,
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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.)


:

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

:

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.)


:

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:
Click to expand...
Click to expand...
Click to expand...
 
Glad to be of help.

Those pesky users *always* find ways to break the app... :D
Or they want more (and more and...) :O

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


Wendymel said:
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 said:
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 said:
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

:

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.)


:

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

:

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.)


:

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top