combobox selection going into a query

[ryan.fitzpatrick3]

I have 3 comboboxes. The first one named cboxClass, which pulls from
tblClass table has a selection of items where when you select the
filtered choices should go into combobox2 named cboxCategories, which
pulls from tblCategories table. Then it does the same from cbox2 to
cbox3. Cbox3 is named cboxGroup because it pulls from tblGroup table.
Now I have this SQL code. I go into record source of cbox2 and link it
to cboxclass. The form name is frmsystemsreport.

SELECT tblCategories.CategoryID, tblCategories.CategoryName,
tblCategories.ClassID
FROM tblCategories
WHERE (((tblCategories.ClassID)=[Forms]![frmSystemsReport]!
[cboxClass]));

How do I get the filtered selection from cboxclass into
cboxcategories?

Also after this works, I have a query that runs a report. I put the
fields Class, categorie and group into the query and in the criteria I
link it to the combobox, so in the class field I link it to the
cboxclass and so on for the other two fields categorie and group. My
problem is that when I run the report it comes up with error#'s. How
do I fix. Here is the SQL

SELECT QrySSIMSAllitemswithAllVendors.CORP,
QrySSIMSAllitemswithAllVendors.DIVISION,
QrySSIMSAllitemswithAllVendors.FACILITY,
QrySSIMSAllitemswithAllVendors.DST_CNTR,
tblSSIMSItemNumbers.ItemNumber,
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD,
tblSSIMSItemNumbers.Class, tblSSIMSItemNumbers.Categorie,
tblSSIMSItemNumbers.Group, QrySSIMSAllitemswithAllVendors.DESC_ITEM,
QrySSIMSAllitemswithAllVendors.VEND_NUM,
QrySSIMSAllitemswithAllVendors.VEND_SUB_ACNT,
QrySSIMSAllitemswithAllVendors.NAME, [Qry(1c)POReceivings].AMT_RECV,
QrySSIMSAllitemswithAllVendors.SIZE_NUM,
QrySSIMSAllitemswithAllVendors.SIZE_UOM, IIf([SIZE_UOM]="OZ",
[SIZE_NUM]/16,[SIZE_NUM]) AS OZTOLBCONV,
QrySSIMSAllitemswithAllVendors.PACK_WHSE,
[AMT_RECV]*[PACK_WHSE]*[OZTOLBCONV] AS TOTALVOL,
QrySSIMSAllitemswithAllVendors.UNITCOST,
QrySSIMSAllitemswithAllVendors.PERUNITCOST, [PERUNITCOST]/[OZTOLBCONV]
AS LBPRICE, QrySSIMSAllitemswithAllVendors.COST_IB,
[LBPRICE]*[TOTALVOL] AS SPEND
FROM (tblSSIMSItemNumbers INNER JOIN QrySSIMSAllitemswithAllVendors ON
tblSSIMSItemNumbers.ItemNumber =
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD) INNER JOIN
[Qry(1c)POReceivings] ON (QrySSIMSAllitemswithAllVendors.CORP =
[Qry(1c)POReceivings].CORP) AND
(QrySSIMSAllitemswithAllVendors.DIVISION =
[Qry(1c)POReceivings].DIVISION) AND
(QrySSIMSAllitemswithAllVendors.FACILITY =
[Qry(1c)POReceivings].FACILITY) AND
(QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD =
[Qry(1c)POReceivings].CORP_ITEM_CD)
WHERE (((QrySSIMSAllitemswithAllVendors.CORP)="001") AND
((tblSSIMSItemNumbers.Class)=[Forms]![frmSystemsReport]![cboxClass])
AND ((tblSSIMSItemNumbers.Categorie)=[Forms]![frmSystemsReport]!
[cboxCategories]) AND ((tblSSIMSItemNumbers.Group)=[Forms]!
[frmSystemsReport]![cboxgroup]) AND (([Qry(1c)POReceivings].DATE_RECV)
Between [Start Date:] And [End Date:]));



thanks in advance.

Ryan

 

[Tom Wickerath]

Hi Ryan,

How do I get the filtered selection from cboxclass into
cboxcategories?

How to synchronize two combo boxes on a form
http://support.microsoft.com/?id=289670

My problem is that when I run the report it comes up with error#'s.

With your form open, and the proper selections made in the three combo
boxes, try running the query. Does it return any records? If the answer is
no, and you have any calculated controls on the report, this could explain
the # Errors. Since Access 2000, reports have included a NoData event
procedure that you can use to cancel opening the report, if there is no data.

