Is it possible to use Parameters with an Insert query in vba? What am I doing wrong?

[Vita]

So I initially had a bunch of concatenated strings with the values in between them (EX "VALUES (' & Me.LOT & "','" & Me.QUANTITY & "',' etc) but I saw Parameters looked a lot cleaner and allowed for the same if not more control.
When I run this code I get an error in the insert into syntax.

        Dim qdf As DAO.QueryDef, strSQL As String        strSQL = "PARAMETERS prmLot Text(255), prmQuantityShipped Long, prmLength Long, prmCalcLbsShipped Long, prmDate Date, prmPO Long; "
        strSQL = strSQL & "INSERT INTO tblShippingHistory (IdLot, QuantityShipped, Length, CalcLbsShipped, Date, PO) "
        strSQL = strSQL & "VALUES ([prmLot] , [prmQuantityShipped], [prmLength], [prmCalcLbsShipped], [prmDate], [prmPO]);"
        Debug.Print strSQL
        Set qdf = CurrentDb.CreateQueryDef("", strSQL)


'        qdf.Parameters!prmHeat = Me.HEAT
' not sure if I can use a with here but wanted to try. Cant even get to it as it errors on Set qdf


        With qdf.Parameters
            !prmLot = Me.LOT
            !prmQuantityShipped = Me.QUANTITY
            !prmLength = Me.LENGTH
            !prmCalcLbsShipped = FTtoLBS(Me.QUANTITY)
            !prmDate = DATE
            !prmPO# = Me.MASTER_PO_
        End With

I actually couldn't find any examples of Parameters being used with an Insert query when I was googling.


So I'm not sure if they even work together but I don't see why they wouldn't.


The actual goal of this code is to enter some values from 2 different forms into a table that is not the form source.
As always thanks for any help in advance.

[Minty]

I copied this from another forum with the virtually the same question

Const Sql_Insert As String = _ 
          "Insert into tblPeople" & _ 
          "(FirstName,MiddleName,LastName,Suffix) " & _ 
          "Values(p0,p1,p2,p3)"


     With CurrentDb.CreateQueryDef("", SQL_Insert)
          .Parameters(0) = Me.FirstName
          .Parameters(1) = Me.MiddleName
          .Parameters(2) = Me.LastName
          .Parameters(3) = Me.Suffix
     .Execute dbFailOnError
     .Close
End With

[Vita]

I copied this from another forum with the virtually the same question

Const Sql_Insert As String = _ 
          "Insert into tblPeople" & _ 
          "(FirstName,MiddleName,LastName,Suffix) " & _ 
          "Values(p0,p1,p2,p3)"


     With CurrentDb.CreateQueryDef("", SQL_Insert)
          .Parameters(0) = Me.FirstName
          .Parameters(1) = Me.MiddleName
          .Parameters(2) = Me.LastName
          .Parameters(3) = Me.Suffix
     .Execute dbFailOnError
     .Close
End With

I didn't even think to use the search on the forum smh. So I don't actually need to use the Parameters line inside the SQL statement?
I was attempting to use the documentation on parameters to figure it out myself and it appears that you need to include it in the SQL statement.

I also failed to realize that Date is a reserved word. Had to change it to [Date].

[moke123]

code looks familiar



An update Qdf would look like

[Vita]

code looks familiar



An update Qdf would look like

Forms to generate code? Interesting. I should look more into add-ins and think about making some.
I see the difference but what confused me (still does a bit) is that the documentation shows a separate line before the actual query that seems to declare the parameters. (Copied example from the docs below)
Is this only necessary on selects or is it entirely optional?

Sub CreateQueryWithParameters()
    
        Dim dbs As DAO.Database
        Dim qdf As DAO.QueryDef
        Dim strSQL As String
    
        Set dbs = CurrentDb
        Set qdf = dbs.CreateQueryDef("myQuery")
        Application.RefreshDatabaseWindow
    
        strSQL = "PARAMETERS Param1 TEXT, Param2 INT; "
        strSQL = strSQL & "SELECT * FROM [Table1] "
        strSQL = strSQL & "WHERE [Field1] = [Param1] AND [Field2] = [Param2];"
        qdf.SQL = strSQL
    
        qdf.Close
        Set qdf = Nothing
        Set dbs = Nothing
    
    End Sub

This is another example I saw (Source)

Dim oDb As DAO.Database, qdef As DAO.QueryDef
Dim StrSql As String, strProductionStatus As String


GetCurrentProductionStatusString NewProductionStatus, strProductionStatus


Set oDb = CurrentDb


StrSql = "PARAMETERS strProductionStatusParam Text(255), lngProductionIdParam Long;" _
           & " UPDATE tProduction SET tProduction.Statut = [strProductionStatusParam]" _
           & " WHERE (tProduction.IdProduction = [lngProductionIdParam]);"


Set qdef = oDb.CreateQueryDef("", StrSql)


qdef!strProductionStatusParam = strProductionStatus
qdef!lngProductionIdParam = lngProductionId


qdef.Execute dbFailOnError


Set qdef = Nothing
Set oDb = Nothing

My code is working as intended now but I just want to know out of curiosity/learning purposes.
(I do have another semi-related question but one thing at a time)

P.S. Thanks for your time teaching!