Categories
dlookup events flattening forms hierarchy MS Access

Flattening a hierarchy in an editor

In one of projects I had to import in Excel spreadsheets into an Access database. This database also had to maintain the structure of the files, as the on screen representation was to be identical. The file was sectioned and this structure was stored in a hierarchy in the database. I had to supply a form to the users so they could edit the file (and screen) layout.

One can show this in flattened screen but how do you edit this nicely? I hope to show in this post a nice technique to edit tables which refer to hierarchical information using a single form.

The Tables
First lets build a simple hierarchy. I’ll keep it simple and just stick to a two level hierarchy.
And now the table to store the referencing information which has fields to reference the top and middle values
The Form
I will build the from on the tblSections which is essentially the configuration table. I use the form wizard to create the intial form in the tabular style.

Now setup the rowsource for each combo appropriately:



select id,top from tblTop

and

select id,middle from tblMiddle

Now if you run this form as is you will notice that the sectionmid combo is not filtering based on the sectiontop combo. So lets sort that.

We will need to add the following event code to the form.



Private Sub SectionTop_Change()
Dim sSQL As String
sSQL = "select id,middle from tblmiddle where TOP_ID=" & Me.SectionTop
Me.Combo7.RowSource = sSQL
Me.SectionMid = Null
End Sub

Private Sub Combo7_GotFocus()
Dim sSQL As String
sSQL = "select id,middle from tblmiddle where TOP_ID=" & Me.SectionTop
Me.Combo7.RowSource = sSQL
End Sub

Private Sub Form_Current()
Dim sSQL As String
sSQL = "select id,middle from tblmiddle where TOP_ID=" & Me.SectionTop
Me.Combo7.RowSource = sSQL
End Sub

(you would probably do a better job by placing this into a single sub and invoking it from the events)

And now run the form again. What you will notice is that when you run the form all the sectionmid combos will change and not just the single one you are using. You can notice this when the combo blanks out when you change rows.
The trick to this is the place a textbox control on top of the combo box. And have this combo use a DLookup to display the proper value. Place the text box on top of the text area and ensure you move it to the top. Change the border to transparent.

Set the DLookup formula to



=IIf(IsNull([SectionMid]),"",DLookUp("[middle]","[tblMiddle]","ID=" & [SectionMid]))

The IIF ensures that and #error is not displayed when the comboboxes are null.

Conclusion
You can of course extend this by adding in more combos and textboxes. Just modify the code appropriately and add in events to enure changes are cascaded.

That’s all there is to it. I hopes this provides you some inspiration.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.