You might also be getting circular reference errors, if you have any
expressions in the control source of text boxes that have the same name as a
field included in the expression. Here are some possibilities:

You Cannot Sum Calculated Controls in Forms or Reports
http://support.microsoft.com/?id=207763

Troubleshooting Tips for Error Values
http://support.microsoft.com/?id=209132

#ERROR Message When You Reference Subreport Controls
http://support.microsoft.com/kb/288251

#Error when the Subform has no records
http://www.mvps.org/access/forms/frm0022.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have 3 comboboxes. The first one named cboxClass, which pulls from
tblClass table has a selection of items where when you select the
filtered choices should go into combobox2 named cboxCategories, which
pulls from tblCategories table. Then it does the same from cbox2 to
cbox3. Cbox3 is named cboxGroup because it pulls from tblGroup table.
Now I have this SQL code. I go into record source of cbox2 and link it
to cboxclass. The form name is frmsystemsreport.

SELECT tblCategories.CategoryID, tblCategories.CategoryName,
tblCategories.ClassID
FROM tblCategories
WHERE (((tblCategories.ClassID)=[Forms]![frmSystemsReport]!
[cboxClass]));

How do I get the filtered selection from cboxclass into
cboxcategories?

Also after this works, I have a query that runs a report. I put the
fields Class, categorie and group into the query and in the criteria I
link it to the combobox, so in the class field I link it to the
cboxclass and so on for the other two fields categorie and group. My
problem is that when I run the report it comes up with error#'s. How
do I fix. Here is the SQL

SELECT QrySSIMSAllitemswithAllVendors.CORP,
QrySSIMSAllitemswithAllVendors.DIVISION,
QrySSIMSAllitemswithAllVendors.FACILITY,
QrySSIMSAllitemswithAllVendors.DST_CNTR,
tblSSIMSItemNumbers.ItemNumber,
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD,
tblSSIMSItemNumbers.Class, tblSSIMSItemNumbers.Categorie,
tblSSIMSItemNumbers.Group, QrySSIMSAllitemswithAllVendors.DESC_ITEM,
QrySSIMSAllitemswithAllVendors.VEND_NUM,
QrySSIMSAllitemswithAllVendors.VEND_SUB_ACNT,
QrySSIMSAllitemswithAllVendors.NAME, [Qry(1c)POReceivings].AMT_RECV,
QrySSIMSAllitemswithAllVendors.SIZE_NUM,
QrySSIMSAllitemswithAllVendors.SIZE_UOM, IIf([SIZE_UOM]="OZ",
[SIZE_NUM]/16,[SIZE_NUM]) AS OZTOLBCONV,
QrySSIMSAllitemswithAllVendors.PACK_WHSE,
[AMT_RECV]*[PACK_WHSE]*[OZTOLBCONV] AS TOTALVOL,
QrySSIMSAllitemswithAllVendors.UNITCOST,
QrySSIMSAllitemswithAllVendors.PERUNITCOST, [PERUNITCOST]/[OZTOLBCONV]
AS LBPRICE, QrySSIMSAllitemswithAllVendors.COST_IB,
[LBPRICE]*[TOTALVOL] AS SPEND
FROM (tblSSIMSItemNumbers INNER JOIN QrySSIMSAllitemswithAllVendors ON
tblSSIMSItemNumbers.ItemNumber =
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD) INNER JOIN
[Qry(1c)POReceivings] ON (QrySSIMSAllitemswithAllVendors.CORP =
[Qry(1c)POReceivings].CORP) AND
(QrySSIMSAllitemswithAllVendors.DIVISION =
[Qry(1c)POReceivings].DIVISION) AND
(QrySSIMSAllitemswithAllVendors.FACILITY =
[Qry(1c)POReceivings].FACILITY) AND
(QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD =
[Qry(1c)POReceivings].CORP_ITEM_CD)
WHERE (((QrySSIMSAllitemswithAllVendors.CORP)="001") AND
((tblSSIMSItemNumbers.Class)=[Forms]![frmSystemsReport]![cboxClass])
AND ((tblSSIMSItemNumbers.Categorie)=[Forms]![frmSystemsReport]!
[cboxCategories]) AND ((tblSSIMSItemNumbers.Group)=[Forms]!
[frmSystemsReport]![cboxgroup]) AND (([Qry(1c)POReceivings].DATE_RECV)
Between [Start Date:] And [End Date:]));



