One combo box to control another 2

[Mayhem9]

Essentially I would like to use my current tool type combo box to control the options available in a second combo box, which will then contain sub types (I do not have this yet).

Currently, I have a tool log table, which is populated by my main form. There are several combo boxes, that reference other tables (each of which contain a single entry per recort). One of these is the tool type table (Type). This table simply contains the various tool types. The Tool_Log table and Type table structures are below:

Tool_Log
ToolID dbLong PrimaryKey Indexed
ManID dbText
Serial dbText
TypeID dbText
Size dbText
Description dbText
Set dbText
Year dbText
RRP dbCurrency
PurID dbText
Date dbDate
LocID dbText
ImagePath dbText
ImagePath2 dbText
TechDoc dbText
TechDocPath dbMemo
TechDoc2 dbText
TechDocPath2 dbMemo
TechDoc3 dbText
TechDocPath3 dbMemo

Type
Type dbText PrimaryKey Indexed

So currently if I select "wrench", it includes adjustable wrenches, pipe wrenches, box wrenches, combination wrenches etc. What I wold like to do is select "Wrench" from the Type combo box (cmbType) and then based on this selection have only the wrench sub types appear in the Sub Type combo box (cmbSubType [not yet present]).

I played with the code posted in the post but I was unsure as to whether the Sub Type would be in its own table or in the Type table.

Please let me know if I have not made this clear or I need to provide additional information.

Thanks,
Darren

 

[dhookom]

First, please excuse the critique of your table structure and some field names.

Your Doc, DocPath, etc fields are not normalized. What is you have a 4th TechDoc? You should not have to change a table structure to accommodate more information.

Type is not a good name for anything in Access. It must the type of something like ToolType. There is a similar issue with using Date, Description, and Year. Kick your apps up a notch to not use reserved words as field or object names.

Typically, I would have tables like tblToolTypes and tblToolSubTypes. Since you aren't even using an autonumber primary key, you could probably use a single table. The only issue would be if some tools don't have subtypes.

Roger Carlson has information on Cascading Combo boxes at

http://www.rogersaccesslibrary.com.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

Thank you for your reply and for your critique, as this is how I learn. Up to date, I have learned by looking at other databases and I have learned on here that this is not always a good thing. My intention now that the database is working well is to make a copy and then go back and rename some items (this way if it stops working, I know it is something I have done). MajP has been very helpful, along with you, in pointing out these things. I have been looking for info on such 'conventions' but haven't found much yet. Most either tell me what a table is and others already assume you know this.

I will look at Roger's site and see if I can find the information I need.

If I used a single table would I have to duplicate the tool type? e.g.

wrench. adjustable.
wrench. combination
wrench. open end
wrench. pipe

and so on.

Thanks,
Darren

 

[dhookom]

Yes, a single table would need to have multiple records with the same value of Tool Type. I would create two tables.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Thanks Duane,

I have managed to get the cascading combo boxes to work in testing and will incorporate it into my database shortly.

In the meantime, I been working on rectifying the naming issues you (and MajP) have pointed out and I have also tried to use a more conventional naming standard. However I am not sure what you mean when you said:

Your Doc, DocPath, etc fields are not normalized. What is you have a 4th TechDoc? You should not have to change a table structure to accommodate more information.

 

[dhookom]

I asked a question in that quote which you need to answer. Each tech document should be a separate record in a related table. This is called normalization. The same is true with you images. What if you have 3 or 4 images?

You should always create applications with the assumption that if you have 2 similar "attributes" you could have 3 or more. Adding fields, controls, and other design tasks should not be part of your application maintenance plan.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

I'm not sure how to answer that. If I suddenly realized that I needed another image, document or field then I guess I would add it in.

I'm not sure how else I can do this other than adding in a host of things now, which would remain blank for the majority of records, just in case I need them in the future. I would probably not know what to label them anyway.

At the moment, the majority of records will not have a second image and only the power tools, torque wrenches and a few others will have any documentation. For this reason, I have placed them on the second and third visible tab pages.

Thanks,
Darren

 

[dhookom]

Properly normalized tables would not store the image or document information in the main table. I would actually create a table of related files (documents or images) with a structure like:
[tt][blue]
tblToolFiles
======================
ToolFileID autonumber primary key
ToolID foreign key to Tool_Log.ToolID
FileType "Tech Doc", "Image", or possibly other values
FileTitle
FilePath
[/blue][/tt]
This structure allows you to add from 0 to many related files. You could display these records in a continuous subform.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

So if I understand this correctly, this is a 'single item' table in which the items are linked back to the tool in question (say via a combo box).

I have never used a continuous sub form (or any form of sub form I think) before, so I will have to do some reading on these but I wonder whether the data is input via the main form or separately (such I have to do for the manufacturer, tool type, location etc).

Thanks Darren

 

[dhookom]

I'm not sure what you mean by 'single item'. If a tool has 3 documents and 1 image, there would be 4 records in this table related to the main tool table.

These would all be entered/edited on a subform contained in the main form.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

I meant that the form only contains the fields for one item (image, document etc), as opposed to several (as in my main form). So each item is a new record. From your reply, I am guessing this is the case

Thanks,
Darren

 

[dhookom]

