Hnext Record in Accessw to Read Records in Access Macro
Creating a macro:
Starting with the basic I am going to create a macro that merely 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 push button on the toolbar where yous see Open up, Blueprint, New. When the macro screen comes up, I go to the Action column and select a macro for something I want to exercise. 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 upwards the results of the majorlink query. Note that when you become to the Macro choice in the Database window, there is no a Run button. You tin 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 footstep to the macro. Commencement highlight the macro and 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 Notice 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 information will be highlighted and the cursor will indicate to that tape.
At present I created a form using the Sorcerer and using the majorlink query equally the subject of the form. And so I created a macro that will open this form in read only mode and move to the last tape. The ii commands in the macro are shown in the images below.
A macro is but a sequence of actions that the programmer puts together to perform a chore. In the last example, the chore was to open the grade and become to the final record. Most advanced Access applications will use VBA to automate a task, macros accept their place in automating the processing of simple directly forward tasks.
Looking at the Macro form, the first column beneath, the first column says MacroName. Basically when you put a name here, Access creates a macro group meaning that the macro proper name is one of the macros, if another name is used it is another macro. The Condition column allows you to identify a condition in the column that will be tested. I am testing to see if the land 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 institute that I needed to give the total definition - and in fact I frequently observe this is true so I am in the addiction of using it all of the fourth dimension. 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 concluding line is shown on the next page. Note the ellipsis in the line that called for the shut. This means if it is truthful, this line will be executed. In my example, if the null condition that I am testing is truthful than I close which means the form disappears. Note that I do not accept a looping activity hither, I need to test each record individually - not very practical.
Finally I will prove running the macro with a country that is null.
Now I desire to automate this process then I am going to put a push on the grade that will execute the macro CheckState. That means that the person looking at records or creating records can printing that macro and find out if the state is blank (in case they can't meet 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 push on the form would automatically activate the sorcerer. 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.
Once again please note that the wizard push right nether toolbox is selected when I first bring over the button!
The next screen will prove a list of macros. I selected the CheckState macro from the list. The side by side screen lets me pick a picture or text on the button. I selected text and entered the text Check Country. I and then named the macro StateButton. When I clicked on the push button when the state was in that location, 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 prepare a macro that will allow me to search for something - I decided to use city. Get-go I needed to create a query of distinct cities so I would take 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 grade 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 philharmonic box over, I fabricated 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 tabular array or query. On the next screen, I selected query and selected the query that I created with the distinct cities called DistinctCity. On the adjacent screen, I brought over the city, which is the only field in this query. I so 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 call up for later use. On the next screen, when asked for a proper name, I entered City Select. Note that I am going to have to put ii action buttons on this form to really execute the search and to cancel. I decided to start writing the macro first and add the buttons when I got to them. Check beneath to come across the course after I have added the buttons.
I am now going to showtime writing the macro to use this form. Commencement I want to open the grade that I volition use to select that I will employ to select the metropolis and run the macro to locate the city. The beginning of the macro will do that. Find that I am defining a Macro called FindCity which in fact is going to exist a group macro made upwardly of iii different submacros. This gives me the opportunity to group all of the actions related to finding the urban center together. The OpenCityDialog is going to open the dialog box where the user will select the city they desire to run into. The dialog box is actually the form that I started to develop above, called CitySelect Box.
First I decided to deal with the fact that they might not select annihilation from the combo box, so I decided to ask the IsNull question nigh the contents of the combo box. If the box is empty, I volition beep at the user and cease the macro. The form volition stay open up and then they can again endeavour and make a selection from the philharmonic box. Assuming they make the selection, then I want to hide the grade with the combo box and motility the focus to the form where they volition run into the urban center, and so select the urban center field. At that point I need to sort the records by that field and then move to the record that contains the first urban center that matches to the selected in the combo box. Offset I will prove yous the entire macro and the forms, then I volition prove you the footstep past footstep code for the macro.
To run the macros above, the user would click on Detect City in the student00 form. This executes the OpenCityDialog macro which brings upwardly the CitySelectBox. The user would then choice a city and assuming they desire to practice the search select Exercise Search (cancel can be used if yous decide non to exercise a search). Do Search executes the macro named DoCitySearch and Cancel executes the macro named CancelSearch. The DoCitySearch macro volition locate the urban center specified and show the start record with that city on the student00 course.
Now we will go through these 2 macros 1 line at a time. (Note for complete comments you will accept to await at the database). The first 2 commands under DoCitySearch deal with the situation where no city was selected. I am checking to run into if the field containing the city , called PickCity which is on the form CitySelectBox and therefore clearly is a Forms object, is null. If information technology is I will Beep. On the next line the ... means this is what to practise if the condition above is true, and since I desire 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 tin try again.
Next, lets look at SetValue which hides the form. At this point, we know that the PickCity is not empty and and so nosotros want to keep with the search.
The SetValue action has entries in the Activeness Arguments that specify what is to exist done. In this example we are talking about the attribute of visible on CitySelectBox and nosotros are setting that attribute to False. That means that the grade 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 pupil 00.
The next control is GoToControl where we plant the metropolis column as the column that we volition utilise to do the sort.
Next nosotros will await at RunCommand which really executes the sort on the city column. The actual control is SortAscending.
The next command is to find a tape. This uses the FindRecord command with the opportunity to prepare several Action Arguments. The first is in response to the Detect What, I entered =[Forms]![CitySelectBox]![PickCity]. PickCity is the proper name of the combo box on the CitySelectBox form which is where the use selects the metropolis to look for. This selected metropolis will and then be compared to the city on the form (which shows the data on the table). The next field says Lucifer. I selected WholeField, but the user can select to look at the first part of the field, or to expect for the data anywhere in the field etc. The other options I accustomed the default, which was to ignore example, search all the records, look at the selected field, which is city, and find the kickoff record. The search as formatted means search the fashion information technology is formatted on the form or search equally divers in the table.
Finally the Close command is used to shut the dialog box grade.
The cancel macro has just two commands. The first closes the dialog box - it is identical to the picture to a higher place. The 2d 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 bachelor.
Now nosotros demand to go back and look at the buttons that were set upward on the dialog box form. I brought a button over (with the magician active meaning the wizard button on the top line of the toolbox was selected). And then I selected Misc and Run Macro.
The side by side screen gave me the option of macros bachelor, I selected the macro that is used to practise the search from the group macro that I have fix.
The next screen asks me to pick a movie or name the macro - I named it &Do Search. The & means that the D is a shortcut. I followed similar steps to exercise the Cancel - yous can see in a higher place FindCity.CancelSearch which I used for that. At present when I click on the form to Find City, the dialog box comes up and I can cull to do the search or abolish.
Source: http://www.bristolcc.edu/pgrocer/cis61/noteswithimages/macrointro.htm
Post a Comment for "Hnext Record in Accessw to Read Records in Access Macro"