thanks in advance.

Ryan

 

[ryan.fitzpatrick3]

Hi Tom,

I get nothing on the query. The three combo boxes work where the 2nd
is a filter for the first and 3rd for the 2nd. For example in the
first I have "FLOUR" the 2nd I have "WHITE" the 3rd I have "ORGANIC".
I'd like to have these go right into the query. The query I would like
to look for those 3 criterion. I have the tables where the combo boxes
pull from in the query so I have fields in the query and the in the
criteria I have each field linked to each respective combo box. I'm
guessing that instead of "FLOUR" OR "WHITE" it pulls the unique number
assigned to it and the query pulls nothing because the it's looking
for a text and gets a number. That's what I think is going on.

Hi Ryan,

How do I get the filtered selection from cboxclass into
cboxcategories?

How to synchronize two combo boxes on a form
http://support.microsoft.com/?id=289670

My problem is that when I run the report it comes up with error#'s.

With your form open, and the proper selections made in the three combo
boxes, try running the query. Does it return any records? If the answer is
no, and you have any calculated controls on the report, this could explain
the # Errors. Since Access 2000, reports have included a NoData event
procedure that you can use to cancel opening the report, if there is no data.

You might also be getting circular reference errors, if you have any
expressions in the control source of text boxes that have the same name as a
field included in the expression. Here are some possibilities:

You Cannot Sum Calculated Controls in Forms or Reports
http://support.microsoft.com/?id=207763

Troubleshooting Tips for Error Values
http://support.microsoft.com/?id=209132

#ERROR Message When You Reference Subreport Controls
http://support.microsoft.com/kb/288251

#Error when the Subform has no records
http://www.mvps.org/access/forms/frm0022.htm

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I have 3 comboboxes. The first one named cboxClass, which pulls from
tblClass table has a selection of items where when you select the
filtered choices should go into combobox2 named cboxCategories, which
pulls from tblCategories table. Then it does the same from cbox2 to
cbox3. Cbox3 is named cboxGroup because it pulls from tblGroup table.
Now I have this SQL code. I go into record source of cbox2 and link it
to cboxclass. The form name is frmsystemsreport.

SELECT tblCategories.CategoryID, tblCategories.CategoryName,
tblCategories.ClassID
FROM tblCategories
WHERE (((tblCategories.ClassID)=[Forms]![frmSystemsReport]!
[cboxClass]));

How do I get the filtered selection from cboxclass into
cboxcategories?

Also after this works, I have a query that runs a report. I put the
fields Class, categorie and group into the query and in the criteria I
link it to the combobox, so in the class field I link it to the
cboxclass and so on for the other two fields categorie and group. My
problem is that when I run the report it comes up with error#'s. How
do I fix. Here is the SQL

SELECT QrySSIMSAllitemswithAllVendors.CORP,
QrySSIMSAllitemswithAllVendors.DIVISION,
QrySSIMSAllitemswithAllVendors.FACILITY,
QrySSIMSAllitemswithAllVendors.DST_CNTR,
tblSSIMSItemNumbers.ItemNumber,
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD,
tblSSIMSItemNumbers.Class, tblSSIMSItemNumbers.Categorie,
tblSSIMSItemNumbers.Group, QrySSIMSAllitemswithAllVendors.DESC_ITEM,
QrySSIMSAllitemswithAllVendors.VEND_NUM,
QrySSIMSAllitemswithAllVendors.VEND_SUB_ACNT,
QrySSIMSAllitemswithAllVendors.NAME, [Qry(1c)POReceivings].AMT_RECV,
QrySSIMSAllitemswithAllVendors.SIZE_NUM,
QrySSIMSAllitemswithAllVendors.SIZE_UOM, IIf([SIZE_UOM]="OZ",
[SIZE_NUM]/16,[SIZE_NUM]) AS OZTOLBCONV,
QrySSIMSAllitemswithAllVendors.PACK_WHSE,
[AMT_RECV]*[PACK_WHSE]*[OZTOLBCONV] AS TOTALVOL,
QrySSIMSAllitemswithAllVendors.UNITCOST,
QrySSIMSAllitemswithAllVendors.PERUNITCOST, [PERUNITCOST]/[OZTOLBCONV]
AS LBPRICE, QrySSIMSAllitemswithAllVendors.COST_IB,
[LBPRICE]*[TOTALVOL] AS SPEND
FROM (tblSSIMSItemNumbers INNER JOIN QrySSIMSAllitemswithAllVendors ON
tblSSIMSItemNumbers.ItemNumber =
QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD) INNER JOIN
[Qry(1c)POReceivings] ON (QrySSIMSAllitemswithAllVendors.CORP =
[Qry(1c)POReceivings].CORP) AND
(QrySSIMSAllitemswithAllVendors.DIVISION =
[Qry(1c)POReceivings].DIVISION) AND
(QrySSIMSAllitemswithAllVendors.FACILITY =
[Qry(1c)POReceivings].FACILITY) AND
(QrySSIMSAllitemswithAllVendors.CORP_ITEM_CD =
[Qry(1c)POReceivings].CORP_ITEM_CD)
WHERE (((QrySSIMSAllitemswithAllVendors.CORP)="001") AND
((tblSSIMSItemNumbers.Class)=[Forms]![frmSystemsReport]![cboxClass])
AND ((tblSSIMSItemNumbers.Categorie)=[Forms]![frmSystemsReport]!
[cboxCategories]) AND ((tblSSIMSItemNumbers.Group)=[Forms]!
[frmSystemsReport]![cboxgroup]) AND (([Qry(1c)POReceivings].DATE_RECV)
Between [Start Date:] And [End Date:]));

