Cascading Combo Box Items

[gsdcrazy]

Gurus,

I am very new at Access Programming and really would appreciate some help. I have spent hours going through threads and just can seem to hit on the right key words.

My situation is that I have a table that will be use to create a combo box. The table (among others) has two columns: FundName and FundLevel. For example,

FUNDNAME FUNDLEVEL
Income 0
Tithes & Offerings 1
Building Rental 1
Interest Income 1
Expenses 0
Outside Giving 1
Co-op 2
Clovis Vanover 3
Salaries & Benefits 1
Pastor's Salary 2

I want to come up with a Combo Box (or maybe it should be a List and that is what I am doing wrong) that looks like:

Income
Tithes & Offerings
Building Rental
Interest Income
Expenses
Outside Giving
Co-op
Clovis Vanover
Salaries & Benefits
Pastor's Salary

I have been trying to perform the indentation with a procedure off the "Enter" event. (I was planning on getting rid of the spaces added in the "AfterUpdate" event.) Is there a better way? Are there formatting commands I can place in the SQL that will provide the indentation? I can still catch it in the "AfterUpdate" event to get rid of the spaces for storing in the destination table (not the Funds table). Here is the code I have been trying, but nothing is showing in the Combo Box.

Private Sub DefaultFundName_Enter()

Dim iCount As Integer
Dim strDesignatedFundName As String
Dim strFirstTime As String

strFirstTime = "N"
Set dbs = CurrentDb()

Set rst = dbs.openrecordset("SELECT DesignatedFundName " _
& " ,DesignatedFundLevel " _
& " FROM DesignatedFunds " _
& " ORDER BY DesignatedFundSequence &quot

rst.MoveFirst

Do Until rst.EOF
iCount = 1
strDesignatedFundName = rst!DesignatedFundName
While iCount < rst!DesignatedFundLevel
strDesignatedFundName = &quot; &quot; & strDesignatedFundName
iCount = iCount + 1
Wend
If strfirstime = &quot;N&quot; Then
Me.DefaultFundName.RowSource = strDesignatedFundName
Else
Me.DefaultFundName.RowSource = Me.DefaultFundName.RowSource _
& &quot;;&quot; _
& strDesignatedFundName
End If
rst.MoveNext
Loop

End Sub

Any help would be greatly appreciated.

Thanks,
GSDCazy

 

[thornmastr]

Actually, you really have two combo boxes. The first contains two rows, ie,
Income
Expenses.

Your user clicks income and the second combo box shows all your income categories. When they click expenses, the second box gets all the expense categories.

Clicking on any item in the 2nd combo box will do whatever accounting functionality you wish to implement.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[MasterPO]

I've used a similar technique where I put the main choices into a Option Group (i.e. Income, Expense). One is the default, and the associated combo box for detail choices has it's recordsource set to the details for the default main choice. When the user selects the other choice in the Option Group, I reset the recordsource in the combobox. From a user perspective it works well when the main choices are limited to 4 or less. After that, I go to a combo box for the main choices as well.

 

[FEDSEC]

Just curious, why would you have income and expences in the same table?

 

[thornmastr]

Value list. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[gsdcrazy]

thornmastr, MasterPO, and FEDSEC,

Thanks for the ideas. I did not explain my need very well. I have income and expense funds in the same list because you either select one or the other, not both. I am not sure that two combo boxes will do me any good since I have only one field to populate and &quot;Income&quot; or &quot;Expense&quot; is a possible selection. Also, I have upto four levels now (Expenses, Outside Giving, Co-op, Clovis Vanover) and the user can add additional levels at will. Using a fixed definition will not provide the effect desired. I will look into the &quot;value list&quot; and see what the differences are with the Combo Box.

I thought of something else to try as well. I am not sure how, but I think it is possible to create a combo box that displays one column and uses a second to fill the field in the form. Assuming that is true, I will change the Funds table to include a display field that provides my indentations as desired and display that field in the combo box, but use the standard field for populating the field in the form that is related to the table the form is based upon.

Again, thanks for the ideas and help. I'll post whatever the final solution is.

Thanks,
gsdcrazy

 

[gsdcrazy]

Everyone,

I have a solution that works for me. Due to the other features that I need to provide, this list of hierarchical funds need to be listed in one table and this combo box feature is only one small part of what I need. Anyway...

I have nine columns in the Funds table; however, only three are of interest for the combo box: FundName, FundLevel, and DisplayFundName (the new one I added for this feature). FundName is self-explanitory. FundLevel is calculated to let me know where in the heirarchy the fund resides (as in the original question above). I added logic to whenever a new Fund was added or the ParentFund was changed to create/replace the DisplayFundName. The logic looks like this:


Dim iCount As Integer
Dim strDisplayFundName As String

strDisplayFundName = Me.DesignatedFundName
While iCount < Me.DesignatedFundLevel
strDisplayFundName = &quot; &quot; & strDisplayFundName
iCount = iCount + 1
Wend

Me.DisplayFundName = strDisplayFundName

Then wherever I want to have a combo box of funds, I defined a SQL statement with two columns (the Fund and the DisplayFundName). The Column Width was defined as 2.0938&quot;;0&quot; to display the indented fund names and not the real fund names. The Bound Column was set to &quot;2&quot; for the real fund name to populate the source record field.

It works great!!

Thanks for all you help,
gsdcrazy