Return macro name from a table in VBA

I am builing a reporting database where I have a form(Report Dashboard) that has an unbound control where the control source is a table (Reports) and reflects the report name from the table. The table has the report name and another field called Trigger that has the name of the macro to run. When user selects the Report Name from the unbound control (ie on click) I am trying to build a VBA code that runs the macro in the field Trigger from the Reports table. Here is what I have cobbled together but I get an compile error stating "object required" on my set command for strMacro.

Private Sub lstReports_Click()
Dim myDb As DAO.Database
Dim strMacro As String

Set myDb = CurrentDb()
Set strMacro = myDb.OpenRecordset("SELECT Trigger FROM Reports")

DoCmd.RunMacro "strMacro"

On Error GoTo strTrigger_Error

strTrigger_Done:
Exit Sub

strTrigger_Error:
MsgBox "An error has occured - no associated macro."
Resume strTrigger_Done

Sponsored Links:

Related Topics

Run a converted Macro
I am a beginner in Access VBA. I have created a macro in access to run a make table query and then rename the table. Now I want the current date to be part of the renamed table.
So, I converted the Macro to VBA and then put the date field in the code. When I run it from the VBA editor, using F5, it runs great and produced the right output. But, when I run the original macro, it is still creating the hard coded name as in the macro.

I want to know, how to run the converted macro, without having to go into the VBA editor mode


2010 VBA "do while" code in a macro
In Access 2010, want to run perform a "do while" loop in a macro. I understand VBA is the way to do this. So, I think I want to run a VBA script in a macro.

This Macro runs against a Form that has a SubFrom (MainForm named "Expense ID" & SubForm, "Expense"). Macro is initiated by pressing a button.

I would like help with some VBA code showing how to perform the following:

1. Evaluate a field ("Activity") in the first record of Subform ("Expense") against condition "Is not Null".

2. If condition is true, set value of another field ("Post") in the same SubForm record to "1".

3. Move to next record and repeat steps 1 & 2 until all Subform records are evaluated.

4. Return to macro that initiated the VBA script to perform the next action in the macro.


Macro from Access 2007 Ribbon: Return without GoSub
I am calling a macro from a ribbon using XML. The macro calls a function to run however I keep getting the following error:
'Return without GoSub'

And then a message pops up. I have put debugger on the first line of the function, but it never gets there -therfore it must be the macro, which is a simply a RunCode macro calling the function.


Jump Start VBA Skills with MS Access Macros
Macros help to speed up the performance of certain repetitive tasks when using an application. They are used extensively in all office-related software. As a programmer, you can leverage the built-in macro object in MS Access to get a head start in understanding VBA. This article explains how. TOC: Jump Start VBA Skills with MS Access Macros; Creating and running a macro in MS Access: opening the macro designer; Creating the first macro; Converting this macro to VBA code; Creating the second macro.


Collating Return Data
I am in the process of collating & quantifying customer return data, and have a table that I am entering return information (i.e. Return Date, Return Code (reason for the return), $ of the Return, etc...) What I would like is to compile this data into a table that has the monthly totals based on the Return Code (so, I will end up with one column for each return code). I will then use this table to create a chart (I know how to do the chart, but need help compiling the data).


Update table via VBA code
Have a table (several hundred entries) which must be updated weekly using VBA code. I know how to write the actual VBA code (for each record) but I do not know how/where/what is necessary to open the table, read each record seq and update based on VBA code (function call return value) and to finally close table.


Creating a Validation Rule in an Access Table
The validation rules use the same logical expressions that can be found typically in criteria of a query and must return a logical value of either TRUE or FALSE in order to accept or reject the value and trigger an exception (validation text property). This simple property is easier to apply than attaching a macro or VBA code to an event which means learning VBA programming!


Run a Embedded Macro from VBA Code
I am trying to rerun an embedded macro which is attached to the AfterUpdate event of a combobox. Is there anyway of rerunning this query from VBA Code?

I have tried using:

DoCmd.RunMacro

However it says you need to insert the Macro name however I do not know what the embedded macro is called in order to refer to it


Querying a table to return duplicate values
seriously I need help with a table and form. In short I want to know if I can modify a query to return all duplicates but only if it matches a combo box entry if so can I then have the query add the matching data to the same table?
I have a table of customer names and a macro to run a query to find duplicate names from my table after update. I just don't want to keep typing all the details when they are already there basically. So say Mike called and on my form I typed mike into the combo box instead of all the duplicates appear Can I modify it to show only customers called mike?

PHEW!

Oh btw I don't now anything about sql or vba and would be really appreciative of any help given.


VBA to get value from combobox to run a macro
How can I get vba to do this.

When I click a button on the main form the vba code will execute and get the value from the combobox ie TE4700 and locate the macro with the same name ie TE4700 with reports in then it willrun the macro and print off the reports.


Renaming external file from within a macro
I only need to rename a txt file created by a dos procedure invoked by an Access macro through the same macro. What happens is that the txt file just created seems to be held by the macro, because if I try to rename it through a VBA module inside Access database by runnig it after the creation during the macro, nothing happens, conversely if I let the macro finish and execute the VBA renaming module inside a new macro, it works fine. Maybe I need to create an external exe vba file to rename the txt file to be invoked by the main macro?


