Creating a macro:
Starting with the basic I am going to create a macro that simply executes a query that I have already designed and saved. To do this I first click the Macros button on the object bar and the I click the New button on the toolbar where you see Open, Design, New. When the macro screen comes up, I go to the Action column and select a macro for something I want to do. I selected OpenQuery, put a comment in the comments area, and selected the name of a pre existing query called majorlink. The View and DataMode were the defaults already in place. When I execute this macro, it brings up the results of the majorlink query. Note that when you go to the Macro selection in the Database window, there is no a Run button. You can either select your macro and click the run button or you can double click the macro to execute it.
Now let's say that you want to add another step to the macro. First highlight the macro and then click on design on the top bar. The design above will appear on the screen. For this macro, I selected FindRecord and entered the 4444 in the Find What field. All other fields were left as they appeared with the FindRecord action.
Note that when the output from this macro is shown, the designated data will be highlighted and the cursor will point to that record.
Now I created a form using the Wizard and using the majorlink query as the subject of the form. Then I created a macro that will open this form in read only mode and move to the last record. The two commands in the macro are shown in the images below.
A macro is simply a sequence of actions that the developer puts together to perform a task. In the last example, the task was to open the form and go to the last record. Most advanced Access applications will use VBA to automate a task, macros have their place in automating the processing of simple straight forward tasks.
Looking at the Macro form, the first column below, the first column says MacroName. Basically when you put a name here, Access creates a macro group meaning that the macro name is one of the macros, if another name is used it is another macro. The Condition column allows you to place a condition in the column that will be tested. I am testing to see if the state field is null in my example below. My understanding is that I would not need the bang notation (the notation with the !) since I was using a specific form and the fields would be easy to identify. I found that I needed to give the full definition - and in fact I frequently find this is true so I am in the habit of using it all of the time. The Actions are selected from the available list of Access macros.
I am showing each line of the macro in this example. The first two lines are shown in the screen above, the last line is shown on the next page. Note the ellipsis in the line that called for the close. This means if it is true, this line will be executed. In my example, if the null condition that I am testing is true than I close which means the form disappears. Note that I do not have a looping action here, I need to test each record individually - not very practical.
Finally I will show running the macro with a state that is null.
Now I want to automate this process so I am going to put a button on the form that will execute the macro CheckState. That means that the person looking at records or creating records can press that macro and find out if the state is blank (in case they can't see for themselves!!!).
First, I brought over a button from the tool box - I made sure that the wizard on the tool bar was activated so that the putting the button on the form would automatically activate the wizard. When the wizard showed the options, I responded to the question what I wanted to happen when the button, is pressed by selecting Miscellaneous and then selecting Run Macro.
Again please note that the wizard button right under toolbox is selected when I first bring over the button!
The next screen will show a list of macros. I selected the CheckState macro from the list. The next screen lets me pick a picture or text on the button. I selected text and entered the text Check State. I then named the macro StateButton. When I clicked on the button when the state was there, nothing happened. When I clicked on the button when there was an empty state, the macro was executed.
Searching for something macro:
I want to set up a macro that will allow me to search for something - I decided to use city. First I needed to create a query of distinct cities so I would have a list of the cities I needed to search for. I created a simple SQL query for distinct cities. Then I needed to establish a form that would allow me to do a search for a particular city using the cities in the distinct list. I started out by putting a rectangle on the form and then I brought over a combo box that I am going to link to the selected distinct cities. When I brought the combo box over, I made sure that the wizard on the toolbox was on. I then selected the option that said that I wanted the wizard to look up values in a table or query. On the next screen, I selected query and selected the query that I created with the distinct cities called DistinctCity. On the next screen, I brought over the city, which is the only field in this query. I then got a screen showing the cities and allowing me to make any adjustments. I did not want this field linked to the database, so I left the default, which is to remember for later use. On the next screen, when asked for a name, I entered City Select. Note that I am going to have to put two action buttons on this form to actually execute the search and to cancel. I decided to start writing the macro first and add the buttons when I got to them. Check below to see the form after I have added the buttons.
I am now going to start writing the macro to use this form. First I want to open the form that I will use to select that I will use to select the city and run the macro to locate the city. The beginning of the macro will do that. Notice that I am defining a Macro called FindCity which in fact is going to be a group macro made up of three different submacros. This gives me the opportunity to group all of the actions related to finding the city together. The OpenCityDialog is going to open the dialog box where the user will select the city they want to see. The dialog box is actually the form that I started to develop above, called CitySelect Box.
Now I will start to write the macro that will actually select the city. Once they have selected the city that they want to see from the combo box, they need to click on one of those buttons that I need to put on the form so that the search for the first record that has that city will happen.
First I decided to deal with the fact that they might not select anything from the combo box, so I decided to ask the IsNull question about the contents of the combo box. If the box is empty, I will beep at the user and end the macro. The form will stay open so they can again try and make a selection from the combo box. Assuming they make the selection, then I want to hide the form with the combo box and move the focus to the form where they will see the city, and then select the city field. At that point I need to sort the records by that field and then move to the record that contains the first city that matches to the selected in the combo box. First I will show you the entire macro and the forms, then I will show you the step by step code for the macro.
To run the macros above, the user would click on Find City in the student00 form. This executes the OpenCityDialog macro which brings up the CitySelectBox. The user would then pick a city and assuming they want to do the search select Do Search (cancel can be used if you decide not to do a search). Do Search executes the macro named DoCitySearch and Cancel executes the macro named CancelSearch. The DoCitySearch macro will locate the city specified and show the first record with that city on the student00 form.
Now we will go through these two macros one line at a time. (Note for complete comments you will have to look at the database). The first two commands under DoCitySearch deal with the situation where no city was selected. I am checking to see if the field containing the city , called PickCity which is on the form CitySelectBox and therefore clearly is a Forms object, is null. If it is I will Beep. On the next line the ... means this is what to do if the condition above is true, and since I want to do two things I used this ... followed by StopMacro which ends the macro. Note that the dialog box for selecting the city will remain on the screen. The use can try again.
Next, lets look at SetValue which hides the form. At this point, we know that the PickCity is not empty and so we want to continue with the search.
The SetValue action has entries in the Action Arguments that specify what is to be done. In this example we are talking about the attribute of visible on CitySelectBox and we are setting that attribute to False. That means that the form with the combo box will no longer be visible.
Now, the SelectObject which sets the focus to the form where the records with the selected city will be displayed - the form is student 00.
The next command is GoToControl where we establish the city column as the column that we will use to do the sort.
Next we will look at RunCommand which actually executes the sort on the city column. The actual command is SortAscending.
The next command is to find a record. This uses the FindRecord command with the opportunity to set several Action Arguments. The first is in response to the Find What, I entered =[Forms]![CitySelectBox]![PickCity]. PickCity is the name of the combo box on the CitySelectBox form which is where the use selects the city to look for. This selected city will then be compared to the city on the form (which shows the data on the table). The next field says Match. I selected WholeField, but the user can select to look at the first part of the field, or to look for the data anywhere in the field etc. The other options I accepted the default, which was to ignore case, search all the records, look at the selected field, which is city, and find the first record. The search as formatted means search the way it is formatted on the form or search as defined in the table.
Finally the Close command is used to close the dialog box form.
The cancel macro has only two commands. The first closes the dialog box - it is identical to the picture above. The second selects the object to receive the focus. I am selecting a form, specifically the student00 form and the No to In Database Window means the form is already available.
Now we need to go back and look at the buttons that were set up on the dialog box form. I brought a button over (with the wizard active meaning the wizard button on the top line of the toolbox was selected). Then I selected Misc and Run Macro.
The next screen gave me the choice of macros available, I selected the macro that is used to do the search from the group macro that I have set up.
The next screen asks me to pick a picture or name the macro - I named it &Do Search. The & means that the D is a shortcut. I followed similar steps to do the Cancel - you can see above FindCity.CancelSearch which I used for that. Now when I click on the form to Find City, the dialog box comes up and I can choose to do the search or cancel.