Nov 17 2022 02:12 PM
Hello Experts,
I have an unbound form frmFXTracker.
I have a subform on this form subfrmFXRollsChild
I need to reference a field (IDRollsPK)in this subform but I am sure I have it wrong (bold)
I get an error message of:
How can I reference the field in the strSQL as below?
I am only showing a part of the code:
Dim strSQL As String
strSQL = "INSERT INTO tblFXRollsChildHist (IDRollsPK, AmountRoll) VALUES (" & [subfrmFXRollsChild].[Form]![txtIDRollsPK]!IDRollsPK & "," & Me!AmountRoll & ")"
Nov 17 2022 06:18 PM
@Tony2021 You have to mention parent form name then use subform container. Try below-
strSQL = "INSERT INTO tblFXRollsChildHist (IDRollsPK, AmountRoll) VALUES (" & [ParentFormName]![subfrmFXRollsChild].[Form]![txtIDRollsPK]![IDRollsPK] & "," & Me!AmountRoll & ")"
If above do not work then please attach a sample db file here.
Nov 17 2022 07:13 PM
SolutionNov 18 2022 05:30 AM
well it turns out that I had a mistake (my field in the target table to 'insert into' was IDRollsFK not IDRollsPK) and once I fixed that I then reverted back to what I originally tried (no subform calling) and it inserted to my surprise:
strSQL = "INSERT INTO tblFXRollsChildHist (IDRollsfK, AmountHist) VALUES (" & [txtIDRollsPK] & "," & [AmountRoll] & ")"
I also dont need Me! and I always thought I needed Me! It works either way though I confirmed in my testing.
It looks like I am good now but adding extra comments in case others read:
I also tried referencing the subform but I still got the runtime error 2465 message (above pic in original qstn): note that [amountroll] (I didnt name it as txtamountroll and I should have and since I have code attached to it I dont want to change it) field is also in the subform
strSQL = "INSERT INTO tblFXRollsChildHist (IDRollsPK, AmountHist) VALUES (" & [subfrmFXRollsChild].[Form]![txtIDRollsPK] & "," & [subfrmFXRollsChild].[Form]![AmountRoll] & ")"
I am surprised the code fires and inserts without referencing the subform. I believe in most cases when dealing with an unbound form with a subform and if you need to reference a field on the subform you need to use [subformname].[Form]![name of field] (and sometimes add the parent name of form in front of subform).
Maybe I dont need to reference the subform since the subform is in the footer and is not in the detail section. I confirm that the form frmFXTracker is unbound though.
harun: I also tried to reference the parent form (frmFXTracker) but I still got the runtime error:
strSQL = "INSERT INTO tblFXRollsChildHist (IDRollsfK, AmountHist) VALUES (" & [frmFXTracker].[subfrmFXRollsChild].[Form]![txtIDRollsPK].[IDRollsPK] & "," & [frmFXTracker].[subfrmFXRollsChild].[Form]![AmountRoll].[AmountRoll] & ")"
Anyways, it looks like its inserting as it should. thanks again for the help guys. Still a little confused why I dont need to reference the subform. have a good weekend.
Nov 17 2022 07:13 PM
Solution