More Combo Box help

[djclntn]

I created a very simple database with1-Table which has, 'ProductID' 'ProductName' & 'ProductCategory' -- Created a Form hoping that the 'ProductCategory' would be depending upon which 'Product' is selected. I can pull down the "Product' but when I pull down the'ProductCategory' it says "characters found after SQL Statement." Where do you find the SQL for A Form? https://www.box.com/s/fb8sl94ofbz2hr99ekwg Thanks & Happy Holidays to ALL!

[Missinglinq]

I assume you're talking about the Category Combobox on the Form ProductsCategoriesfrm. Your RowSource is:

SELECT DISTINCT Products.ProductsName
FROM Products
ORDER BY MyProducts.ProductName;
SELECT Products.ProdctName
FROM Products
WHERE (((Products.ProductCategory)=[Forms]![frmProductCategory]![Prodcbx]))
ORDER BY Products.ProductName;

Which is actually two SQL Statements! The second one starts after the SQL Statement-ending semi-colon (;) of the first Statement, hence the error message!

My guess is that you you had one statement in there, tried to copy over it with the second statement, and ended up with both of them in the RowSource. I've done the same thing; you really need to delete the first statement, then paste in the second one.

I also noticed that in this mishmash you have

Products.ProductsName
MyProducts.ProductName
Products.ProdctName
Products.ProductName

which all appear to be referring to the same Field, despite being spelled three different ways:

ProductsName
ProductName
ProdctName

And in the latter SQL Statement, you refer to a Table named MyProducts, which doesn't even exist! As my signature says, The Devil's in the Details! There is no room in Access, or in any programming language, for that matter, for this kind of carelessness.

I assume you're trying to set up a Cascading Combobox here; the RowSource for the second Combobox would usually be placed, in this case, in the ProdNamecbx_AfterUpdate, although I suppose it would work as you've set it up.

Also note that most of the people here will not go to another site to download a file; you really need to Zip your file and place it in a post, here, as an attachment if you want it looked at by people.

Get the above straightened, and if you're still having problems, post back.

Linq ;0)>

[djclntn]

Ling, Thanks for responding to my post. I added the Code you provided into the Category Combobox, but I'm unclear as to what is my next step. I'm still getting the error message, 'Characters found after SQL Statement." I do understand your point that most people will not go to another site to download a file, however every time I try to attach a file to a post it fails.

[Missinglinq]

...I added the Code you provided into the Category Combobox, but I'm unclear as to what is my next step...

What code I provided? The only code in my post was the RecordSource from the
Combobox in your Form, which is incorrect, for the reasons given!

I think you need to go back and read my post a bit more carefully!

Linq ;0)>
Linq

[djclntn]

I see what you're saying, sorry Ling. Get back at it again tomorrow...been going on for over 2-wks, but I'm determine to figure this out! Thanks & I may need to follow up w/ you.

[ssanfu]

Hey Dave. PMFJI

When I was learning TurboPascal, there was a thing called a "Pick List". It was, you guessed it, a list of items to pick from. In VBA we know this as a combo box (also as a list box).

A combo box has a few properties that are really important:
ROW SOURCE - where the data to be displayed comes from
CONTROL SOURCE - where the bound column is saved to
BOUND COLUMN - which column's values (from the row source) to use as the value of the combo box
COLUMN COUNT - how many fields (columns) are available
COLUMN WIDTHS - specify the width of each column in a multiple-column combo box


If the ROW SOURCE TYPE is Table/Query, then you have 2 options: a table or a query.
*A table as the row source is not much use (IMO) - can't filter or sort the table.
*A query. You can enter a SQL statement in the Row Source property ("SELECT Field1, Field2 FROM MyTable;") or use a saved query (a query you can see in the Query section of the database window).

I usually use a saved query. It is easier to check or change. So create a new query. Name it "qryProducts". Add the table "tblProducts". Drag down the two fields 'ProductID' (an autonumber) and 'ProductName' (a text field). Set the Sort row for 'ProductName' to Ascending.
The SQL of the query looks like:

SELECT tblProducts.ProductID, tblProducts.ProductName
FROM tblProducts
ORDER BY tblProducts.ProductName;

Save the query. In the ROW SOURCE for the combo box, click the dropdown arrow and select the query "qryProducts".
On the Format Tab, the Column Count should be 2. Set the column Widths property to 0", 1"

Go to form view and click on the combo box. TADA!!! It should drop down a list of the product names. Good so far??

So you now have an unbound combo box.... Unbound being there is no place to save the selected value. (the form has no record source in my example)

If you wanted to type in the SQL into the ROW SOURCE property instead of using a saved query, you would type in:

SELECT tblProducts.ProductID, tblProducts.ProductName FROM tblProducts ORDER BY tblProducts.ProductName;

This is the basics of configuring a combo box. This was just so we are on the same form (so to speak )

If you are good with this, I will continue when you post back (or if there are questions....??).

[djclntn]

Steve, Thank you for responding to my thread. I followed your instructions in creating the first Combo Box (which I always managed). It's the second Combo Box which populates data dependent upon what is selected where I'm stuck!!!! The first Combo Box is the "ProductsCategory" e.g. Beverage, Crackers, & Condiments. The second Combo Box is "ProductName" e.g. Pepsi, Coke, Ritz, CheezeIt, Idomize Salt, white Pepper, Black Pepper etc

