Refresh subform combobox

[Guest]

I have a form for recording work done on a production line and a subform for
recording the supplies used on that line. There are 4 underlying tables
being used:

[LineActivity] with fields: ID, PartID, Who
[Parts] with fields: ID, PartNumber, LineCode
[Pins] with fields: ID, LineCode, PinNumber
[PinTransactions] with fields: ID, PinID, Used

On my LineActivity table I have a combobox bound to PartID and with [Parts]
as the record source. I also have a combobox on my PinTransactions subform
bound to PinID and with [Pins] as the record source.

I would like to be able to filter the record source on my subform to only
show the pins that have the same LineCode as the PartID combobox on the
parent form. I have gotten it to work using:
SELECT [Pins].[ID], [Pins].[Number], [Pins].[LineCode] FROM Pins WHERE
((([Pins].[LineCode])=(DLookUp("[LineCode]","Parts","[ID] = " &
[forms]![LineActivity].[PartID]))));
but I cannot get it to update when the PartID combobox is changed. Is
there a better way of doing this?

 

[Lynn Trapp]

Requery the Pins combobox in the change event of the Parts combo box.

 

[Guest]

How? I've tried

Me.PinTransactions_subform.requery
forms![PinTransactions subform].requery
forms![PinTransactions subform].[PinID].requery

none work

Requery the Pins combobox in the change event of the Parts combo box.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

I have a form for recording work done on a production line and a subform for
recording the supplies used on that line. There are 4 underlying tables
being used:

[LineActivity] with fields: ID, PartID, Who
[Parts] with fields: ID, PartNumber, LineCode
[Pins] with fields: ID, LineCode, PinNumber
[PinTransactions] with fields: ID, PinID, Used

On my LineActivity table I have a combobox bound to PartID and with [Parts]
as the record source. I also have a combobox on my PinTransactions subform
bound to PinID and with [Pins] as the record source.

I would like to be able to filter the record source on my subform to only
show the pins that have the same LineCode as the PartID combobox on the
parent form. I have gotten it to work using:
SELECT [Pins].[ID], [Pins].[Number], [Pins].[LineCode] FROM Pins WHERE
((([Pins].[LineCode])=(DLookUp("[LineCode]","Parts","[ID] = " &
[forms]![LineActivity].[PartID]))));
but I cannot get it to update when the PartID combobox is changed. Is
there a better way of doing this?

 

[Guest]

Ok, now I got it:

Me.PinTransactions_subform.Controls!PinID.Requery

How? I've tried

Me.PinTransactions_subform.requery
forms![PinTransactions subform].requery
forms![PinTransactions subform].[PinID].requery

none work

Requery the Pins combobox in the change event of the Parts combo box.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

I have a form for recording work done on a production line and a subform for
recording the supplies used on that line. There are 4 underlying tables
being used:

[LineActivity] with fields: ID, PartID, Who
[Parts] with fields: ID, PartNumber, LineCode
[Pins] with fields: ID, LineCode, PinNumber
[PinTransactions] with fields: ID, PinID, Used

On my LineActivity table I have a combobox bound to PartID and with [Parts]
as the record source. I also have a combobox on my PinTransactions subform
bound to PinID and with [Pins] as the record source.

I would like to be able to filter the record source on my subform to only
show the pins that have the same LineCode as the PartID combobox on the
parent form. I have gotten it to work using:
SELECT [Pins].[ID], [Pins].[Number], [Pins].[LineCode] FROM Pins WHERE
((([Pins].[LineCode])=(DLookUp("[LineCode]","Parts","[ID] = " &
[forms]![LineActivity].[PartID]))));
but I cannot get it to update when the PartID combobox is changed. Is
there a better way of doing this?