Forum Discussion

Tony2021's avatar
Tony2021
Steel Contributor
Nov 17, 2022
Solved

Subform calling in StrSQL Insert Into

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

  • If [txtIDRollsPK] is the textbox (bound to IDRollsPK) on your subform:

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

3 Replies

  • arnel_gp's avatar
    arnel_gp
    Steel Contributor
    If [txtIDRollsPK] is the textbox (bound to IDRollsPK) on your subform:

    strSQL = "INSERT INTO tblFXRollsChildHist (IDRollsPK, AmountRoll) VALUES (" & [subfrmFXRollsChild].[Form]![txtIDRollsPK] & "," & Me!AmountRoll & ")"
    • Tony2021's avatar
      Tony2021
      Steel Contributor

      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. 

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor

    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. 

Resources