[ssanfu]

This is an example of setting up 2 cascading combo boxes.


I use two tables:.

The table structure for "tblcatagory" is:
CatagoryID_PK (Autonumber)
CatagoryName (Text)


The table structure for "tblProducts" is:

ProductID_PK (Autonumber)
ProductName (Text)
CategoryID_FK (Long) FK

The relationship:
Attachment 10476


After entering the data, the table looks like:
Attachment 10477

Attachment 10478


-----------------
Starting with a blank form (no controls). Name it "frmMain".
Add 2 combo boxes. Name one "cboCategory" and name the other "cboProducts"

Create a new query. Name the query "qryCatagories". The SQL for the query is:

SELECT tblCatagories.CatagoryID_PK, tblCatagories.CategoryName
FROM tblCatagories
ORDER BY tblCatagories.CategoryName;

We have to edit the query "qryProducts". Open "qryProducts" in design view. Add the field "CategoryID_FK" to the design grid.
In the criteria row for the column "CategoryID_FK", enter "[forms]![frmMain].[cboCatagory]". Save the query.

The SQL of the query should look like:

SELECT tblProducts.ProductID, tblProducts.ProductName, tblProducts.CategoryID_FK
FROM tblProducts
WHERE (((tblProducts.CategoryID_FK)=[forms]![frmMain].[cboCatagory]))
ORDER BY tblProducts.ProductName;

Configure the combo box "cboCategory" properties:

ROW SOURCE : qryCatagories
BOUND COLUMN : 1
COLUMN COUNT : 1
COLUMN WIDTHS : 2"


Configure the combo box "cboProduct" properties:

ROW SOURCE : qryProducts
BOUND COLUMN : 1
COLUMN COUNT : 3
COLUMN WIDTHS : 0"; 2"; 0"



If you try both combo boxes, you should be able to see the values in the drop down lists.
Now we have to requery the list returned in "cboProducts".
In the after update event of cboCatagories, enter: me.cboProduct.requery



The code should look like:

Private Sub cboCatagory_AfterUpdate()
   Me.cboProduct.Requery
End Sub

[djclntn]

Hi Ling, I’m still struggling with these ‘Product Category’ “Product Name’ Combo Boxes. I’m going to copy/show you what I have in each of the Combo Boxes Record Sources:

For the ‘ProductaNme’ I have:
SELECT DISTINCT MyProducts.ProductCatagory
FROM MyProducts
ORDER BY MyProducts.ProductCatagory;

For the ’ProductCatagory’ I have:
SELECT MyProducts.ProductName
FROM MyProducts
WHERE (((MyProducts.CatagoryName)=[Forms]![ProductsCatagoriesfrm]![Catagorycbx]))
ORDER BY MyProducts.CatagoryName;

Then, in the ProductName Combo Box After Update I have:
Private Sub Catagorycbx_AfterUpdate()
Forms!frmProductsCatagories.CatagoryNamecbx.Requer y
Me.CatagoryName = ""

I’m able to select a Product Category but when I go to select a Product Name it asks me to Enter a Parameter Value (MyProducts.CatagoryName)…?

[djclntn]

Forms!frmProductsCatagories.CatagoryNamecbx.Requer y -- Forms!frmProductsCatagories.CatagoryNamecbx.Requer y-- I fixked

[djclntn]

Steve, in the cboCategory I'm getting numbers. Record Sources "qryCategories" BOUND COLUMN : 1
COLUMN COUNT : 1
COLUMN WIDTHS : 2"

[ssanfu]

Steve, in the cboCategory I'm getting numbers. Record Sources "qryCategories" BOUND COLUMN : 1
COLUMN COUNT : 1
COLUMN WIDTHS : 2"

My bad.....


COLUMN COUNT : 2
COLUMN WIDTHS : 0" ; 2"

You can set the column width to whatever size it takes to see the value - 2" might be too large. It was just a starting point.

[djclntn]

In the "tblProducts" I have the Category_PK in the Table, but when the 'tblProducts is in Datasheet view there are no numbers like in the image

[ssanfu]

Did you enter the numbers?

If you look at the categories table, "Beverage" has an ID of 1. In the products table, Pepsi and Coke are beverages so they need to be linked to the Beverage category. (PK - FK relationship)

If they were in one table, you would see

ID, Product, Category (as text)
1,    Pepsi,   Beverage
2,    Coke,    Beverage

Move the word Beverage to its own table (tblCategory) and replace it with the PK from the Category table for Beverage.

You get:

ID, Product, CategoryFK (as Long)
1,    Pepsi,    1
2,    Coke,     1

Does any of this make more sense?

[djclntn]

From the tblCategory I put the Category_PK into the tblProducts, right? Does it go in as "CategoryID_PK" -or- "CategoryID_FK"? " Did you enter the numbers? " What do you mean, I thought the numbers were generated by the FK? I'm not getting the same results in the tblProducts as shown in the image, but I'm certain the Relationship between the 2-Tables is identical to how you have it in the image/picture. I'm not doing something right...?