Combos working on stand alone form but not in datasheet in TabCtl

[projectpupil7]

Hi All, i have a form with two cascading combo's that work when the form is opened as a single form however when i place the form on Page 3 of a TabCtl it fails to cascade the correct values. I have include information below explaining the set up.

Could i have some help to get this working please.


frmSiteDetails > TabCtl33 > Page3 > frm ConditionDetails subform with Combo's 1 & 2

Combo 1 = [ElementGroupName]


SELECT ElementGroupID, ElementGroupName FROM tblElementGroup; has Column Count of 2 and Bound Column 2


Combo 2 = [Element]

qrySubElements has SQL:

SELECT tblSubElement.SubElementPKID, tblSubElement.SubElement, tblElementGroup.ElementGroupName, tblSubElement.SubElementID
FROM tblElementGroup INNER JOIN tblSubElement ON tblElementGroup.[ElementGroupID] = tblSubElement.[SubElementID]
WHERE (((tblElementGroup.ElementGroupName)=[Forms]![frmConditionDetails]![ElementGroupName]));

has Column Count of 4 and Bound Column 2

qrySubElements for [Element]has four fields:


[SubElementsPKID]
[SubElement]
[ElementGroupName] Pointing to [Forms]![frmConditionDetails]![ElementGroupName]
[SubElementID]

In the AfterUpdate and OnChange events for [ElementGroupName] i have this VBA

Private Sub ElementGroupName_AfterUpdate()

Me.ELEMENT.Requery

End Sub

Private Sub ElementGroupName_Change()

Me.ELEMENT.Value = ""

End Sub

[June7]

Both combos are on the same form?

Try putting the SQL statement directly in the RowSource property and remove the form prefix:

SELECT SubElementPKID, SubElement, ElementGroupName, SubElementID
FROM tblElementGroup INNER JOIN tblSubElement ON tblElementGroup.[ElementGroupID] = tblSubElement.[SubElementID]
WHERE (((tblElementGroup.ElementGroupName)=[ElementGroupName]));

[John_G]

If I understand you correctly, you are now using the form as a subform on a tab control of another form?

