Adding VBA Code to a Macro - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Adding VBA Code to a Macro

Jeffrey Hebert

3 years ago

I have a Append Query in a subform that adds a record to a table. The query work fine but I am unable to get the subform to automatically update after the query is executed. I have a separate Command button that runs a me.requery event that updates the subform when pressed. I want to include refresh in the Macro that runs the Append Query but I can't get it to work. I've tried adding a RunCode function that calls a Module with the Me.requery but this throws a error "The component doesn't make the property or method available for Automation operation". The MS RefreshAll command also work but I can't figure out how to add this the macro either.

	Kevin Yip @Reply 3 years ago
You can create a custom VBA function that runs the Requery command, then have the macro call that function. See picture below.

	Kevin Yip @Reply 3 years ago

	Jeffrey Hebert OP @Reply 3 years ago

	Jeffrey Hebert OP @Reply 3 years ago

	Jeffrey Hebert OP @Reply 3 years ago
I had tried that before but I must have a simple error in the process. Above are screen shots of the saved location, code, Macro I 'm trying to call the code with, and the resulting error when the full macro is executed. The macro works fine until I add the RunCode piece at the end. Thoughts? Jeff

	Adam Schwanz @Reply 3 years ago
There is an actual requery function inside the macros, don't need to runcode it. Otherwise, I would advise just making the whole thing out of VBA instead of using a macro.

	Alex Hedley @Reply 3 years ago
The sub is called `Refresh()`

	Alex Hedley @Reply 3 years ago
Also Refresh and Requery do different things so this naming convention could be difficult to understand.

	Jeffrey Hebert OP @Reply 3 years ago

Jeffrey Hebert OP @Reply

3 years ago

Okay, I renamed the Sub to JeffRefresh() and used that name as the Function name. I still get the same "The expression you entered has a function name that Microsoft Access can't find". I even tried entering the sub name of a command button that completes the same refresh (plus a bit more) as the JeffRefresh VBA and I get the same error. It SEEMS like I'm having trouble with how I am entering the function name. Should it be "JeffRefresh()"?

	Alex Hedley @Reply 3 years ago
`Private` or `Public`?

	Kevin Yip @Reply 3 years ago
Hi Jeffrey, your procedure is not a function, but a sub (short for subroutine). You need to make it a function, and a public one too. Instead of this: Private Sub JeffRefresh() ... End Sub Use this: Public Function JeffRefresh() ... End Function

	Jeffrey Hebert OP @Reply 3 years ago
Thanks Kevin, That worked (after a slight adjustment), I did have to get rid of the Me.xxxx functions and replace the Me with the Form and sub form to get it to work. I now see how I need to use a Public Function when using the RunCode macro.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View