VBA INSERT query

[Soundje]

Hello,
Anybody still awake for a stuppid/ easy question ?

I wondering how I can use this INSERT query to insert the value of max_hoeveelheid_contract into the "AS Expr7" part (end of the strQuery)

 Dim max_hoeveelheid_contract As String
max_hoeveelheid_contract = som_hoeveelheid_NettoGewicht -Me.Hoeveelheid.Value 
  
 Dim strQuery As String

 strQuery = "INSERT INTO tbl_data_leverancier ( contractID, Datum, Artikel, [Hoeveelheid Bruto], [RestAfval Tara], prijs, Hoeveelheid_Netto ) SELECT [Forms]![frml_contract_lev]![ContractID] AS Expr1, [Forms]![frml_contract_lev]![Datum_Toevoegen] AS Expr2, [Forms]![frml_contract_lev]![Artikel] AS Expr3, [Forms]![frml_contract_lev]![hoeveelheid_bruto_toevoegen] AS Expr4, [Forms]![frml_contract_lev]![RestAfval_tara_toevoegen] AS Expr5, [Forms]![frml_contract_lev]![Prijs] AS Expr6, " & max_hoeveelheid_contract & " AS Expr7;"
  
 DoCmd.RunSQL strQuery

Thanks a lot for your helps

Best regards,
Koen

 

[jdraw]

Try this (untested)

Dim max_hoeveelheid_contract As String
max_hoeveelheid_contract = som_hoeveelheid_NettoGewicht -Me.Hoeveelheid.Value 
  
 Dim strQuery As String

 strQuery = "INSERT INTO tbl_data_leverancier ( contractID, Datum, Artikel, [Hoeveelheid Bruto], [RestAfval Tara], prijs, Hoeveelheid_Netto )
SELECT " & [Forms]![frml_contract_lev]![ContractID] & " AS Expr1," &  [Forms]![frml_contract_lev]![Datum_Toevoegen] & " AS Expr2," & [Forms]![frml_contract_lev]![Artikel] & " AS Expr3," & [Forms]![frml_contract_lev]![hoeveelheid_bruto_toevoegen]  & " AS Expr4," & [Forms]![frml_contract_lev]![RestAfval_tara_toevoegen] & " AS Expr5," &  [Forms]![frml_contract_lev]![Prijs] & " AS Expr6, " & max_hoeveelheid_contract & " AS Expr7;"
  
 DoCmd.RunSQL strQuery

I recommend you insert a Debug.Print strQuery before the DoCmd.RunSQL strQuery and comment that line until you get the syntax correct.

 

[Soundje]

Thanks jdraw that is working however I found something else and I do not know how to handle this...

the value of 'nieuweprijs' contains a comma since it is actually a number how should I handle this in the query (gives all the time an error because it thinks it is an extra field that I want to add not linked)


Here is the code i finally used:

strQuery = "INSERT INTO tbl_data_leverancier (contractID, Datum, Artikel, [Hoeveelheid Bruto], [RestAfval Tara], prijs, Hoeveelheid_Netto ) VALUES ([Forms]![frml_contract_lev]![ContractID], [Forms]![frml_contract_lev]![Datum_toevoegen], 1, [Forms]![frml_contract_lev]![hoeveelheid_bruto_toevoegen],[Forms]![frml_contract_lev]![RestAfval_tara_toevoegen]," & nieuweprijs & "," & max_nog_leveren & ");"

Debug.Print strQuery
DoCmd.RunSQL strQuery

Any ideas?


Many thanks in advanced !!!

Kindly regards,
Koen

 

[JANR]

Since your regional settings uses comma as a decimal seperator for numbers and Jet/Ace-engine expect a period as seperator it will fail.

solution 1 is to wrap 'nieuweprijs' with the Str() function to covert the number into something the engine can understand

... & Str(Nieuweprijs) & ....

Solution 2 is to use full formrefrence to nieueprijs (I assume the control is on a form) like you done with the rest of you sql string. note that you do refrence it inside the sql-string. Drawback is you have to use DoCmd.RunSql and not the Currentdb.Execute methode.

...[Forms]![frml_contract_lev]![RestAfval_tara_toevoegen], [Forms]!Formname!nieuweprijs " & " ,"....

Solution 3 is to create a temporary querydef and excecute any action queries. This methode has the benefit of ignoring all delimiters you have to use with your current methode of executing your queries.

With CurrentDb.CreateQueryDef("", "Insert into tbl_data_leverancier (contractID, Datum, Artikel,[Hoeveelheid Bruto], [RestAfval Tara], prijs, Hoeveelheid_Netto ) " & _
                                  "Values (p0,p1,p2,p3,p4,p5,p6)")
    .Parameters("p0") = Forms!frml_contract_lev!ContractID
    .Parameters("p1") = Forms!frml_contract_lev!Datum_toevoegen
    .Parameters("p2") = 1
    .Parameters("p3") = Forms!frml_contract_lev!hoeveelheid_bruto_toevoegen
    .Parameters("p4") = Forms!frml_contract_lev!RestAfval_tara_toevoegen
    .Parameters("p5") = nieuweprijs
    .Parameters("p6") = max_nog_leveren
    .Execute
End With

Note that if this is executed from your form frml_contact_lev, you can shorten the refrence to the form controls by using Me.ContractID

.Parameters("p0") = Me.ContractID

Good luck

Jan R