A subform cannot be referenced a part of the Forms!... collection (because it isn't part of it).

Instead of [Forms]![frmConditionDetails]![ElementGroupName] you could write Me![ElementGroupName], but I'm not sure you could use it in the SQL as you have shown. You might have to build a string with the SQL and include explicit values in the string, then requesry the second combo box:


SQL = "SELECT tblSubElement.SubElementPKID, tblSubElement.SubElement, tblElementGroup.ElementGroupName, tblSubElement.SubElementID
FROM tblElementGroup INNER JOIN tblSubElement ON tblElementGroup.[ElementGroupID] = tblSubElement.[SubElementID]
WHERE tblElementGroup.ElementGroupName= '" & me![ElementGroupName] & "'"

me![Element].Rowsource = SQL
me![element].Requery

[projectpupil7]

Hi All, have tried your suggestions but am obviously not getting this right. I am getting a parameter query input request box ---- not correct eh!!.

I am attaching the db in the hope you can see the problem better. apologies in advance if i have missed anything.

[June7]

Primary key and related foreign key fields must be the same data type. If primary key is autonumber then foreign key must be number.

The field ElementGroupName in tblCondition is text type and needs to be number type because it is saving the ElementGroupID primary key value from tblElementGroup as foreign key. Same goes for Element field in tblCondition which is saving SubElementPKID from tblSubElement.

Setting lookups in table is not advised unless you really understand what is happening with them. Review: http://access.mvps.org/access/lookupfields.htm

RowSource for Element combobox:
SELECT SubElementPKID, SubElement FROM tblSubElement WHERE SubElementID=[ElementGroupName];

[projectpupil7]

I have read the article and for me it begs the question of how to handle the lookup fields on the subform. I would like to keep them on the form as they are central to the collection of information. Are they best handled by code and if so how do I do that.

Any suggestions and /or an example would help.

Thanks!

[June7]

I advised not setting lookup in table. Build comboboxes and listboxes on form.

Did you make the suggested adjustments?

[projectpupil7]

OK I will try building the cb's directly on the form. I did try your adjustments but the Element field did not show any values in the drop down. The drop down was empty.

[June7]

Okay, I was mistaken. You had the ElementGroupName combobox set to save the descriptive name, not the ElementGroupID. If you want to save descriptive text then there is no need to have primary key ID fields. So what do you want?

In addition to earlier suggestions, change the BoundColumn property to 1.

[projectpupil7]

What I need is to have the combos working on the subform and store the descriptive text. What I have now is combo's built on the subform.

cboElementGroupName has 2 columns with the BoundColumn as 1 and sql SELECT [tblElementGroup].[ElementGroupID], [tblElementGroup].[ElementGroupName] FROM [tblElementGroup];

cboElement has 3 columns and BoundColumn as 1 and sql SELECT SubElementPKID, SubElement FROM tblSubElement WHERE SubElementID=[ElementGroupName];

Code to requery cboElement

Private Sub cboElementGroupName_AfterUpdate()
Me.cboElement.Requery
End Sub
Private Sub cboElementGroupName_Change()
Me.cboElement.Value = ""
End Sub


COMBO's don't cascade at the moment.

[projectpupil7]

Sorry cboElement sql should be SELECT SubElementPKID, SubElement FROM tblSubElement WHERE SubElementID=[ElementGroupName];

Combo's do now cascade but need to store the descriptive text in the table.

[June7]

They work for me after the suggested edits. But the arrangement will save the ID values, not text. If you want descriptive text then eliminate the ID fields.

[projectpupil7]

I have eliminated the ID in [ElementGroupName] as instructed and now have SELECT[tblElementGroup].[ElementGroupName] FROM tblElementGroup; for [ElementGroupName]. The cb displays the list correctly.

For cboElement i have eliminated the ID's as instructed and now have SELECT SubElement FROM tblSubElement WHERE SubElementID=[ElementGroupName]; this doesn't populate the cb.

[June7]

This has grown into a nasty conundrum. The second combobox is still dependent on the ID to filter the RowSource. The combobox RowSource SQL statement must use the value of the combobox as filter parameter. If you save the descriptive text, that is the value of combobox, not the ID. An SQL statement cannot refer to column index of RowSource which means the ID could not even be in another column and be retrieved as filter parameter.

Now I understand why you used a query that joined the tables as the RowSource for second combobox. So let's backup. The only thing that needed to be changed for the subform to not trigger popup was to remove reference to form prefix:

SELECT tblSubElement.SubElementPKID, tblSubElement.SubElement, tblElementGroup.ElementGroupName, tblSubElement.SubElementID FROM tblElementGroup INNER JOIN tblSubElement ON tblElementGroup.ElementGroupID = tblSubElement.SubElementID WHERE (((tblElementGroup.ElementGroupName)=[ElementGroupName]));

Sorry for the misdirect. I thought you had made a common mistake in the use of lookups. It is a bit unusual to save the text instead of the ID PK.

However, to get the Requery to work I changed combobox names to: cbxElementGroupName and cbxElement. Then RowSource:
SELECT tblSubElement.SubElementPKID, tblSubElement.SubElement, tblElementGroup.ElementGroupName, tblSubElement.SubElementID FROM tblElementGroup INNER JOIN tblSubElement ON tblElementGroup.ElementGroupID = tblSubElement.SubElementID WHERE (((tblElementGroup.ElementGroupName)=[cbxElementGroupName]));

Had to then change the VBA code to reflect the name changes in the procedures.

I recommend the GotFocus event of the second combobox for the Requery.

Private Sub cbxElement_GotFocus()
Me.cbxElement.Requery
End Sub

[projectpupil7]

This is truly a right nasty conundrum indeed, still struggling a bit understanding the mechanics of it. I definitely WILL try your latest suggestions but in the meantime could i respectfully ask if you could forward the working example. Doing this will give me a chance to work the problem backwards to understand it. Am very grateful for you help on this.