Insert multiple groups of Text fields in table

[croatic]

Hi what i´m trying to do ist to make a for loop to insert multiple text fields in on table.

depending on the counter (Zaehler) it should insert that representing text field. for example if Zeahler is 1 it should input whatever is inside the text field KVP_Kfm1 if its 2 then it should input the textfield KVP_Kfm2 and so on. here is the code that i'm trying to work but sadly it wont.I believe that the mistake is that my syntax is wrong but i cant figure out what is right.

For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
    SQL = "INSERT INTO tblLifecycle_Projektion(ID_Berichtstand, KVP_Kfm, KV P_technisch, AeJ, MoPf, skAe, MiBst, Sonstige_Effekte, " & _
          "KVP_technisch_FTR, KVP_Kfm_FTR, AeJ_FTR, MoPf_FTR, sk Ae_FTR, Sonstige_Effekte_FTR, Jahr) VALUES " & _
          "('" & newRow & "', forms!frmCMP![txtKVP_kfm_IST & Zaehler], forms!frmCMP![txtKVP_tech_IST & Zaehler], forms!frmCMP![txtAJ_IST & Zaehler], " & _
          "forms!frmCMP![txtMoPf_IST & Zaehler], forms!frmCMP![txtskae_IST & Zaehler], forms!frmCMP![txtMiBst_IST]," & _
          "forms!frmCMP![txtSonst_Effekte_IST & Zaehler],forms!frmCMP![txtKVP_fkm_FTR & Zaehler]," & _
          "forms!frmCMP![txtKVP_techn_FTR & Zaehler],forms!frmCMP![txtAJ_FTR & Zaehler],forms!frmCMP![txtMoPf_FTR & Zaehler]," & _
          "forms!frmCMP![txtskae_FTR & Zaehler], forms!frmCMP![txtSonst_Effekte_FTR & Zaehler',forms!frmCMP![txtAnlauf & Zaehler])"
    DoCmd.RunSQL SQL
Next Zaehler

thx in advance

 

[richsql]

Hi

Its a little tricky to read but you are building a string that you are going to execute. All of the values you are adding to the string need to be evaluated before you can run it.

So the line

"forms!frmCMP![txtKVP_kfm_IST & Zaehler],forms!frmCMP![txtKVP_tech_IST

should not be in quotes only the part gluing the value from the form to the SQL string should be in quotes

forms!frmCMP![txtKVP_kfm_IST] & Zaehler & "," & forms!frmCMP![txtKVP_tech_IST

Hopefully that should get you a step closer

 

[pr2-eugin]

Hello coratic, Welcome to AWF

You need to concatenate the values like the way you have newRow. The common way to do is to make sure,

• String is enclosed within ' quotes or " quotes, eg. 'Sally' or """Sally"""
• Number need no delimiter
• Dates need to be enclosed between #. eg. #17 Feb 2015# or #02/17/2015#

Also make sure the field names that have special characters enclosed in square brackets. [Field Name] or [Field#]

 

[croatic]

Thx guys for the quick reply. i figured it out now. I know that my code is far from pretty but it works now.

For Zaehler = 0 To (Forms!frmCMP!txtAuslaufjahr - Forms!frmCMP!txtAnlaufjahr)
SQL = "insert into tblLifecycle_Projektion(ID_Berichtstand,KVP_Kfm,KVP_technisch,AeJ,MoPf,skAe,MiBst,Sonstige_Effekte," & _
"KVP_technisch_FTR,KVP_Kfm_FTR,AeJ_FTR,MoPf_FTR,skAe_FTR,Sonstige_Effekte_FTR,Jahr) values ('" & newRow & "', " & _
"forms!frmCMP![txtKVP_kfm_IST" & Zaehler & "],forms!frmCMP![txtKVP_tech_IST" & Zaehler & "],forms!frmCMP![txtAJ_IST" & Zaehler & "], " & _
"forms!frmCMP![txtMoPf_IST" & Zaehler & "],forms!frmCMP![txtskae_IST" & Zaehler & "],forms!frmCMP![txtMiBst_IST" & Zaehler & "]," & _
"forms!frmCMP![txtSonst_Effekte_IST" & Zaehler & "],forms!frmCMP![txtKVP_fkm_FTR" & Zaehler & "]," & _
"forms!frmCMP![txtKVP_techn_FTR" & Zaehler & "],forms!frmCMP![txtAJ_FTR" & Zaehler & "],forms!frmCMP![txtMoPf_FTR" & Zaehler & "]," & _
"forms!frmCMP![txtskae_FTR" & Zaehler & "], forms!frmCMP![txtSonst_Effekte_FTR" & Zaehler & "],forms!frmCMP![txtAnlauf" & Zaehler & "])"
DoCmd.RunSQL SQL
Next Zaehler