2

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.

  • well you don't show connecting to the database or closing the db connection so I'm guessing there is something wrong in those parts. – Hogan Sep 06 '17 at 17:03
  • Perhaps try `Set cdb = CurrentDb` followed by `Set qdf = cdb.QueryDefs("qerApndNote")` – Gord Thompson Sep 06 '17 at 17:04
  • my other suggestion is to change the routine to do some logging -- log the values before the call, log the values after the call, include timestamps. Mysteries will be revealed. – Hogan Sep 06 '17 at 17:06
  • Kind of a long shot, but try addressing the parameters by name, not by index. `qdf.Parameters("noteDetails").Value = txtDetails.Value` etc. – Andre Sep 06 '17 at 17:07
  • Still isn't filling in the user ID or the date time columns, though I thank you all for your help nonetheless. –  Sep 06 '17 at 17:16
  • I do not believe the query or code is the issue. You are passing parameters from what appears to be a form control and another method, `getCurrentUserID()`. These may not retain values at runtime of query. Hence the inconsistency. Please show their code and/or describe form. – Parfait Sep 06 '17 at 17:18
  • In fact, since you mention `Now()` strangely does not insert, I am convinced it may be your environment. Please describe architecture. FE/BE split across a LAN network? How is this query triggered (what form event or macro)? Do not run MS Access on thumb drives or internet folders (Dropbox, Google Drive, etc.). – Parfait Sep 06 '17 at 17:22
  • Hmm. Your code, parameters and their values seem perfectly fine. *This should work.* Grr. In case you don't do this regularly anyway, try Compact/Repair and a full [Decompile](http://stackoverflow.com/a/3268188/3820271). – Andre Sep 06 '17 at 18:05
  • @Andre I do regularly compact/repair, which I'm glad to know is a good idea. The whole decompile / recompile thing doesn't seem to have changed anything though, apart from the fact that _frmNewNote_, which is a popup, refuses to pop up now... –  Sep 06 '17 at 18:25
  • Just a thought. Since you are passing Now(), have you tried removing noteTime as a parameter, and insert Now() instead. It shouldn't make a difference, but I have seen instances where doing something like this has made a difference, and has provided the clue as to where to look for the problem! Re frmNewNote post recompile - can you still open it in design view? – Jonathan Willcock Sep 06 '17 at 18:53
  • @JonathanWillcock Huh. Would you look at that. It takes care of that particular piece. Still got another to fix, but hey, progress! –  Sep 06 '17 at 20:19

2 Answers2

1

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.

Andre
  • 26,751
  • 7
  • 36
  • 80
0

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.

SunKnight0
  • 3,331
  • 1
  • 10
  • 8
  • 1
    From everything that I've read and learned about until now, this right here isn't exactly the best practice. The way I've done it, I actually _can_ have single quotes in my strings, because the queries are parameterized. At least, that's the gist of it, I think. –  Sep 06 '17 at 17:47
  • I added on more change with the use of `Me.`. As for the best practice it really depends on your environment and the openness of your system. In most cases when dealing with just the Access UI and not an Internet facing application, just using replace to deal with single quotes makes life a lot easier, especially when having much more complicated queries generated by code. I usually create a short function calling it something like `Qs` to take care of the single quote issue, to make code more readable. – SunKnight0 Sep 06 '17 at 17:54