Access table valued function equivalent
I'm hoping someone can put me in the right direction. I have a C# app that uses access as the DB. I have some vba code that will create a table and populate it with some data. I now need a way to call the function and select from the new table externally.

I'm still relativly new to programming in vba but as far as I know you can't call a vba functional from outside the database. What I'm hoping for is some way I can select all from the function and have the function return a table, the same as table valued functions in MSSQL. e.g. SELECT * FROM myFunction()

Is this possible in assess? what would I need to do to my function to have it populate and return a table? I'm not trying to be spoon feed here, I'm just a little lost for direction. If someone can point me the right way I'm happy to do more resarch myself.


VBA Code for buttons, Access 2010
In using this new version of Access, I have found that when creating buttons, Access no longer uses vba codebehind to handle all the dirty work - rather, it seems Microsoft is forcing me to use the upgraded (and very nice I must say) Macro builder.

This is fine for all the simple tasks, but what I have myself into is a little more complex than the Macro builder is ready tohandle I think.

Before, when I created a button, not a Macro. I am trying to build the following button types:

Button 1 - Update a Field in a table, Save form, Close form
Button 2 - Update a field in a table, save form, new record
Button 3 - Update a field in a table, save form, print report, close form . and so on and so forth.

I used to put 3 or 4 buttons on the form, then copy and paste the generated vba in the correct order in a single button, and delete the dummy buttons. But now it seems as the basicfunctions are built using an embedded macro, and when that is combined with VBA, Access must choose between one or the other, rendering my button useless.

Is there a setting in 2010 that will change the way buttons are created in that VBA is used by default instead of macros?


Convert macro to vba (2007)
Anyone know where the Convert macro to vba is on access2007?

else
I need this criteria set into the macro somehow

strCriteria = "[claimID]=Forms!frmClaimsParts!claimID"

& this is the macro

Report, rptParts, PDF Format (*.pdf), , Yes, , 0, Print


Storing a DAO.RecordSet/DAO.QueryDef in a TempVar in an Embedded Macro
In an embedded macro, is it possible to use a TempVar to store a DAO.RecordSet or DAO.QueryDef? Or otherwise, is there another way to use a Recordset or QueryDef in an Embedded Macro?

For example, say I have a Table (called myTable), that has 2 fields, ID, myFieldA. I want to make a Form that has an Unbound text box (say, boxA). Then, I want to make an Embedded Macro that queries myTable, using ID as the criteria field, so that the query would return only one record.

The Macro would then temporarily store the RecordSet, so that it would populate thetextbox, boxA, with the value of myFieldA from the query.

I know about binding boxA to myFieldA. I also know how to do this using VBA. But for my purposes here, I want to keep all of the form elements Unbound, and drive the user interface with queries, using only Embedded Macros.

Then I could use smarter logic for doing things such as validating that the user fills in required boxes before writing records to the table, verifying that the user intends to save changes, etc.

(I've had enough pain dealing with Access' clunky BeforeUpdate and AfterUpdate events).


Loop in access 2010 macro
I am trying to create a macro to loop through all the records in a form and replace a number field until it hits a record with a specific entry.
I am unable to find the command in the macro drop-down box. The command I am looking for is Do Until.
I would prefer to work through macros rather than in VBA as I don't know how to program using VBA


Access 2007 - Macro trouble coverting
someone know if there is a trouble or a bug when you try to convert a macro to a VBA? Because I tried many times, save before, another macro, but I always get a message saying that can not find the macro.


Code to delete records in a Table
Can somebody point me in the right direction
for some VBA code to delete all records
in a table. I know I can create a macro and call the macro,
but like to do it in VBA


Selecting a row's highest option and its associated code
I have an MS Access table that contains colour codes and their relevant percentage with reference to particular products. Each product can be made up of up to 3 colours and I am trying todetermine the most used colour for each product - so far I can determine the percentage of the highest colour (using a MaxOfList VBA module), but am unable to associate it with the actualcolour.

Table example:
...

So as an example, product 001 would return Col034, product 002 would return Col241 and product 003 would return Col360.

The colour codes are as they stand I.e. they do not refer to another table to return a 'real-world' colour, and there isn't a rule on the order by which the the codes should be entered. This is a fully normalised table.

Essentially I need something that performs a Vlookup on the Highest column, and returns the value which is 1 field to the left of the result.


List of a macro's commands in VBA?
When you open a macro in Design mode, you get the four columns, Macro Name, Condition, Action (with the parameter below) and Comment. Where is this list of actions in VBA? I've searched the database's Scripts container, the project's AllMacros collection, all properties for both -- nothing.

EVen when I leave a macro open in design mode and examine all these with the VBE'sWatch Window, nothing shows up.

The reason I ask is: I have a system to export VBA code to text files, overwriting only the ones that change, so that I can do meaningful version control. So modules' lines of code andeven a project's references are accessible via VBA -- but not macros' actions? Really?