How do I create a form to run macros

Posted on 2012-08-10
Medium Priority
Last Modified: 2012-08-13

I am creating a form to run two macros.  The first one has three prompts that need to be answered before it completes; all three answers to the prompts are the same because it's running append queries.  I want to, someway, create a text box where the user will enter the answer once and it will update all three.

I hope I am making some sense.  Any help would be greatly appreciated.  Also, I'm kind of new to Access 2007, or DB in general.

Question by:lpsoto63
LVL 66

Expert Comment

by:Jim Horn
ID: 38281818
Have you started on this, and if so where are you at?

This quesiton sounds very generic.

Author Comment

ID: 38281887
Yes, I have.  My first macro runs one create table query and two append queries.  This information is gathered from our server for payments made to vendors and need to be for a specific date.  Each of the queries requires that we enter the date.  

Instead of having to enter the date three times, I would like the form to have a place to enter the date once and the macro would derive the information from this.

I hope I'm makin some sense.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38282195
1. Can you post the SQL behind these queries?

2. Is using a Macro a hard requirement?
You can do this quite easily with VBA

Here is a sample.

In my code you would use the syntax for the date variable in all three queries.

Play around with it and see if you can adapt it for your database.

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.


Author Comment

ID: 38282263
Hi Boag2000,

I am brand new to this program.  I don't know where I would get the SQL.

maybe if I give you more information you might be able to help me.

I pull data from our main database that stores all payments made since 2007.  I have to make sure I only pull information for the current month.  In addition, I pull any accruals entered and payments other than rent made on the same check; all from the same database.

From the first query I made a table and added a column "Type" which allows me to identify whether the payments were for rent or other, or whether the entry is an accrual.

Once I have run the three queries, I ran a crosstab query so that all payments and accruals  are listed by vendor, with their appropriate breakdown.

The reason for the macro is to see if I can simplify the input steps but now I'd like to have just one place were the user may go and request the information with minimal prompts.

Sorry about such a long explanation but I have no idea how to explain it otherwise.

LVL 29

Expert Comment

ID: 38283832
In the criteria for your queries where you prompt the user, add a reference to the textbox in your form instead.

For example if you have a criteria for the Type column such as [Enter Type], change that to [Forms]![NameOfYourForm]![NameOfYourTextbox]
LVL 74

Accepted Solution

Jeffrey Coachman earned 2000 total points
ID: 38284485
Not to worry, ...my post was just a suggestion if you could use code/SQL

It seems like IrogSinta has a good grip on what you need, ...I'll let you continue with him.



Author Comment

ID: 38288637
Yeeey!  Thanks!  it worked perfectly.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question