MS Access 2013 saved append query not updating all fields

Question

I have a saved query, qryInsertLog which is as follows:

PARAMETERS UserIDPar Long, UnitIDPar Long, LogEntryPar LongText, FNotesPar LongText;
INSERT INTO tblLogBook ( UserID, UnitID, LogEntry, FNotes )
SELECT [UserIDPar] AS Expr1, [UnitIDPar] AS Expr2, [LogEntryPar] AS Expr3, [FNotesPar] AS Expr4;

I'm trying to run this query when a save button is clicked on an unbound form, where the parameters are gathered from the form controls. My VBA code for the save button is:

Private Sub cmdSave_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim okToSave As Boolean

If Me.cboUser.Value = 0 Or IsNull(Me.cboUser.Value) Then
    MsgBox "You must choose a user. Record not saved."
    okToSave = False
ElseIf Me.cboUnit.Value = 0 Or IsNull(Me.cboUnit.Value) Then
    MsgBox "You must choose a unit. Record not saved."
    okToSave = False
ElseIf Me.txtLogEntry.Value = "" Or IsNull(Me.txtLogEntry.Value) Then
   MsgBox "You must have somtehing to log. Record not saved."
    okToSave = False
Else
    okToSave = True
End If

Set db = CurrentDb
Set qdf = db.QueryDefs("qryInsertLog")

qdf.Parameters("UserIDPar").Value = Me.cboUser.Value
qdf.Parameters("UnitIDPar").Value = Me.cboUnit.Value
qdf.Parameters("LogEntryPar").Value = Me.txtLogEntry.Value
qdf.Parameters("FNotesPar").Value = IIf(IsNull(Me.txtFNotes.Value), "", Me.txtFNotes.Value)

If okToSave Then
     qdf.Execute
End If

qdf.Close
Set qdf = Nothing

End Sub

When this code is run, the FNotes field of the table isn't updated. The other three fields update as expected. FNotes is the only field which isn't required. I hardcoded a string for FNotes paramater like so:

qdf.Parameters("FNotesPar").Value = "why doesn't this work"

rather than using the form control value, and got the same result: that field just doesn't update. When I run this query from the Access Objects window and supply parameter values from the prompts, it works just fine. When I create form that's bound to the table, it also seems to work just fine.

I can't figure out why there's no trouble updating the LogEntry field but the FNotes field fails to update.

You have parameter query issues. No luck with last one either? Check your values using debug.print. Add a dbFailonError to your Execute. — dbmitch

HansUp HansUp · Accepted Answer · 2016-08-24T13:22:51

Add the new record via a DAO.Recordset instead of a DAO.QueryDef.

First, include this declaration ...

Dim rs As DAO.Recordset

Then use this after Set db = CurrentDb ....

Set rs = db.OpenRecordset("tblLogBook")
With rs
    If okToSave Then
        .AddNew
        !UserID = Me.cboUser.Value
        !UnitID = Me.cboUnit.Value
        !LogEntry = Me.txtLogEntry.Value
        !FNotes = Nz(Me.txtFNotes.Value, "")
        .Update
    End If
    .Close
End With

Note Nz(Me.txtFNotes.Value, "") gives you the same thing as IIf(IsNull(Me.txtFNotes.Value), "", Me.txtFNotes.Value), but more concisely.

MS Access 2013 saved append query not updating all fields

1 Answers