multiple tiered cascading field question

[Guest]

Hello, i have a form that pulls current open projects from each selected
users by using these three criterias:
- user name
- fiscal year
- quarter
Each of these are combo boxes and are cascading lists, dependant on the
field before it.

What i want to do is:
Have a 4th field titled "projects" be a list box that pulls all projects
that matches the three criterias selected above. I have some coding for this
already, but keep getting an error that says something along the lines of the
query being too complicated:

SELECT A.ProjectName, A.*
FROM tbl_project AS A
WHERE (((A.OwnerID)=forms!projectStatus!OwnerIDbox)) And
((A.FiscalYearID)=forms!projectStatus!FiscalYearbox) And
((A.QuarterID)=forms!projectStatus!quarterbox);


Can anyone suggest a solution or provide some assistance? THanks very much.

 

[Marshall Barton]

Hello, i have a form that pulls current open projects from each selected
users by using these three criterias:
- user name
- fiscal year
- quarter
Each of these are combo boxes and are cascading lists, dependant on the
field before it.

What i want to do is:
Have a 4th field titled "projects" be a list box that pulls all projects
that matches the three criterias selected above. I have some coding for this
already, but keep getting an error that says something along the lines of the
query being too complicated:

SELECT A.ProjectName, A.*
FROM tbl_project AS A
WHERE (((A.OwnerID)=forms!projectStatus!OwnerIDbox)) And
((A.FiscalYearID)=forms!projectStatus!FiscalYearbox) And
((A.QuarterID)=forms!projectStatus!quarterbox);

The first thing to do is get rid of the ,A.* because you
probably do not want to retrieve all the fields in the table
and you certainly do not want to retrieve two copies of the
ProjectName.

The rest of the query looks ok to me, so post a more precise
copy of any further error messages.

 

[Guest]

Actually, i just tested the query again...with the suggestion you made
below...and there is no error...but there's no results either. Here is the
updated query statement:

SELECT A.ProjectName
FROM tbl_project AS A
WHERE (((A.OwnerID)=forms!projectStatus!OwnerIDbox)) And
((A.FiscalYearID)=forms!projectStatus!FiscalYearbox) And
((A.QuarterID)=forms!projectStatus!quarterbox)
ORDER BY A.ProjectName;


Should i be doing anything special with the "After Update" events for each
of the 3 initial fields to get this list box updated in real-time? THanks
for the response so far!

 

[Marshall Barton]

Yes, the AfterUpdate event of each combo box needs to do two
things. The first is to clear any old values based on the
combo box's old value. The second is to Requery the next
combo box.

user name combo box's AfterUpdate event procedure:
Me.cboFiscalYear = Null
Me.cboQuarter = Null
Me.cboProject = Null
Me.cboFiscalYear.Requery

fiscal year combo box's event:
Me.cboQuarter = Null
Me.cboProject = Null
Me.cboQuarter.Requery

quarter combo box:
Me.cboProject = Null
Me.cboProject.Requery

 

[Guest]

Yup, i have those AfterUpdate's set just like you said below Marshall, i
think it might be something wrong with my query perhaps?

Yes, the AfterUpdate event of each combo box needs to do two
things. The first is to clear any old values based on the
combo box's old value. The second is to Requery the next
combo box.

user name combo box's AfterUpdate event procedure:
Me.cboFiscalYear = Null
Me.cboQuarter = Null
Me.cboProject = Null
Me.cboFiscalYear.Requery

fiscal year combo box's event:
Me.cboQuarter = Null
Me.cboProject = Null
Me.cboQuarter.Requery

quarter combo box:
Me.cboProject = Null
Me.cboProject.Requery
--
Marsh
MVP [MS Access]

Actually, i just tested the query again...with the suggestion you made
below...and there is no error...but there's no results either. Here is the
updated query statement:

SELECT A.ProjectName
FROM tbl_project AS A
WHERE (((A.OwnerID)=forms!projectStatus!OwnerIDbox)) And
((A.FiscalYearID)=forms!projectStatus!FiscalYearbox) And
((A.QuarterID)=forms!projectStatus!quarterbox)
ORDER BY A.ProjectName;

Should i be doing anything special with the "After Update" events for each
of the 3 initial fields to get this list box updated in real-time? THanks
for the response so far!

 

[Marshall Barton]

The query looks ok to me, but double check to make sure that
each combo box's Value really is the value used in the next
combo's criteria.

Make doubly certain that each combo box's BoundColumn is set
to the field that you want the Value to be. Also chack that
the ColumnCounts are the same as the number of fields in the
RowSource.