cascading combo boxes on a subform question

  • Thread starter Thread starter Alex Anderson
  • Start date Start date
A

Alex Anderson

Hello everyone,

I have a subform with two combo boxes and I want to cascade them but at
the same time be able to add to both combo boxes if the item I'm looking for
is not listed. Is this possible? If so, how would I something like this?

Thank you
Alex Anderson
 
By "cascading" combos, I assume you mean that the RowSource of the 2nd one
is limited, based on the value selected in the first? If so, the 2nd one's
NotInList event will fire if the entry is present in its lookup table but
currently filtered out of the RowSource, and so attempting to add such a
value would fail. Probably not a good design.

Alternative: use the DblClick event of the combos to popup a form where the
user can edit/add/delete records for the lookup tables. Then use the
AfterUpdate and AfterDelConfirm events of these popup forms to Requery the
combo on the original form so it hears about the changes.

Subforms are typically in continuous or datasheet view, so there is another
potential issue here. If the bound column of the 2nd combo is zero-width and
you filter its RowSource, the combo goes blank in the other rows of the
subform where the needed value is filtered out. You can avoid this issue if
you can use the actual display text as the primary key value of the lookup
table (instead of an autonumber).

HTH
 
Allen,

How would you go abouts setting up the tables though? Right now my
table structure for jsut the cascading effect is set up as the following: I
have two tables;

Table 1:
AttributeID =Primary key and Autonumber
Attribute = text

Table 2:
SubAttributeID = Primary key and Autonumber
AttributeID = Number
SubAttribute = Text

Relationships = AttributeID from table 1 and AttributeID from table 2 is a
1-to-many.

The problem with this type of cascading is that I have to input the
related number to AttributeID in Table 2 so when I bring up the form, it
will only display values associated to AttributeID.

I'm not sure the best way to tackle this, but ultimately I want (for
simplicity) have two combo boxes, so when someone is working with the
database if the item they are looking for is not listed in the combo box,
they can add it. Is there another way to engineer this if I have two
attributes which in my case one reflects the other depending on the choice,
but easy enough for the user themselves add values if they are not there? I
know it's hard to describe what someone else ultimately wants, so take this
with a grain of salt.

Thank you in advanced.
Alex Anderson
 
Yes, the question of the data structure is the more significant one.

To get these combos to work correctly, you need to make the *text* the
primary key:
Table1:
Attribute = Text and primary key
SortOrder = Number (lower numbers sort first).
Table2:
SubAttribute = Text and primary key
Attribute = Text, and foreign key to Table1.Attribute.
SortOrder = Number (lower numbers sort first).

In your form, you will have two combos:
- Combo1 with a RowSource of:
SELECT Attribute FROM Table1 ORDER BY SortOrder, Attribute;
- Combo2 with an initial RowSource that gives no records:
SELECT SubAttribute FROM Table2 WHERE (False);

In the Enter event of the 2nd combo, change it's RowSource like this:
Me.Combo2.RowSource = "SELECT SubAttribute FROM Table2 WHERE Attribute =
""" & Me.Combo1 & """ ORDER BY SortOrder, SubAttribute;"

The reason you can't use the AutoNumber is that there is not a separate
recordset for the rowsource of every combo on every row of the continuous
subform. Since there is only one RowSource and it is restricted to value
that match Combo1 in the current record only, the combo will go blank in all
the other rows of the subform if its bound column is zero-width. This
doesn't happen if the text is the primary key, because Access can just show
that value. And as soon as you move to another row to drop the list down,
Combo2's Enter event fires, so the RowSource is re-evaluated, so by the time
the combo drops down the list is populated with the correct values.

That works. It does not address the issue that you are storing redundant
data, i.e. the valules for both Combo1 and Combo2, when the value of Combo2
actually does define the value of Combo1 as well. Strictly, that's not
correctly normalized, but you may want to do it anyway.
 
Allen,

I don't want to ruin my welcome, but as for SortOrder in Table1 and 2 I
just made a query to sort them accending. Is that what I'm suppose to do?
And as for the foreign key on Table1.Attribute, I just made a 1 to Many
relation from Table1.Attribute to Table2.Attribute. I've tried to make a
few records in both tables however the code you presented to me is not
working. I'm not sure if I left something out or was I to fill some gaps
since you're not holding my hand completely through this excerise. If you
could give me more direction I would highly appreciated it Allen.

Thanks again for your help and the hand holding.
Alex Anderson
 
The idea of a SortOrder column is not necessary if the alphabetic sort on
the text field is all you need.

