-3

I apologize if this has been answered, but I really don't have the time to search thoroughly right now. Partially because I'm doing it for work. And I'm asking from my phone so I can't screenshot, etc.

I've got a database built in Access 2016 and am having an issue.

I have a form that updates a table based on whats entered and I'd like to see whether or not anything was actually updated in the table without opening the query and scrolling through the results.

I have a macro set up to run when the 'update' button is clicked, which runs a different query based on the value for a specific field. I.E. "if field = 6, runquery update6"

I wrote a function in the VBA thing called RecordsChanged that is literally just

[ AffectedRows = CurrentDb.RecordsAffected MsgBox CStr(AffectedRows) & " records changed" ]

Which I got from here: How to show how many records were updated by an update query?

To quit rambling, the message box displays but always says 0 records changed even if there was one changed. I have a RunCode action at the end of the macro hooked into the button on the form that calls RecordsChanged.

Can anyone give me advice or an explanation?

D34DL0CK
  • 1
  • 1
  • When you say "runquery update6" do you mean that "update6" is the name of a saved Update Query in the Access database? – Gord Thompson Aug 26 '17 at 22:10
  • Gord - Yes. Not the literal name but thats what i meant. Its basically set up in the macro as: if certain_field = 6 then runquery update6 – D34DL0CK Aug 26 '17 at 22:20
  • I've played around with it a little bit and I think the problem is either I didn't set up the CurrentDb properly or by the time its asking whether any records were updated the query is technically finished. Hmmm... – D34DL0CK Aug 26 '17 at 23:00
  • I may also not have as firm a grasp on all of this as I thought... Only started using access a week ago and have never touched vb. – D34DL0CK Aug 27 '17 at 01:55

1 Answers1

1

As far as I know, the DAO.Database.RecordsAffected property in VBA only applies to SQL queries that are run by the DAO.Database.Execute method. Since you are calling a VBA function from your macro anyway, you may have better luck running the query in VBA using a DAO.QueryDef object and using its RecordsAffected property, e.g.,

Public Function RunMyUpdateQuery(queryName As String)
    Dim cdb As DAO.Database
    Set cdb = CurrentDb
    Dim qdf As DAO.QueryDef
    Set qdf = cdb.QueryDefs(queryName)
    qdf.Execute dbFailOnError
    Dim affected As Long
    affected = qdf.RecordsAffected
    Set qdf = Nothing
    Set cdb = Nothing
    MsgBox affected & " record(s) affected."
End Function

where your macro would do

RunCode  RunMyUpdateQuery("update6")
Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • I am about to leave work so I will try first chance I have tomorrow. To clarify, I would write the query in access itself (I'm good with SQL) save it as "update6" then more or less copy/paste what you provided into the VBA? I think my confusion is coming from the syntax of VB. Sorry for the long comment and thanks for the answer. – D34DL0CK Aug 27 '17 at 04:06
  • The answer was based on the assumption that you already had a saved Update Query named "update6" (ref: my comment to the question). If you don't already have a saved query then you don't really need to create one; you could also just have your VBA code `cdb.Execute` the SQL and then use `cdb.RecordsAffected` to get the count. – Gord Thompson Aug 27 '17 at 17:53
  • Sorry for the late response. I've been super busy. Thank you for the answer. I modified it slightly but that was the issue I was having, not using the querydef objects recordsaffected. Works like a charm. – D34DL0CK Sep 02 '17 at 20:27