thanks in advance.

Ryan

 

[Tom Wickerath]

Hi Ryan,

I get nothing on the query.

Okay, so this problem needs to be solved first, before worrying about what
the report does or does not show.

I'm guessing that instead of "FLOUR" OR "WHITE" it pulls the unique number assigned to
it and the query pulls nothing because the it's looking for a text and gets a number. That's
what I think is going on.

You may be correct. In your first message, you wrote:
__________________
I go into record source of cbox2 and link it to cboxclass. The form name is
frmsystemsreport.

SELECT tblCategories.CategoryID, tblCategories.CategoryName,
tblCategories.ClassID
FROM tblCategories
WHERE (((tblCategories.ClassID)=[Forms]![frmSystemsReport]!
[cboxClass]));
__________________

So, Column 1 of cbox2 appears to be CategoryID. Column 2 appears to be
CategoryName. Is the bound column of this combo box Column 1? If the answer
is yes, then your query is seeing an ID value as it's criteria. If the
underlying table stores the text values "FLOUR" OR "WHITE" instead of
corresponding CategoryID values, then you will not get any records returned.
Incidently, a combo box has a Row Source property, but not a Record Source.
(Forms have a Record Source property).

Do you have any of those nasty table lookup fields defined? These are lookup
fields that are defined at the table or query level (when you click into a
cell, you see a dropdown box)? See the 2nd Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[ryan.fitzpatrick3]

Yes in the 3rd column is where it links to the previous table. It has
a combo box for selection if you want to change which class you want
it to go to. I take from the ten commandments that you don't want
this? What do I do, link it by name(text) instead of number?

thanks for the row source fyi, i'm still learning this program, I
started in Jan.

Ryan

Hi Ryan,

I get nothing on the query.

Okay, so this problem needs to be solved first, before worrying about what
the report does or does not show.

I'm guessing that instead of "FLOUR" OR "WHITE" it pulls the unique number assigned to
it and the query pulls nothing because the it's looking for a text and gets a number. That's
what I think is going on.

You may be correct. In your first message, you wrote:
__________________
I go into record source of cbox2 and link it to cboxclass. The form name is
frmsystemsreport.

SELECT tblCategories.CategoryID, tblCategories.CategoryName,
tblCategories.ClassID
FROM tblCategories
WHERE (((tblCategories.ClassID)=[Forms]![frmSystemsReport]!
[cboxClass]));
__________________

So, Column 1 of cbox2 appears to be CategoryID. Column 2 appears to be
CategoryName. Is the bound column of this combo box Column 1? If the answer
is yes, then your query is seeing an ID value as it's criteria. If the
underlying table stores the text values "FLOUR" OR "WHITE" instead of
corresponding CategoryID values, then you will not get any records returned.
Incidently, a combo box has a Row Source property, but not a Record Source.
(Forms have a Record Source property).

Do you have any of those nasty table lookup fields defined? These are lookup
fields that are defined at the table or query level (when you click into a
cell, you see a dropdown box)? See the 2nd Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi Tom,