A form doesn't contain fields. A form might be bound to a Record Source that has fields. These fields are displayed in controls on the form. A single record is displayed in a single instance of the form. If a form is viewed as a continuous or datasheet then it may display many records.

Each item will be stored in its own record.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

I have the combo box control issue figured out and working, so the next challenge will be to try and figure out the related files table.

Thanks,
Darren.

 

[dhookom]

I don't know what combo box issue you had. I guess I don't know what you don't understand about the related files table. I suggested a structure for the table.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

I don't know what combo box issue you had.

This post was about me trying to get one combo box to control what data is visible in another combo box. I just realized that I posted too soon! I have it working OK in the modification of the example db that I downloaded and also in my first record. However, now it is holding the data in the second combo box based on what was selected for the first record!

I guess I don't know what you don't understand about the related files table. I suggested a structure for the table.

Yes you have given me a table structure to work with (thanks) but I have never used sub forms before so I know it isn't going to be a straight forward for me than for others here but I will try to figure it out for myself.

Thanks,
Darren

 

[dhookom]

I don't understand

it is holding the data in the second combo box based on what was selected for the first record!

You haven't told at all what you actually did. Is your main form continuous?

I don't think I have ever created a form without a subform. Access is a relational database system which suggests master and child tables. The typical interface for this is main forms with subforms. Access makes this quite easy with the Link Master/Child properties.

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

My main form (frmTool_Log) shows one record at a time and I am not using any sub forms. The underlying table is tblTool_Log, which is as follows:

tblTool_Log
ToolID dbLong PrimaryKey Indexed
ManufacturerID dbText
Serial dbText
[purple]CategoryID[/purple] dbText Indexed
[purple]SubCategoryID[/purple] dbText Indexed
Size dbText
Description dbText
Set dbText
Year dbText
RRP dbCurrency
PurchaseID dbText
Date dbDate
LocationID dbText
ImagePath dbText
ImagePath2 dbText
TechDoc dbText
TechDocPath dbMemo
TechDoc2 dbText
TechDocPath2 dbMemo
TechDoc3 dbText
TechDocPath3 dbMemo

Any field name with ID in it signifies it as receiving its data from another table, via a look-up combo box in the form. Please ignore the last several fields, as I will place these in their own table in time.

The two combo boxes that I am currently using in a cascade are [purple]CategoryID[/purple] and [purple]SubCategory ID[/purple]. Each has its own table:

tblCategory
Category dbText PrimaryKey Indexed

tblSubCategory
SubCategoryID dbLong PrimaryKey Indexed
CategoryID dbText
SubCategory dbText

In tblSubCategory, CategoryID is the value stored in tblCategory (which is the same as that stored in CategoryID in tblTool_Log).

In frmTool_Log the combo box for [purple]CategoryID[/purple] is called [purple]cmbCat[/purple] and has the following row source: [purple]SELECT DISTINCT tblCategory.Category FROM tblCategory ORDER BY Category;[/purple]

The combo box for [purple]SubCategoryID[/purple] is called [purple]cmbSubCat[/purple] and has the following row source: [purple]SELECT DISTINCTROW tblSubCategory.SubCategoryID, tblSubCategory.SubCategory FROM tblSubCategory WHERE (((tblSubCategory.CategoryID) Like forms!frmTool_Log!cmbCat)) ORDER BY SubCategory;[/purple]

There is an after update event for cmbCat, which has the following code:

 Private Sub cmbCat_AfterUpdate()
Me!cmbSubCat = Null
Me!cmbSubCat.Requery

End Sub

To get to this point I went to Roger Carlson's web site you pointed me to and downloaded a sample database and then played with it until I got it to work using the table structure and names I was using. It tested OK but the form in the sample db only allowed one record.

At this point I duplicated what I had done in my db and it appeared to work well as, I could select the category and then only the relevant sub category would be available. However, I later noticed that when selecting the category on other records (where the category was different) it did not alter the selection available for the sub category. So, if Screwdriver was selected for the category for record 2, the sub category would show items for ½” Drive tools (the selected category for record 1).

I tried to delete the entry for category on these records, close the form and then try again but it still the same. I apologize for long post and hope I have provided sufficient information. Please let me know if you require anything more.

Thanks,
Darren.

 

[dhookom]

You need to requery the subcategory combo box in the On Current event of the main form.

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Also, your Row Source should use "=" rather than "Like":

SELECT DISTINCTROW tblSubCategory.SubCategoryID, tblSubCategory.SubCategory FROM tblSubCategory WHERE (((tblSubCategory.CategoryID) = forms!frmTool_Log!cmbCat)) ORDER BY SubCategory;

Duane
Hook'D on Access
MS Access MVP

 

[Mayhem9]

Hi Duane,

I think I have coded the requery:

Private Sub subK()
   cmbSubCat.Requery
End Sub

I wasn't sure if I need to use the name of the combo box (cmSubCat) or the actual field name in the table (SubCategoryID). I tried with the latter but I was told that I had not defined the variable.

At first I thought it had solved my problems, as I was able to set the correct sub category for the second and third records. However, the fourth record returned the same problem. I closed the form and when I opened it again, the selection for sub category in records two and three had not been retained. The list in sub category is now showing the records for the category selected for the second record (if that all makes sense!).

What am I missing?

Also, I have changed 'like' to '=' as you have suggested.

Thanks,
Duane