SOLVED

Subform calling in StrSQL Insert Into

Super Contributor

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:

Tony2021_0-1668722965537.png

 

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 & ")"

3 Replies

@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. 

best response confirmed by Tony2021 (Super Contributor)
Solution
If [txtIDRollsPK] is the textbox (bound to IDRollsPK) on your subform:

strSQL = "INSERT INTO tblFXRollsChildHist (IDRollsPK, AmountRoll) VALUES (" & [subfrmFXRollsChild].[Form]![txtIDRollsPK] & "," & Me!AmountRoll & ")"

@arnel_gp 

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.