Combobox Data

[remco]

I am building a database that has two comboboxes in it.
The first one is set up to select a province from its own table.
The second one selects data from it's own table the has two colunms.(province and site name).
I would like the this second combobox to only contain data based on the selection of the first combobox.
Can you help?


Thanks.

 

[SydneyGeek]

This technique involves building an information loop. Here's how:
Combo1 is where you select Province.
Combo2 has Site and Province. (Province is the last field, and formatted not to display). So...
1. in the query builder for Combo2, pick those 2 fields IN THAT ORDER.
2. In the Criteria line for Province, put [Combo1]. Close the combo, save when prompted.
3. Still in Combo2, set the Column Count to 2, the Bound Column to 1, and the Column widths to 3;0 [This ensures that the Site is stored in the field, and Province will not display in the drop-down].
4. Now open Combo1 in design view. Go to the After Update event and double-click the blank line. You'll see [Event Procedure] in the line. Click the Builder (...) button on that line, and in the code, type [Combo2].Requery

Close, save, and that's it.
Note: Change the Combo1 and Combo2 names to suit.

Denis

 

[remco]

Denis
Thank you for the information.
But i'm having some troubles.
I am just learning access and have been trying to get to the proper places to complete this task.
Perhaps I did not give you enough information. When I am in the design view for the tables, I select Lookup Wizard... to create a lookup colunm for each of the fields. IE: Province, Site Name
This then creates a combobox in the table for what needs to be selected when inputing the data.

Am I not doing this the proper way? Or is there a better approach to take?

The more description you can give me the better as I'm still a newbe at this.

Thanking you in advance for you time. ray:

 

[SydneyGeek]

The info that I gave you was for working on a form -- much better for data entry because it gives you more control and a better interface.

Create an autoform from your table (select a table and click the Autoform icon on the toolbar). Save the form. Now open it in design view.
The field with the lookup should have a combobox -- the others will be standard text boxes.
If you can't see the Toolbox, click the Tool icon (hammer and spanner). On the Toolbox is a wand icon -- clicking this activates the wizards when you add a control to the form.
Click the Combo box control on the toolbox, and click on the form. The wizard will lunch. Follow the prompts to create a combo that selects from the Province table/field. This will be the Combo1 from my example; Combo2 is the lookup field that you created previously.

See if that gets you going in the right direction...

Denis

 

[remco]

Thanks,

I'll give it a try.

 

[Blue Hornet]

Just a quick hello and thanks, guys. Remco for asking the question and Geek for the detailed answer. This was just what I was looking for a couple of days ago, and it worked perfectly for me.

One followup question, Geek: Where does one start to learn these little tricks? I'm pretty experienced in Excel and VBA for that application, but I'm a babe in the woods in Access VBA. And with no way to 'record' macros as in Excel ... yikes! I've used the macro builder in Access but it's not all that's needed for things like this.

But thanks again, both!

Chris

 

[SydneyGeek]

Hi Chris,

Thanks for your comment. Yeah, I agree that getting a handle on creating Access code can be difficult because the macros are limited. However, you can always convert macros to VBA using Save As. Gives some clues on the object model.
Getting started in Access VBA is sort of a "how long is a piece of string" question. Picking up the tricks was sort of a piecemeal thing for me. I did a fair bit of training a few years back, and I found that the different courseware providers all had different tips and tricks. The VBA and interaction stuff usually came from the Advanced manuals -- maybe you could get a couple of manuals and work your way through. I also have a couple of favourite web sites -- http://www.helenfeddema.com and The Access Web, which you can reach via http://www.mvps.org I happen to like Helen Feddema's style and she has written a number of books, including Microsoft Access 2002 Inside Out (an Intermediate / Advanced book) and Expert One-on-one Access Application Development (more advanced). I have both of these. What else? This site is good, and there is an Access book -- Grover Park George 0n Access -- which looks pretty good from the Table of Contents. Don't have this one but I haven't been disappointed yet by the books I have bought from here -- I've got 3 of the Excel books. Also, you can try the MS Knowledge Base -- but search it from Google, much easier.

Hope that helps

Denis