I get nothing on the query. The three combo boxes work where the 2nd
is a filter for the first and 3rd for the 2nd. For example in the
first I have "FLOUR" the 2nd I have "WHITE" the 3rd I have "ORGANIC".
I'd like to have these go right into the query. The query I would like
to look for those 3 criterion. I have the tables where the combo boxes
pull from in the query so I have fields in the query and the in the
criteria I have each field linked to each respective combo box. I'm
guessing that instead of "FLOUR" OR "WHITE" it pulls the unique number
assigned to it and the query pulls nothing because the it's looking
for a text and gets a number. That's what I think is going on.

 

[ryan.fitzpatrick3]

do you want to match autonum with number field? I'm trying text to
text, on the 1st to the 2nd combo box it works but from the 2nd to the
3rd it says indeterminate. And the lookup fields in column 3 are
dropdown boxes, how do I get away from that or am I supposed to get
away from that?

Ryan

Yes in the 3rd column is where it links to the previous table. It has
a combo box for selection if you want to change which class you want
it to go to. I take from the ten commandments that you don't want
this? What do I do, link it by name(text) instead of number?

thanks for the row source fyi, i'm still learning this program, I
started in Jan.

Ryan

Hi Ryan,

Okay, so this problem needs to be solved first, before worrying about what
the report does or does not show.

You may be correct. In your first message, you wrote:
__________________
I go into record source of cbox2 and link it to cboxclass. The form name is
frmsystemsreport.

SELECT tblCategories.CategoryID, tblCategories.CategoryName,
tblCategories.ClassID
FROM tblCategories
WHERE (((tblCategories.ClassID)=[Forms]![frmSystemsReport]!
[cboxClass]));
__________________

So, Column 1 of cbox2 appears to be CategoryID. Column 2 appears to be
CategoryName. Is the bound column of this combo box Column 1? If the answer
is yes, then your query is seeing an ID value as it's criteria. If the
underlying table stores the text values "FLOUR" OR "WHITE" instead of
corresponding CategoryID values, then you will not get any records returned.
Incidently, a combo box has a Row Source property, but not a Record Source.
(Forms have a Record Source property).

Do you have any of those nasty table lookup fields defined? These are lookup
fields that are defined at the table or query level (when you click into a
cell, you see a dropdown box)? See the 2nd Commandment of Access, here:

The Ten Commandments of Access
http://www.mvps.org/access/tencommandments.htm

Tom Wickerath
Microsoft Access MVPhttp://www.accessmvp.com/TWickerath/http://www.access.qbuilt.com/html...
__________________________________________

 

[Tom Wickerath]

Combining the last two posts....

Yes in the 3rd column is where it links to the previous table.

I'm not sure which question I asked that this answers...

It has a combo box for selection if you want to change which class you
want it to go to. I take from the ten commandments that you don't want
this?

A lookup field defined at the table or query level will generally have three
properties that you can see in table design view (or query design view, if
defined at the query level):

Display Control (usually a combo box)
Row Source Type (usually Table/Query)
Row Source (can be a table, saved query, SQL statement, value list, etc.)

Open the sample Northwind.mdb database. I'm assuming you are using Access
2000, 2002 or 2003 for these next set of steps. The instructions will need to
be modified if you are using Access 2007. Open the Order Details table in
normal preview mode. Click your mouse into the ProductID field. Do you notice
the dropdown combo box as soon as you do this? This is a nasty table lookup.
This field actually stores numeric values, but displays the associated text.

Now open the Order Details table in design view. Click into the ProductID
field. Press the F6 button to change focus to the lower window. Select the
Lookup tab. You should see Combo Box as the Display Control, Table/Query as
the Row Source Type, and a SQL statement as the Row Souce (SELECT
[ProductID], [ProductName] FROM Products ORDER BY [ProductName]; ). Change
the Display Control to Text Box to get rid of the Lookup. Save this design
change. Open the table and view the data once more.

What do I do, link it by name(text) instead of number?

I'm not sure what you are asking me here...

thanks for the row source fyi, i'm still learning this program, I
started in Jan.

You're doing great. Keep up the hard work!

do you want to match autonum with number field?

Yes, specifically with Long Integer as the Field Size. I assume you are
doing this in the Relationships view (Tools | Relationships). An
indeterminate relationship results when one of the fields involved in the
relationship is not uniquely indexed (either a primary key, or it has
Indexed: Yes (No Duplicates) set).

Defining relationships between tables
http://support.microsoft.com/?id=304467


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________