Cascading list box

[ledgerr.rob]

Hey all.

I think i'm on my way at creating my home finance database. As part, I want to track the general categories and more specific items of those categories. To do this i've made a tblCategory and a tblItem. Then i've made a tblCombined where i've gone through and 'paired them up.' To this end i'm happy.

What i'd like is to generate a cascading list box where i select a category and the item list box only shows the resulting pairs. I've looked through some posts and followed some links but haven't been able to get this hammered out. I'm not able to get the second list box to populate with anything.

Any thoughts or ideas would be great

 

[Mr. B]

Give this modified version a try.

Take a look at the changes that I have made to your two list boxes in your "frmProjectedExpense" form..

Look specifically at the record source for each list box. This was your primary issue. The row source for the first list box is the "tblCategory" table with the first column being the AutoNumber (primary key) field. The column count was correct, however by making the CategoryID field to be the first field in the sql statement that serves as the row source and specifying a 0 (zero) in the Column Widths property, we are able to hide the first column of the two column in the row source. Making these changes will cause the first list box to display the Category Name but hold the value from the CategoryID field so we can refer to this value in the criteria for the second list box.

You will also notice that I did not use an actual defined query as the row source for either of the list boxes. Instead, I simply clicked the button at the right end of the row source property and defined a query which is then displayed as an sql statement. This way someone is not going to accidentally delete a query that I have to have as the row source for some control on a form.

The row source for the second list box has the tblItem table linked to the tblCategoryitemCombined table and uses the value (CategoryID) selected from the first list box as its criteria to filter the list of items.

Also look at the small amount of VBA code in the AfterUpdate event of the first list box. This code is needed to cause the second list box to be "re-queried" after a selection is made from the first list box.

Hopefully this will get you started.

 

[ledgerr.rob]

Mr. B

Thanks so much for your help. Your ability to just jump into a db project, synthesize what the goal is and provide a relevant solution is awesome! What you were able to provide as a solution is exactly what i was hoping for. Looking through the record source queries i understand them and the inner join that you used.

I appreciate your help. I'm sure i'll generate a few more questions as i move through this.

rob

 

[ledgerr.rob]

Mr. B~

I have been playing with the db and noticed that when i enter new data on the form using the cascading list boxes it fails to give the tblLedger.CategoryItemID field a value. I wasn't sure if i had messed something up, so i went back to your version that you provided and i don't get the values there either. Any suggestions?

 

[Mr. B]

Yes, the primary focus for what I provided was to make the second list box populate based on the selection made in the first list box. I did not set the Control Source for the first combo box.

I have now set the Control Source of the first combo box to be the "CategoryItemID" field in the "tblLedger" table. The selection made in the "lstCategory" field will now be stored in the "tblLedger" table. See the attached file.

I am now curious as to what do you plan to do with the "Items". Will there be one or more selections made from that list box or is it just designed to display the items related the selected category?

 

[ledgerr.rob]

Mr. B

What I ended up doing was creating a many-to-many relationship. My tblCategoryItemCombined is my junction table. I then populated a list box with the combination of the category and items 'pairings.' The selection in the listbox would then populate the tblLedger.CategoryItemID field. The list box has repeating category values but it is functional and does what i want.

I still have questions on calculating my balance but that's for my other post.

I appreciate your help. Definitely made me think a lot trying to explain my goals to the posting and then even more about the suggestions i received.

thanks again
rob

 

[Mr. B]

Gald to help and glad to get you thinking. LOL