The relation based on the text field is exactly right.

If the combo is not loading for you, print the value of the string to the
immediate window:
Dim strSql As String
strSQL = "SELECT SubAttribute FROM Table2 WHERE Attribute =""" &
Me.Combo1 & """ ORDER BY SubAttribute;"
Me.Combo2.RowSource = strSql
Then when it runs open the Immediate Window (Ctrl+G) and see if the SQL
statement is correct. You could even copy this SQL statement, paste it into
SQL View of a new query, and then switch the query to design view to see
what's going on.
 
Allen,

On what event item should I put the strSql command on? I'm thinking "On
Click?" And I did what you said and if I understood you correctly, I got rid
of SortOrder from both tables. Once I get this code working, what is the
next step to bring up a sub form to add items to the combo boxes (described
in your original post) if the item is not listed? You already told me, and
I know but that's a little over my head.

Thank you
Alex Anderson
 
Allen,

Great...that worked! Now, to add items to both combo boxes based off
their content, would making a maintance form be appropriate or what?

Thank you
Alex Anderson
 
What we normally do is use the DblClick event of the combo to open the form
where the user can enter/edit/delete the lookup items for the combo. Users
quickly come to understand this as a kind of "drill-down" to the data in the
list.

Use the AfterDelConfirm and AfterUpdate events of the lookup form to
Requery the combo on the original form so it gets to hear about the changes.
 
Allen,

Opps, spoke to soon. Well, the Sql statement is working, but it's
listing everything under the table tblsubattribute, it's not filtering out
specifically. Here's the code I'm using under On Enter in combo2:
Dim strSql As String
strSql = "SELECT SubAttribute FROM tblsubAttribute WHERE Attribute ="""
& Me.Combo1 & """ ORDER BY SubAttribute;"
Me.Combo2.RowSource = strSql

Thank you
Alex Anderson
 
Where have you put this code? Into the Enter event of Combo2?

What is the value of Combo1 at the time this runs? You could find out by
adding the line:
Debug.Print strSql
and then looking in the Immediate Window (Ctrl+G) after it runs.

What data type is the Attribute field if you open tblsubAttribute in design
view? Your quotes are correct if it is a Text field, but remove the extra
quotes if it is a Number field.

What is the bound column of Combo1? Is this the correct field to be matching
to Attribute?
 
Allen,

Scratch that... It works now. I just started completely over and bam!
it works. Okay...now that it's working. You mentioned that adding items to
the combo boxes is a "drill-down" process. How do set up the form to allow
this drill-down? I mean, when editing the Attribute table, is that what you
mean when you add new data? Could you give me a few examples that would be
feasible in this situation?

Thank you
Alex Anderson
 
Just use the DblClick event procedure of the combo to OpenForm, e.g.:
Private Sub Combo1_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmAttribute"
End Sub

Then use the AfterUpdate event of that form to requery the original combo:
Private Sub Form_AfterUpdate()
Forms!Form1!Combo1.Requery
End Sub
Do the same in the form's AfterDelConfirm event. There's a bit more to it to
ensure the original form is still open and Undo the combo if it has an
uncommitted value, but that's the idea.
 
Allen,

I understood that part, however where I'm a little lost on how to
construct the form where the user will be inputting the updated attributes.
From my understanding, I cannot use a subdatasheet in a form? Instead of a
form, should I use a query or table? I'm thinking a subdatasheet would be
the easiest way to accomplish this? Any ideas?

Thank you
Alex Anderson
 
Subdatasheets are not very useful, as they lack the events.

Just create a small form bound to the Attribute table, and OpenForm.
 
Allen,

I did exactly what you said, and I know I'm leaving something out
because all the form does when it does open, is just a blank canvas. I had
some success with subdatasheet in a subform. I just bounded Table.Attribute
to the subform, and now I have an easy way for the user (and for myself
design wise) to enter new items to the combo boxes. Problem like you said,
there are no events. So when you said "Just create a small form and bound
it to the Attribute table" was there more to this, because I took it
literal?

Thank you
Alex Anderson
 
You created a form, and it came up completely blank? That indicates that
there are no records to load AND no new records can be added.

Make sure the form is bound to the table (Attribute), not a query that could
be read-only. Make sure this form's AllowAdditions property is set to Yes.
If you have secured the database, make sure adequate permissions are given
to add records to the table.

Some things (like fields) have an Attributes property. I doubt that the name
is the issue, but you could try this with a table of a different name and
see if it works.

Other than that, a corruption is possible, because what you are trying to do
is such a straight-forward thing.
 
Back
Top