Cascade Lookup or drop down - 2010

[Tablerone]

I would like to have lists of Manufacturers and Models in my database. When I select ManufacturerA in the first drop down, I want the Models drop down to be modified to only show those models actually made by ManufacturerA.

In the past I've done this by:
1) Creating a Manufacturers table
2) Creating a Models table, including a field for ManufacturerFK (foreign key)
3) Creating a relationship between the two
4) Using an AfterUpdate event for the Manufacturer field on the form, so that it changes the contents of the Model drop-down once a Manufacturer has been selected.

I have been looking at the Access 2010 "Assets" web database template, and am trying to learn about the changes made for web databases. I've long liked Datasheet View forms, but been frustrated with their inability to handle conditional drop-downs (as above) without affecting all lines on the sheet.

***My favoured work-around (which I'm not yet quite good enough to implement - so would like help) is a datasheet view form, where you click something to open another form to enter a new item. The Web template I've just mentioned does exactly this! In the ID column, you either click the ID number, or "New" (to enter a new item), and it brings up a form you can use to edit the item. Perfect!

Only trouble is the 2010 Web databases don't support relationships. So now I don't know how to setup the cascade drop downs with Manufacturer and Model again. I don't know much code and sometimes get really frustrated with Access. I often see this kind of cascade in real databases, so why isn't that functionality built into Access?



I had a look at the new data macros. I like how they affect a table, then also any forms built on it automatically. However, the instructional video I saw used data macros to set the second field to a given value based on the item entered in the first field, which is less powerful than a cascade where a choice leads to a SET OF VALUES rather than a given value.

Can anyone help?

Two routes
- build that pop-up form functionality (paragraph ***) into a desktop database, or
- make the cascade combo boxes work with the web database
Currently I'm not bothered which. Ideally I'd understand both.

[Tablerone]

Hunting through this forum, I just came across this video:
http://www.datapigtechnologies.com/f...combobox2.html

Datapig seems to have a slightly different method for implementing the cascade, although of course he uses the same after-update even because that's crucial.

What's not clear to me though is what work he has already done in tables to do with locations before this video starts. Has he created tables for regions and cities (with a relationship), or... I suppose he might not have bothered. Why did I bother? Have I done something in a round about way? Looks like I used to. I'm such an irregular Access user, that I can't remember if there was a good reason for using the "different tables with relationships and foreign key" setup or not.

[RuralGuy]

Here's another link that may add to your knowledge of Cascading Lists: http://www.fontstuff.com/access/acctut10.htm

[Tablerone]

The latest thing I'm stuck on (in the DataPig video) is this:
In Datapig's video, at 43 seconds it becomes clear that the source for cmbRegion is the Locations table, field Region. But of course region West would be listed several times, once for each corresponding location.

However, when Datapig drops down the Region combo box, North, South and West are each only listed once. How has he managed to achieve this?

Thanks for your reply. It looks like your comment might answer my question, I can't see exactly where it does yet, but only just started looking.

[Tablerone]

Aha, RuralGuy's reply does indeed answer my post #4.

The answer is SELECT DISTINCT (CTRL+F the page RuralGuy linked for "select distinct" to see what I mean)

This is why I used to create multiple tables linked by relationship and foreign key. That was to give me a table for eg "country" with each country only listed once, then there would be another table with all the locations in linked to the country by foreign key and a relationship. All because I'd never heard of SELECT DISTINCT. Now I have.

[Tablerone]

Right, I'm trying to ammend the Assets web template, as I think I may have mentioned.

I have already created a Models table containing Model and Manufacturer, on which to base my lookups.

Is there a way I can convert the Manufacturer and Model fields in the Assets table into Lookups, or do I have to create new fields then delete the old ones?

[RuralGuy]

You do not want to use Lookup Fields: http://access.mvps.org/access/lookupfields.htm
The ComboBox wizard can help you get the lookup's on your form.

[Tablerone]

OK, thank you.

Two more questions: I am stuck as to how to get the AFTERUPDATE event code into the 2010 code editor which I haven't used before. What do I choose at the green plus icon, and how should I go from there? Do I need a different view and how do I select it if I do? I wanted to paste in the AFTERUPDATE code from that other link you gave me then modify, but I can't get past the green plus sign.

Secondly, this will sound like a really basic question, but having set everything up, how do I ensure that my combo boxes on my form are entering their data onto a table? How do I link them back to the table, is that what binding means?
EDIT: I do not mean the source for the combo boxes. I mean getting the data stored onto a main table once a choice has been made in the combo box on the form.

[RuralGuy]

I'm not sure what you mean by "Green Plus Sign". You go to the properties sheet for the ComboBox and on the Event tab press the "..." button on the AfterUpdate event.

[RuralGuy]

Each ComboBox has a ControlSource that will bind the results of a selection to a field in the RecordSource of the Form.

[Tablerone]

As I think I mentioned several times, I am using a web template.

On a desktop database, clicking the ... allows you to choose from code builder, and 2 other options.

In a web database it goes straight to the macro builder. What should I do?

[RuralGuy]

Uh Oh...I'm in trouble here. Sorry but I missed that part abot the web. I'm pretty sure that requires macros and I'm out of my element there. Sorry to string you on like that.

[Tablerone]

Rural Guy, thanks for trying to help anyway.

Could you put me in touch with someone who ISN'T out of their element with 2010 Macros / web macros?

[RuralGuy]

I'm looking. I'll check with some other MVP's. Sorry again.

[Tablerone]

Right, don't worry for now RuralGuy, because I've achieved what I want to.

It turns out I was getting hung up on the wrong bit. Setting a Requery macro, is easy and is all I need. I didn't actually spot that Requery was available in the Macro Builder until someone pointed it out to me. I then quickly realised that Dropdown and Setfocus were luxuries that weren't essential, so their absence from the Macro options didn't matter.

I then had to focus on the difference between VBA code (that you can't use with these Web Databases) and the code that goes in the RowSource line (do you call it SQL?). So, the very helpful link you gave me:
http://www.fontstuff.com/access/acctut10.htm
(and I'm refering to the Sub code in the Single Row Source Table example)
has VBA code for the WHERE statement and basically says:
WHERE tblAll.Country = cboCountry.Value

in SQL you don't use ".value" and you have to say
WHERE tblAll.Country = [Forms]![NameOfTheForm]![cboCountry]

at least I think so, and this seems to check out for me in practice, please correct me if I'm wrong.

In summary I would say that Access can be very daunting for beginners trying to advance and it is easy to get worried the solution may lie in what seems like the ocean of what you don't know, rather than trusting that what you do know and understand might be enough. I expect at some point the mists evaporate and it all becomes clear.