Parameterized SQL UPDATE problem

[zepphead80]

Hi all:

I've got one of those problems that I just can't get around, no matter what! I'm working in Access 2007/VBA, using an ADO command structure.

In my code, I've got a SQL UPDATE like:

strSaveLeave = "UPDATE tblLeave 
SET fldLeaveType = @LeaveType, 
        fldLeaveStatus = @LeaveStatus, 
        fldDateLastWorked = @DateLastWorked, 
        fldDateLeaveStart = @DateLeaveStart, 
        fldDateLastPaid = @DateLastPaid, 
        fldDateLeaveEnd = @DateLeaveEnd, 
        fldDateWarningLetter = @DateWarningLetter, 
        fldDateAWOLEmail = @DateAWOLEmail, 
        fldDateReturnEmail = @DateReturnEmail, 
        fldDateReturned = @DateReturned, 
        fldLeaveNotes = @LeaveNotes 
WHERE tblLeave.fldLeaveNum = @LeaveNum AND tblLeave.fldERN = @ERN"

My parameters are created like this:

'Set command information for leave

Set commSaveLeave = New ADODB.Command

With commSaveLeave

    .CommandType = adCmdText
    .CommandText = strSaveLeave
    .ActiveConnection = conn.ConnectionString
    
    .Parameters.Append .CreateParameter("ERN", adChar, adParamInput, 7, currLeave.ERN)
    .Parameters.Append .CreateParameter("LeaveNum", adInteger, adParamInput, , currLeave.LeaveNum)
    .Parameters.Append .CreateParameter("LeaveType", adInteger, adParamInput, , currLeave.LeaveType)
    .Parameters.Append .CreateParameter("LeaveStatus", adChar, adParamInput, 1, currLeave.LeaveStatus)
    .Parameters.Append .CreateParameter("DateLastWorked", adVarChar, adParamInput, 8, currLeave.DateLastWorked)
    .Parameters.Append .CreateParameter("DateLeaveStart", adVarChar, adParamInput, 8, currLeave.DateLeaveStart)
    .Parameters.Append .CreateParameter("DateLastPaid", adVarChar, adParamInput, 8, currLeave.DateLastPaid)
    .Parameters.Append .CreateParameter("DateLeaveEnd", adVarChar, adParamInput, 8, currLeave.DateLeaveEnd)
    .Parameters.Append .CreateParameter("DateWarningLetter", adVarChar, adParamInput, 8, currLeave.DateWarningLetter)
    .Parameters.Append .CreateParameter("DateAWOLEmail", adVarChar, adParamInput, 8, currLeave.DateAWOLEmail)
    .Parameters.Append .CreateParameter("DateReturnEmail", adVarChar, adParamInput, 8, currLeave.DateReturnEmail)
    .Parameters.Append .CreateParameter("DateReturned", adVarChar, adParamInput, 8, currLeave.DateReturned)
    .Parameters.Append .CreateParameter("LeaveNotes", adVarChar, adParamInput, 250, currLeave.LeaveNotes)
    
End With

commSaveLeave.Execute

Set commSaveLeave = Nothing

In this table, fldERN is a foreign key that connects to it's primary key counterpart in another table - however for the purpose of this UPDATE, I'm only working with this table.

Now, when I run this code, it doesn't give me any errors. It's simply that nothing happens. I open up an existing record in the form, make a few changes, hit my save button...and the changes don't show up in the record. What's interesting though is that when I construct the WHERE clause using strings and comment out the .CreateParameter lines for ERN and LeaveNum like this:

WHERE tblLeave.fldERN = '" & currLeave.ERN & "' AND tblLeave.fldLeaveNum = " & currLeave.LeaveNum

...it works fine! I just don't know why the statement won't work when I use parameters in the WHERE clause. I have also successfully executed INSERT INTO and SELECT statements using the same parameters.

Any insight? My table relationships diagram is attached. Thanks so much...

Pat