Refresh subform combobox

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
How? I've tried

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

none work

Lynn Trapp said:
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


AdmSteck said:
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?
Click to expand...
Click to expand...
 
Ok, now I got it:

Me.PinTransactions_subform.Controls!PinID.Requery

AdmSteck said:
How? I've tried

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

none work

Lynn Trapp said:
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


AdmSteck said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top