Web Database - Cascading Combo boxes

[mmandel1]

First off, I am an inexperienced Access user, but I have quite a bit of technical expertise, so excuse me if my question seems too newbie-ish.

Please do not answer my question if you don't understand that Web Databases in Access 2010 are different than non-web ones, because they are. Some of the restrictions are:

• Tables ONLY have a Datasheet view
• Forms have no Design view (they do still have a Layout view)
• Queries have no SQL view (they do have a Design view)
• Certain wizards are not avaialble at all (like Combo box wizards)
• For relationships between tables, you have to use lookup fields
• VB cannot be used

I’m making a simple ordering system for people to order items. I’ve set up these tables:

• Categories
• Items

Right now, my Items table has a Category field that is a lookup to the Category text filed in my Categories table.

One of the things I’m trying to do initially is make a cascading combo box that works in an order form. I want the user to be able to select a Category which would then limit the Items to those associated with the different Categories (boxes, paper products, etc). So, essentially, I think I need to make cascading combo boxes. But, remember, this is a web database (see above differences).

But, being such an Access newbie, I can't even figure out how to associate a combo box I place on a Blank web form with one of my tables. When I place a new combo box on the blank form, no Combo Box Wizard appears. That just doesn't happen with a web database. I need to specifiy all the details of the combo box in layout view. When I look at the form in layout view, the property sheet for the newly added combo box's Control Source and Row Source are blank. Is the only way to associate that combo box with the table and field in that table to write a SQL statement and past it into the Row Source?

I can do that for the Category field. To make that combo box cascade to the correct Items, do I then need to make a query and put the correct SQL in its design view and then associate that query with the Item's combo box in the form?

Please don't send me to Access combp box tutorials that don't deal with web databses, because I have viewed dozens of those and they don't help because I don't know enough about Access to fill in the missing gaps about what is different about cascading combo boxes in web databases.

If I can get some help on creating such a simple form for two tables and however many queries are needed, I will write up an extremely clear tutorial myself. I promise! ;-)

[June7]

I have reviewed a few web databases posted by members in forum. I understand the differences and they are frustrating. If you can type an SQL statement into the RowSource property and a macro can requery the dependent combobox, then sounds like cascading comboboxes will work.

Are you using the Navigation Form object?

[mmandel1]

Thanks for your reply! Yesyerday I started playing around with SQL in the RowSource. I haven't yet worked with the Navigation Form object. I'm actually such a newbie I hadn't heard of that before. But it sounds like something I'll be using.

[June7]

I don't like the Navigation Form. I find it confusing and harder to code for. I think it was designed for use with web database and I don't build web database so I am able avoid the Navigation form.

[mmandel1]

As I learn more and more about web databases in Access for use in SharePoint, I'm seeing major limitations. I've managed to create two combo boxes that cascade, but I just discovered that the SQL DISTINCT and GROUP BY expressions can't be used for web databases in SharePoint! So now I've got a combo box that has duplicate values that my users have to sort through. Oh, well, still chugging away at it...

[June7]

Then you have to use a 'lookup' table. A table of distinct values for the combobox RowSource.

[mmandel1]

That worked! At first, I couldn't get it to.

But now I can have my first combo box use that lookup table (in my case Categories) and then set a temp variable (I called it strCat), which I then used in my second combo box to compare to a Categories field in my Items table so I could show the items with that Category.
What I learned is that Category field CANNOT be a lookup field to the Category table. That breaks things. The field can either be just a plain text field or a lookup field where I type in the values for later use. My "Test" field below is the latter sort of field. SQL for Row Source on my second combo box:

SELECT Items.Description FROM Items WHERE (((Items.Test)=[TempVars]![strCat]));

[June7]

Glad something works.

The TempVars is really needed? Can't just reference the first combobox by name in the SQL? Can in a regular database.

I never set lookups in tables, just on forms.

[mmandel1]

There are so many differences in web databases in Access (see my original post above). In fact I *am* referencing the name of the combo box in the SQL. But, to make one combo box in a web database in SharePoint "cascade" to another, the choice in the first combo box has to be stored somewhere and then compared to the correct field in the second combo box. I learned how to do this here:

http://social.msdn.microsoft.com/For...orum=accessdev