WHERE help

[JohnLute]

Hi, group! I'm trying to populate combobox [cbSelectReport] with another
combobox [cbProfileID].

Here is the [cbProfileID] Row Source:
SELECT tblProfiles.txtProfileID, tblProfiles.Type
FROM tblProfiles
ORDER BY tblProfiles.txtProfileID;

[cbProfileID] also has this After Update Event:
Me.cbSelectReport.Requery

Here is the [cbSelectReport] SQL:
SELECT tblReportNames.ReportName, tblReportNames.ReportToOpen
FROM tblReportNames
WHERE (((tblReportNames.Type)=[Forms]![frmQueriesReports].[cbProfileID]));

What can I add to the WHERE clause so that [cbSelectReport] requeries
according to [cbProfileID].[Type]?

THANKS!!!

 

[Jeanette Cunningham]

John,
the row source query for the second combo needs to be based on a query using
both tblProfiles and tblReportNames.
The matching field for tblReportNames.Type must be in the row source of
cbProfileID, in the bound column.

Assuming both combos are on the same form, the where clause in the sql for
cboSelectReport can be simplified to:
WHERE tblReportNames.Type = " & me.cbProfileID & ""

Jeanette Cunningham

 

[Dale Fye]

John,

The combo box has a "Column" property that lets you point to a particular
column (zero based) in the combo boxes selected record. Assuming that this
is in a saved query, try:

SELECT tblReportNames.ReportName, tblReportNames.ReportToOpen
FROM tblReportNames
WHERE tblReportNames.Type=[Forms]![frmQueriesReports].[cbProfileID].Column(1)

If you are building this on the fly and just storing it in the RowSource of
the control, then, as Jeanette mentions, you should be able to shorten it to:

SELECT tblReportNames.ReportName, tblReportNames.ReportToOpen
FROM tblReportNames
WHERE tblReportNames.Type=me.cbProfileID.Column(1)


HTH
Dale

 

[JohnLute]

Thanks, Jeanette. I'm not sure I follow.

the row source query for the second combo needs to be based on a query using
both tblProfiles and tblReportNames.

No problem. I now have this:
SELECT tblReportNames.ReportName, tblReportNames.Description,
tblReportNames.ReportToOpen, tblReportNames.Type, tblProfiles.txtProfileID
FROM tblProfiles INNER JOIN tblReportNames ON tblProfiles.Type =
tblReportNames.Type;

Bound column is (3) [ReportToOpen] as this contains the names of the reports
to be opened upon selection and triggering with Print and Preview buttons.

The matching field for tblReportNames.Type must be in the row source of
cbProfileID, in the bound column.

This is where I get lost. Here's cbProfileID's row source again:
SELECT tblProfiles.txtProfileID, tblProfiles.Type
FROM tblProfiles
ORDER BY tblProfiles.txtProfileID;

The bound column is (1) [txtProfileID] and I can't use [Type] as the bound
column as you've suggested - or at least that's what I THINK you're
suggesting.

Maybe I've completely misunderstood...?

Assuming both combos are on the same form, the where clause in the sql for
cboSelectReport can be simplified to:
WHERE tblReportNames.Type = " & me.cbProfileID & ""

They are both on the same form.

Jeanette Cunningham

Hi, group! I'm trying to populate combobox [cbSelectReport] with another
combobox [cbProfileID].

Here is the [cbProfileID] Row Source:
SELECT tblProfiles.txtProfileID, tblProfiles.Type
FROM tblProfiles
ORDER BY tblProfiles.txtProfileID;

[cbProfileID] also has this After Update Event:
Me.cbSelectReport.Requery

Here is the [cbSelectReport] SQL:
SELECT tblReportNames.ReportName, tblReportNames.ReportToOpen
FROM tblReportNames
WHERE (((tblReportNames.Type)=[Forms]![frmQueriesReports].[cbProfileID]));

What can I add to the WHERE clause so that [cbSelectReport] requeries
according to [cbProfileID].[Type]?

THANKS!!!

 

[Jeanette Cunningham]

John,
Here is a simplified example:
2 tables tblPeople and tblOrders
The field that is in common between the 2 tables is PersonID
The 1st combo has the field PersonID in its 1st column
The 2nd combo uses the value for PersonID that user selects in the 1st combo
to get only the orders containing that particular PersonID

cboA has 2 columns
row source = "SELECT tblPeople.PersonID, tblPeople.LastName FROM tblPeople
cbo has 1st column hidden, it only shows a list of last names

cboB has 2 columns
row source = "SELECT tblOrders.OrderID, tblOrders.OrderItem FROM tblPeople
INNER JOIN tblOrders ON tblPeople.PersonID = tblOrders.PeopleID WHERE
tblOrdersPeopleID = " & Me.cboA & ""

Jeanette Cunningham

Thanks, Jeanette. I'm not sure I follow.

the row source query for the second combo needs to be based on a query
using
both tblProfiles and tblReportNames.

No problem. I now have this:
SELECT tblReportNames.ReportName, tblReportNames.Description,
tblReportNames.ReportToOpen, tblReportNames.Type, tblProfiles.txtProfileID
FROM tblProfiles INNER JOIN tblReportNames ON tblProfiles.Type =
tblReportNames.Type;

Bound column is (3) [ReportToOpen] as this contains the names of the
reports
to be opened upon selection and triggering with Print and Preview buttons.

The matching field for tblReportNames.Type must be in the row source of
cbProfileID, in the bound column.

This is where I get lost. Here's cbProfileID's row source again:
SELECT tblProfiles.txtProfileID, tblProfiles.Type
FROM tblProfiles
ORDER BY tblProfiles.txtProfileID;

The bound column is (1) [txtProfileID] and I can't use [Type] as the bound
column as you've suggested - or at least that's what I THINK you're
suggesting.

Maybe I've completely misunderstood...?

Assuming both combos are on the same form, the where clause in the sql
for
cboSelectReport can be simplified to:
WHERE tblReportNames.Type = " & me.cbProfileID & ""

They are both on the same form.

Jeanette Cunningham

Hi, group! I'm trying to populate combobox [cbSelectReport] with
another
combobox [cbProfileID].

Here is the [cbProfileID] Row Source:
SELECT tblProfiles.txtProfileID, tblProfiles.Type
FROM tblProfiles
ORDER BY tblProfiles.txtProfileID;

[cbProfileID] also has this After Update Event:
Me.cbSelectReport.Requery

Here is the [cbSelectReport] SQL:
SELECT tblReportNames.ReportName, tblReportNames.ReportToOpen
FROM tblReportNames
WHERE
(((tblReportNames.Type)=[Forms]![frmQueriesReports].[cbProfileID]));

What can I add to the WHERE clause so that [cbSelectReport] requeries
according to [cbProfileID].[Type]?

THANKS!!!

 

[jlute]

John,
Here is a simplified example:

Thanks, Jeanette! That helped clarify things. I actually used another
approach as an error message kept occurring whenever I tried to add a
column to the criteria per Dale's suggestion. I simply placed the
[Type] field on the form as a disabled control and now requery off
that.

Thanks again!!!