Relational Combo Boxes not Working

[Tophan]

Good Morning,

I would really appreciate assistance as I have been trying for over 2 weeks to get the relational combo boxes to work.

I am trying to set up a Material Requisition DB. In the details section of the subform I tried to create a category combo box, which when a selection is made, the Product combo box would requery and show only those items under this category.

However, I have tried several methods and it is just not working. I have attached the database as I am at my wits-end and really need to get this DB set up.

You will see that I have copies of the tblMaterialRequisition and tblMaterialRequisitionDtls as I was trying several times to get the combos to work.

Thanks in advance for any assistance/advice provided.

 

[moke123]

your cboCategories should have

Private Sub cboCategories_AfterUpdate()
Me.cboProduct.Requery
End Sub

the rowsource of cboProducts should be

SELECT tblProducts.ProductName
FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID
WHERE (((tblCategories.CategoryID)=[Forms]![frmMaterialRequisitionDtls]![cboCategories]));

 

[Pat Hartman]

The simplest method requires only one line of code. The query for the child combo, uses a where clause that references the parent combo.

Select ... From ... Where SomeField = Forms!yourform!cboParent

Then in the click event of the parent combo, you requery the child combo.

Me.cboChild.Requery.

 

[Pat Hartman]

Looks like Moke beat me to it

 

[CJ_London]

that method works in a single form, but not in a continuous or datasheet form

for a continuous form the code is a bit more complicated - see this link

enabling cascading comboboxes in datasheet or continuous form

Having been asked about this recently, I reinvented some code I developed to work with datasheets and which also works with continuous and single forms. The attachment was developed in 2010 but saved as 2003. The db demonstrated two techniques for using cascading comboboxes. Technique 1 is...

www.access-programmers.co.uk

 

[Tophan]

I don't understand why but it's just not working. This time the Enter Parameter Value keeps popping up everytime I open the form

 

[Tophan]

I'm starting to think there is a problem with my Access program itself. Another database I have been using for over a year now, started giving trouble after an update in April.

 

[Tophan]

If I open the subform frmMaterialRequisitionDtls alone, the combo boxes work. But when I open the frmMaterialRequisitions, the combo boxes do not work

 

[MajP]

You cannot reference a subform by its name. You have to reference it through its parent. The reason is a subform can be opened many times.
I would do it in code, but you could try
[Forms]![YourMainFormName]![TheNameOfTheSubformControl].[Form]![cboCategories]
However this is problematic because of you cannot test the subform by itself

I would change the sql in code. Way easier and you have to return it to unfiltered anyways once you exit.

Private Sub cboProduct_Enter()
  Dim strSql As String
  strSql = "SELECT tblProducts.ProductName FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID WHERE "
  strSql = strSql & "tblCategories.CategoryID = " & Me.cboCategories
  cboProduct.RowSource = strSql
End Sub

Private Sub cboProduct_Exit(Cancel As Integer)
  Dim strSql As String
  strSql = "SELECT tblProducts.ProductName FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID"
  cboProduct.RowSource = strSql
End Sub

 

[tvanstiphout]

For a subform in Continuous Forms mode, I would refer you to the Northwind 2.0 Developer Edition (File > New). Cascading comboboxes is implemented in the Order Details subform, and the help page has documentation about the technique that was used.

 

[Tophan]

For a subform in Continuous Forms mode, I would refer you to the Northwind 2.0 Developer Edition (File > New). Cascading comboboxes is implemented in the Order Details subform, and the help page has documentation about the technique that was used.

Hi. I did. Still didn't work.

 

[MajP]

Did you try my code. Works for me.

 

[Tophan]

You cannot reference a subform by its name. You have to reference it through its parent. The reason is a subform can be opened many times.
I would do it in code, but you could try
[Forms]![YourMainFormName]![TheNameOfTheSubformControl].[Form]![cboCategories]
However this is problematic because of you cannot test the subform by itself

I would change the sql in code. Way easier and you have to return it to unfiltered anyways once you exit.

Private Sub cboProduct_Enter()
  Dim strSql As String
  strSql = "SELECT tblProducts.ProductName FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID WHERE "
  strSql = strSql & "tblCategories.CategoryID = " & Me.cboCategories
  cboProduct.RowSource = strSql
End Sub

Private Sub cboProduct_Exit(Cancel As Integer)
  Dim strSql As String
  strSql = "SELECT tblProducts.ProductName FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID"
  cboProduct.RowSource = strSql
End Sub

This works...thank you so much

 

[Tophan]

Did you try my code. Works for me.

Yes...thank you. This seems to have solved my problem

 

[MajP]

In a continuous form you want to apply the filter when you Enter the second combo and take the filter off when you Exit. If not what happens is in a continuous form all controls get the same rowsource, That means if you do not remove the filter when you exit all combos get repainted using the current filtered rowsource. Anything that is not included in the list will not display. So some combos will appear empty when in fact they are not.

 

[bob fitz]

The Row Source property of the Product combo should be:

SELECT tblProducts.ProductName FROM tblCategories INNER JOIN tblProducts ON tblCategories.CategoryID = tblProducts.CategoryID WHERE (((tblCategories.CategoryID)=[Forms]![frmMaterialRequisition]![frmMaterialRequisitionDtls].[Form]![cboCategories]));

The following code should be in the GotFocus event of the Product combo:

Me.ActiveControl.Requery

 

[June7]

Why aren't you including ProductID in combobox RowSource? Why join to tblCategories?
When controls are on same form, don't need full form path reference for SQL statement in RowSource, instead of referencing a query object:

SELECT ProductID, ProductName FROM tblProducts WHERE CategoryID=[cboCategories];

 

[Tophan]

Ok...so I'm still getting a couple problems.

I've tried both @MajP and @bob fitz methods and getting the same error message when I try to tab to a new record in the subform. A message box pops up which states You cannot add or change a record because a related record is required in table 'tblproducts'. Also, I noticed when i did select a product the same product shows in the line below.

I then tried @June7 method and I am getting the error message that I tried entering text in a numeric field.

 

[MajP]

That is a problem with your form not saving a related record. Not related to the cmbo box issue.

 

[June7]

With my SQL, change combobox properties:

ColumnCount: 2
ColumnWidths: 0";1.0"

Your original RowSource was attempting to save product name to ProductID field. Need to save ProductID.