ComboBox heirarchy

[welbot]

Hi there!
I'm learning how to make a database for use in keeping track of some products. Extremely simple I should think. Just simple stuff for home.

I want a form/web page (working with form for now) with a 3 combo boxes.. maybe more, but 3 for now...

Category
Sub Category
Sub Sub Category

I have 3 tables that correspond to the 3 combo boxes. As one might expect, I'd like the parent selection to feed the appropriate choices to the child.

All works well for the first 2. I even managed to get it to refresh the Sub Category if the Category choice is changed.

The problem I'm having is with the Sub Sub Category box. It just wont show any choices no matter what is selected in Sub Category.

Do I need to add some sort of OnFocus command to tell it when the Sub Category is selected, to refresh a different box?

Or should I be using another OnCurrent in my code builder? or both?

 

[workmad3]

I think the standard way of doing this is to change the recordsource of the child in the AfterUpdate method of the parent. so
parent_afterupdate
child.rowsource = "SELECT something or other FROM table WHERE value = '" & parent.value & "'"
end sub

hope that helps

 

[welbot]

Update... I got the heirarchy working fine! Turns out I wasn't storing the foreign key for the sub category...

But, this has come full circle again. I am now trying to get the combo boxes to accept input for new records in their appropriate tables.

Inserting a record in to the Categories table works a treat.
Inserting a record in to the SubCategories table works aswell, however because SubCategories is dependant Categories, I need to store the CategoriesID (foreign key in SubCategories table), otherwise the new SubCategory wont show up on the ComboBox.

I have no idea how to do this.. I figure since the user must select a category before entering a Sub Category, that I could pull the appropriate CategoriesID from the parent ComboBox which holds this value already on the same form. Problem is, I'm not sure how to code it...

Do I have to do an AfterInsert? AfterUpdate? Before either? :\

Plz help.. I've attached a copy of my mdb in it's current form.

 

[welbot]

Check above post.. I thought editing my last post would bump it :\

 

[workmad3]

is the foreign key stored in one of the combobox columns, but hidden? if so, then you should be able 2 get at it by just referenceing the combo box

so cmbParent.column([column of key here]) where you are storing the values.

hope this helps

 

[welbot]

Thanks for your reply.
I got it working fine, although with slightly different code, but now I'm having a problem with the SubSubCategory Box.

I can add new records (by entering them in the combobox), and they do appear in the drop down list, but it wont let me select the new additions. I click on the new addition in the list, and it always selects the first available subsubcategory from the list instead of what I choose.
However, I can freely select any of the existing records :|

any ideas on what might be happening?

 

[RuralGuy]

It would be very helpful if you would post the NotInList and AfterUpdate event code for the ComboBox.

 

[welbot]

ok! here's the notinlist code for the subsubcategory box

Private Sub Sub_Sub_Category_NotInList(NewSubSubCategory As String, Response As Integer)
'Suppress the default error message.
Response = acDataErrContinue
' Prompt user to verify if they wish to add a new value.
If MsgBox("The Sub Sub Category " & NewSubSubCategory & " is not in list. Add it?", vbYesNo) = vbYes Then
' Set Response argument to indicate that data is being added.
'Open a recordset of the SubCategories Table.
Dim db As DAO.Database
Dim rstSub_Sub_Category As DAO.Recordset
Dim sqlSubSubCategories As String
Set db = CurrentDb()
sqlSubSubCategories = "Select * From SubSubCategories"
Set rstSub_Sub_Category = db.OpenRecordset(sqlSubSubCategories, dbOpenDynaset)
'Add a new SubCategory with the value that is stored in the variable NewSubCategory.
rstSub_Sub_Category.AddNew
rstSub_Sub_Category![SubSubCategoryName] = NewSubSubCategory
rstSub_Sub_Category![SubCategoriesID] = [Forms]![Category_Selection]![Sub_Category]
rstSub_Sub_Category.Update
'Inform the combo box that the desired item has been added to the list.
Response = acDataErrAdded
rstSub_Sub_Category.Close 'Close the recordset
End If
End Sub

There is currently no afterupdate for that particular combobox, but it doesn't matter if I have one or not... it still does the same thing...

I did try adding the following (which is the code I have for the afterupdate of the first two boxes), but it doesn't seem to do anything....

Private Sub Sub_Sub_Category_AfterUpdate()
Me.[Sub_Sub_Category] = Null
Me.[Sub_Sub_Category].Requery <<This is the only line i'm using atm
Me.[Sub_Sub_Category] = Me.[Sub_Sub_Category].ItemData(-1)
End Sub


Again, i've posted a copy of my .mdb to make it easier to follow!

Much appreciate the help

 

[RuralGuy]

Here's your mdb back. I made some changes and put some notes in the code.

You should turn on "Compact on Close".

 

[welbot]

MWAH!!!!! <<<< That's me kissin your ass Ruralguy!!!! hawt stuff baby!!! I was beginning to give up hope!
/me is a fan for life! I'll be back to give help where I can

 

[RuralGuy]

You're welcome I think.

 

[alexlancaster]

Rural Guy:
You seem to be the only guy in all forums I've searched who's been able to answer clearly how to use categories/subcategories. I'm very new to Access and its logic. I'm making a library database for a non-profit, non-govmnt organization.
I took the file you posted and simply added a 'Books' table, which I do not seem to be able to integrate into the categories and subcategories.
Basically, I need a form that will allow me to enter the categories, subcats and subsubcats.
I've gone as far as getting the info displayed in one form. However, I cannot seem to add the behaviuor of the refreshing comboboxes.
I have attached the file to better illustrate this.
Thanks

 

[RuralGuy]

Hi AlexLancaster,

While I'm playing with your "Books Form" I found this with the search function here. It does not exactly cover your situation but I thought you might like to look at it for ideas. Follow this link

You seem to be the only guy in all forums I've searched who's been able to answer clearly how to use categories/subcategories.

I know of many others but thank you for the compliment, I'm flattered.

 

[alexlancaster]

Update on the books database

Hello again!
I've been trying out a lot of different ways of making the form work. It's nearly complete. I've got most of the main form working. However, for some reason while browsing through the records, the SubCategory and SubSubCategory comboboxes appear blank, with no info of it. The information is in the record, it just seems that for some reason browsing through the records makes the information invisible.
I've been unable to pinpoint the problem. help...

Thanks