How do I create a form to run macros

Posted on 2012-08-10
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 65

    Expert Comment

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

    This quesiton sounds very generic.

    Author Comment

    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
    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.


    Author Comment

    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

    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

    Not to worry, 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

    Yeeey!  Thanks!  it worked perfectly.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    730 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now