CODEWITHSUNDEEP
sqlms-accessvba
user8533487
user8533487

Reputation:

MS Access - SQL append query behavior is erratic

I've been working on an Access database for the last couple weeks, and it's my first project with the tool. Dealing with append queries seems to have become an utter nightmare, and is incredibly frustrating. Even more so because it seems to have simply stopped working in any consistent manner overnight.

The SQL query that I have written goes thus:

PARAMETERS noteDetails LongText, noteTime DateTime, srcUserID Long;
INSERT INTO tblNotes (NOTE_DETAILS, NOTE_TIME_CREATED, NOTE_SOURCE_USER)
VALUES (noteDetails, noteTime, srcUserID)

In tblNotes:

NOTE_ID is an AutoNumber
NOTE_DETAILS is a Long Text
NOTE_TIME_CREATED is a Date/Time
NOTE_SOURCE_USER is a Number

The way that I'm running this query is through VBA:

Set qdf = CurrentDb.QueryDefs("qerApndNote")
qdf.Parameters(0).Value = txtDetails.Value
qdf.Parameters(1).Value = Now()
qdf.Parameters(2).Value = getCurrentUserID()
qdf.Execute dbFailOnError
qdf.Close
Set qdf = Nothing

' Where CurrUserID is a global long
' txtDetails.Value is a textbox's contents
' Now() is the VBA built-in function to return a date/time combo

I have attempted to run this query manually from the navigation bar, and it works fine when done in that manner.

However, running it from VBA has resulted in such things as there being no time / date inserted, sometimes a user ID is not inserted, sometimes both, sometimes even the details text is missing.

What is it that I'm missing? Is there any general advice for users of MS Access to follow that I am not? I'm aware that NOTE is a restricted word in Access, but I really don't think that should apply here, right?

Thanks in advance!

EDIT: The form that I'm passing data from is called frmNewNote, and there is a control in it named txtDetails. It's just a regular textbox. Don't really know what else to share about that.

The getCurrentUserID function is in a module, modGlobal:

Public CurrUserID As Long

Public Function getCurrentUserID() As Long
    getCurrentUserID = CurrUserID
End Function

Public Function setCurrentUserID(CurrID As Long)
    CurrUserID = CurrID
End Function

It's about as barebones as you can get, really. And there is never a circumstance that you'll get to the form before SetCurrentUserID has been called during your... session? There's a login form involved.

@Andre's code for logging:

 0            noteDetailsText             This is a note test
 1            noteTimeCreated             9/6/2017 10:28:45 AM 
 2            srcUserID      1

As for my architecture, um, it's just the single database file right now, on the desktop. The entire function/sub is run when you click a button, btnEnter. It does some other stuff before it gets to the SQL statement bit - checks for null values and prompts user for entries if that's the case.

Upvotes: 2

Views: 130

Answers (2)

Andre
Andre

Reputation: 27644

I just remembered something:

MS Access 2013 calling insert queries from VBA with strange errors

You have a LongText parameter. These don't really work. See also https://stackoverflow.com/a/37052403/3820271

If the entered notes will always be <= 255 characters, change the parameter to ShortText.

If the text can be longer, you'll have to use either SunKnight0's approach with a concatenated INSERT statement.

Or use a Recordset and its .AddNew method, which will be a similar amount of code to your current solution, but also be completely safe from injection or formatting issues.

Upvotes: 1

SunKnight0
SunKnight0

Reputation: 3351

You are doing way more work than you have to. All you need is:

DoCmd.RunSQL("INSERT INTO tblNotes (NOTE_DETAILS, NOTE_TIME_CREATED, NOTE_SOURCE_USER) VALUES ('" & Me.txtDetails & "',Now()," & CurrUserID  & ")")

Note the change from txtDetails.Value to Me.txtDetails which is what may have been messing you up. This of course assumes the code runs in the form's context, otherwise you have to get he value of the text field using a reference to the form.

The only other thing to consider is making sure Me.txtDetails does not have any single quotes, so probably use Replace(Me.txtDetails,"'","''") instead.

That way you can also replace DoCmd.RunSQL with MsgBox to troubleshoot the exact query.

Upvotes: 0

Related Questions