Access VBA Parameter Query Changes Integer to String

Question

Okay, I need help resolving what should be an easy issue. I am trying to do an UPDATE query to an Access table. I have the ID of the record to be updated in a hidden text box on my form. What happens is that the query def changes my Integer to a string when it stores it in the parameter. It does this even after I cast the value to an Integer. p6 is the parameter name. See code below. I get a data type mismatch error on every other field that has an integer value as well.

    Private Sub SubmitButton_Click()
    Dim db              As DAO.Database
    Dim qdf             As DAO.QueryDef
    Dim strSql          As String
    Dim frm             As Object

    If IsRequiredFilled(Me) = False Then
        MsgBox "Please fill out all required fields.", vbCritical
        Exit Sub
    End If

    Set db = CurrentDb
    strSql = "UPDATE [Batches_T] " & _
             "SET [BatchName] = [BatchName] + [p1], " & _
             "[StatusID] = [StatusID] + [p2], " & _
             "[InternalStatusID] = [InternalStatusID] + [p2], " & _
             "[ReviewerID] = [ReviewerID] + [p3], " & _
             "[StartDate] = [StartDate] + [p4], " & _
             "[PowerPointFilePath] = [PowerPointFilePath] + [p5] " & _
             "WHERE [ID] = [p6]"

    Set qdf = db.CreateQueryDef(vbNullString, strSql)
    With qdf
        .Parameters("p1").Value = Me.BatchName
        .Parameters("p2").Value = Me.StatusID
        .Parameters("p3").Value = Me.InternalStatusID
        .Parameters("p4").Value = Me.StartDate
        .Parameters("p5").Value = Me.PowerPointFilePath
        .Parameters("p6").Value = CInt(Me.ID)
        .Execute dbFailOnError
    End With

    Set qdf = Nothing
    Set db = Nothing

    Forms![Dashboard_F]![Batches_DS_F].Requery
    If Me.keepOpenCheckBox = False Then
        DoCmd.Close acForm, "AddBatch_F", acSaveYes
    End If
End Sub

Erik A · Accepted Answer · 2018-01-26 16:46:00Z

1

Try and add explicit type declarations for the parameter:

strSql = "PARAMETERS [p6] INTEGER; " & _
         "UPDATE [Batches_T] " & _
         "SET [BatchName] = [BatchName] + [p1], " & _
         "[StatusID] = [StatusID] + [p2], " & _
         "[InternalStatusID] = [InternalStatusID] + [p2], " & _
         "[ReviewerID] = [ReviewerID] + [p3], " & _
         "[StartDate] = [StartDate] + [p4], " & _
         "[PowerPointFilePath] = [PowerPointFilePath] + [p5] " & _
         "WHERE [ID] = [p6]"

answered Jan 26, 2018 at 16:46

Erik A

32.8k13 gold badges48 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

JasonG Over a year ago

Thank you! This is what I was looking for. I knew there had to be a way to do that, just didn't know, and couldn't find it anywhere. Works perfectly.

Collectives™ on Stack Overflow

Access VBA Parameter Query Changes Integer to String

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related