In this article I will continue the subject concerning VBA and MS Access. I have thought of a rule of a thumb, presenting only 3 macros per article, thus being sure that the reader does not get bored.
Here I will try to show a few useful VBA Macros, which can be used in MS Access. The Macros, used in MS Access are different from the one used in MS Excel, because in MS Access the objects are different and thus they require their own libraries and etc. Nevertheless, the logic is the same, as far as the language is the same.
So, here is how man can open MS Access databank and find a record in it through VBA. For this example I will use the database, which I have created for the SQL lesson here. The database example is provided at the end of the lesson. Here is the code, which opens the table “Professor” and selects professor “Urlauber” through a search.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub OpenTable() On Error GoTo ErrorCode DoCmd.OpenTable "Professor", acViewNormal DoCmd.FindRecord "Urlauber", acEntire, True, _ acSearchAll, True, acAll Exit Sub ErrorCode: MsgBox "The table cannot be opened! Sorry!" End Sub |
As far as always something may go wrong (this is VBA and Microsoft, after all!) I have provided possibility to display an modified message with the syntax “On Error GoTo …”. The code works pretty well.
Another way to open a table and to select a specific line in the code is provided through the command “DoCmd.GoToRecord acDataTable, “Student”, acGoTo, 2″. With this command we tell MS Access to go to the data table “Student” and to select the second entry there. The code consists one more entry, openning the code:
|
1 2 3 4 5 6 |
Sub OpenAndSelectNumber3() DoCmd.OpenTable "Student", acViewNormal DoCmd.GoToRecord acDataTable, "Student", acGoTo, 2 End Sub |
An interesting possibility in MS Access is the possibility to provide filters(queries) through VBA. As far as this is really an easy task in MS Excel, where anyone can just record the macro for the filters, in MS Access the VBA enthusiast should know the commands. So, this is an example, which filters the students”Fichte” and “Jonas” from the table “Students”.
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub OpenWithFilters() On Error GoTo ErrorCode DoCmd.OpenTable "Student", acViewNormal DoCmd.ApplyFilter , "Namee='Fichte' OR Namee='Jonas'" Exit Sub ErrorCode: MsgBox "The table cannot be opened! Sorry!" End Sub |
So, once you are aware with the libraries in MS Access you will see all the possibilities of this tool in combination with VBA.
In order to make the MS Access file a little more user friendly, I have added a Form, with three buttons, associated to each one of the macros. Thus, you should just open the form and press some of the buttons to run the macros.
Enjoy the code as you wish!