MS Access - Macros

Posted on 2012-09-22
Last Modified: 2012-09-23
I have designed a macro in MS Access and I have designed the first query called in the macro (see attachment) such that one of the fields in the macro prompts the user to enter a value. When I run the entire macro, the first shows up in the first quey, but it also shows up in the third query in the macro, which I designed not to have any prompt capabilities. Would anyone know the reason for the prompt coming from the third query?

Question by:maroulator
    LVL 29

    Expert Comment

    This image doesn't tell us anything useful that could point to the source of the problem.  It would be better to upload a stripped down copy of your database that includes just the necessary objects along with some test (non-private) data in order for us to see what's going on.
    LVL 24

    Accepted Solution


    as it seems that you are a beginner with Access I strongly recommend that you forget macros and learn VBA instead. That's not really more difficult than working with macros but you can do a lot more there and what you saw here: You can't even copy a simple macro source code to a forum. You will also not get much help with macros as there are only a very small number of people using macros.

    Your problem has nothing to do with the macro, if you design a query which prompts for a user input you probably have entered the prompt in the query editor below the field in the criterias. Probably you've copied the other query and you didn't remove the prompt.

    And the next recommendation here is: If you design a database which is for other users and not for your own use you should also forget the possibility to enter prompts at the criterias. This will open a simple inputbox which you cannot handle by code so you can't validate the input of the user or control it in any way. The correct way is to design a form which contains a textbox or any other kind of useful control for your input and then you can use the events which any control supplies like the "AfterUpdate" event or an additional button with the "Click" event to validate the input, supply the user with error messages which tell him the right way to input and which supplies a query designed with the "PARAMETERS" keyword (click the parameters button in the query editor to edit a parameter list) using a QueryDef object in VBA. This can then fill the designed parameters with the validated input, execute the query and open it. Here you should also not directly open the query like in your macro as the user never should get a query or table directly opened. Why? The same as above: Because the datasheet view of a directly opened table or query gives you no control over anything the user will do with it. He could for example delete any number or rows, edit anything he wants in any way he wants or add new data, nothing of that validated. The correct way is to use a continous form to display a query output, there you have control over anything the user will see and you can validate anything or lock things the user should not do like deleting records and so on. A continous form can also be switched to datasheet view if you prefer that view but here you have also the full control.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    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…
    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, when working with VBA, learn some techniques for writing readable and easily maintained code.

    759 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

    9 Experts available now in Live!

    Get 1:1 Help Now