MS Access - Macros

Posted on 2012-09-22
Medium Priority
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

ID: 38425850
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

Bitsqueezer earned 1500 total points
ID: 38425952

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.



Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
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