Help with combo boxes, Please!

[Bachfile]

Hi, I have two combo boxes that use similar information for this museum database that I have just started (but its working finally)

To catalogue the artifacts for this museum I have many descriptive areas, including the object's category and subcategory. A category would be personal objects, furnishings etc. and subcategories would be toiletries, recreational objects, toys, etc.

For each category there are a specific number of unique subcategories. Right now, I click on the category, and then I click on a subcategory but I would like the combo box for subcategory to list only the subcategories related to the category and still be able to store it in a specific field. I realize this will probably require some programming which I lack but any advice on how I would go about it would be much appreciated.

Thanks a lot guys!

Bachfile

 

[RichMorrison]

Search the forum for "Cascading Combo Box". There are lots of threads on this topic.

RichM

 

[Bachfile]

I searching for the threads but none came up that gave instructions on creating cascading combo boxes. Do you mind explaining that for me? That would be great, thanks.


Bachfile

 

[RichMorrison]

I just did a search with "cascading combo" and got 68 threads.

I am sure you will find one with instructions.

RichM

 

[vangogh228]

Yes, there are many with instructions. Here are some:

Assuming:
1. You have tables called tblAttractionType and tblAttractions.
2. The tblAttractionType table has the fields of TypeAutoNo and TypeofAttraction.
3. The tblAttractions table has the fields of AttactionAutoNo, NameofAttraction, and AttractionType, which is the related field with the TypeAutoNo field in the other table.
4. Your form contains the combo box fields called Type and Attractions.

Enter the following properties for your two form fields:

Type Field

After Update Event:
Private Sub Type_AfterUpdate()
Me.Attractions.Requery
End Sub

Row Source:
SELECT [tblAttractionType].[TypeAutoNo], [tblAttractionType].[TypeofAttraction] FROM tblAttractionType;

Column Widths:
0";1”

Bound Column:
1


Attractions Field

Row Source:
SELECT [tblAttractions].[AttactionAutoNo], [tblAttractions].[NameofAttraction], [tblAttractions].[AttractionType] FROM tblAttractions WHERE ((([tblAttractions].[AttractionType])=[Forms]![FormView]![Type]));

Column Widths:
0";1.0007";0"

Bound Column:
1

The related field is an Autonumber in the one table, and a Number field in the many table.

Works every time.

Tom

 

[Bachfile]

Thanks a lot for the code!

Unfortunately, I try it out, and everything seems to work fine except that no entries are showing up in the SubCategory (your Attraction) combo box to select. Is there any way I could send you my form + the two tables zipped up? I could really use some outside help on this problem because it just doesn't want to work for me!

I have double and triple checked everything and it just doesn't want to work - I probably have inputted some wrong syntax by accident and I'm just not seeing it.

Or I could just post all of the related code for everyone to see if that would be a better option.

Cheers,

Bachfile

 

[Hayley Baxter]

Hi Batchfile

Did you get this problem resolved? If not I can help you with this. I have several examples one of them is a sample based on the above post. Let me know if you want them to look at.

Hay

 

[Bachfile]

Thanks a heck of a lot guys! I figured it out FINALLY!! All the code was fine, it was just the relationship between the two tables that was the problem and I was able to fix that. As soon as that was fixed it started spitting out the correct subcategories!

Thanks for all the help guys!

 

[Sorrells]

Batchfile,

If you could, let me know what the table relationship was that caused the problem. I have the same problem and cannot resolve it.

Unlike the example provided in this thread, my subform has to be in datasheet view. I can get the fields in the data table to update correctly but the display for a potential user is horrendous!

In fact, in my application, there is no relationship used between two tables, the main form is upbound and the subform is based on a query.

I may well be barking up the wrong tree!

 

[Bachfile]

Hi Sorrells,

The problem I had was with the one to many relationship that I had between the two tables I was using to get these two combo boxes synchronized. I had a one to many relationship, but I couldn't enforce referential integrity because the data types were different (long integer and decimal). Once I fixed that and enabled referential integrity and cascade updating, it worked for me.

You obviously took a different route - one that I definitely tried. In your query, you must still have two separate tables that you are joining in some way right? Try enforcing referential integrity in that relationship and see where it gets you.

Cheers,

Bachfile

 

[vangogh228]

Bachfile:

Sorry I haven't responded. Haven't been on the forums for a while. It sounds as though you got it working ok. I did neglect to enter the info re the necessary query. Sorry about that. Did you get your query written ok... and is all still working?

Tom

 

[Stephanie T.]

I’ve been studying the code presented here, trying to set up my own cascading Combo Boxes and still having some trouble. Your help would be gratly appciated.

The second box keeps coming up blank.

Here is what I have:

On my Sales Order form (frmSalesOrders), I have a combo box that has a list of our Distributors. This is for the “bill to” address. Here is the query (written in the row source) that runs that box:

SELECT tblDistributors.ID, tblDistributors.[Company Name] FROM tblDistributors;

This box runs just fine. I have an After_Update:

Private Sub Distributor_AfterUpdate()
Me.Warehouse.Requery
End Sub

In the “Warehouse” field I would like to be able to have a choice of the “ship to or warehouse” addresses for that Distributor. Many Distributors have more than one warehouse. Obviously, the goal is that the only choices that come up in the Warehouse field, a combo box, would be relevant addresses for the distributor chosen in the Distributor field, combo box.

Here is the query that runs the warehouse field:

SELECT tblWarehouse.WarehouseID, tblWarehouse.Warehouse, tblWarehouse.Distributor FROM tblWarehouse; WHERE ((([tblWarehouse].[Distributor])=[Forms]![FormView]![Warehouse]));

Not being very code literate I have also tried the end statement as:

((([tblWarehouse].[Distributor])=[Forms]![FormView]![Distributor]));

I have a One-to-Many relationship set up between the Distributor table and the Warehouse table, with Enforced Referential Integrity and Cascade Update Related Fields.

Regardless, whatever I try the “warehouse/ship to” field comes up blank. It is called Warehouse for all intents and purposes. Can anyone help? I’m in Access 97 if that makes a difference.

Thanks,
Stephanie