Filter a Combo box on a multi row form

[cawkwell]

I have a form which loads a recordset.
Within each row I want a combo box which is loaded by values from another table filtered by a value from a column on that row.

I have set the Row Source of the combo box to

SELECT tblTempVIC.icID, tblTempVIC.description FROM tblTempVIC WHERE (((tblTempVIC.part_id)=Forms!frmPartInput1!Part_id)) ;

expecting it to pull the Part_id from the current row.
However, every combo box is filtered by the value of the first row.

[PianoMan64]

I have a form which loads a recordset.
Within each row I want a combo box which is loaded by values from another table filtered by a value from a column on that row.

I have set the Row Source of the combo box to

SELECT tblTempVIC.icID, tblTempVIC.description FROM tblTempVIC WHERE (((tblTempVIC.part_id)=Forms!frmPartInput1!Part_id)) ;

expecting it to pull the Part_id from the current row.
However, every combo box is filtered by the value of the first row.

Because you have to base it on what is currently on the form, and not what the bound control on the form is currently pointing too.

It may look like you're doing that already, but the problem comes in because you reference the bound Record value of the control and not what the value on the form that you're editing. It may be different because you're making changes to create a new record.

If you set the .RowSource in VBA just simple replace the Forms!frm..... with me.part_id

example:

me.ComboBoxControlname.RowSource = "SELECT tblTempVIC.icID, tblTempVIC.description FROM tblTempVIC WHERE (((tblTempVIC.part_id)=" & me.part_ID & "));"
 

If you're simply setting that in the Combobox control itself, you're going to need to set that in code so it knows how to populate.

So on the previous control before it gets to the combo box, you need to include the following code section:

 
     me.comboboxcontrolname.RowSource = "SELECT tbTempVIC.icID, tblTempVIC.Description FROM tblTempVIC WHERE tblTempVIC.Part_ID=" & me.Part_ID
     me.comboboxcontrolname.requery
 

You'll put that on the event property of onLostFocus.

Hope that helps,

Joe P.

If you need more detailed answer to this, please let me know.

[cawkwell]

Because you have to base it on what is currently on the form, and not what the bound control on the form is currently pointing too.

It may look like you're doing that already, but the problem comes in because you reference the bound Record value of the control and not what the value on the form that you're editing. It may be different because you're making changes to create a new record.

If you set the .RowSource in VBA just simple replace the Forms!frm..... with me.part_id

example:

me.ComboBoxControlname.RowSource = "SELECT tblTempVIC.icID, tblTempVIC.description FROM tblTempVIC WHERE (((tblTempVIC.part_id)=" & me.part_ID & "));"
 

If you're simply setting that in the Combobox control itself, you're going to need to set that in code so it knows how to populate.

So on the previous control before it gets to the combo box, you need to include the following code section:

 
     me.comboboxcontrolname.RowSource = "SELECT tbTempVIC.icID, tblTempVIC.Description FROM tblTempVIC WHERE tblTempVIC.Part_ID=" & me.Part_ID
     me.comboboxcontrolname.requery
 

You'll put that on the event property of onLostFocus.

Hope that helps,

Joe P.

If you need more detailed answer to this, please let me know.

Thanks for the reply.

That works and I now understand what's happening!