Requirement: Visual Basic Knowledge
File to download: None
Related articles: Macros vs Visual Basic Modules, Using Switchboard in Microsoft Access for easy navigation, Use Input Form for Parameter Query, Hide Visual Basic Modules by saving your Access Files in MDE or ACCDE file format, Use Array to read data from or write data to Tables and Queries
Creating a query in Microsoft Access is simple and most people can learn to do it in a relatively short period of time. However, using query in Microsoft Access has two major drawbacks:
Please follow the below steps to convert a regular query to an embedded SQL query.
Step 1: Create a regular query in Microsoft Access.
Step 2: Click the “View” command on the ribbon and then choose the SQL View and you will see how the query look like in SQL coding.
Step 3: Copy the entire query using CTRL+A (select all) and CTRL+C (copy).
Step 4: If you want to create a new VB module, please click the “Create” command on the ribbon, then click the “Macro” and choose “Module” option to open the “Module”. You can also use an existing module, just open it and then put your SQL query in it.
Step 5: Create a new Subroutine in the module and paste the SQL query in it. In the below example, I created a new subroutine called “SearchByAcctNum()” and I pasted the SQL query in it. After you paste the SQL query, you will see that it is marked as red color because the VB editor regards your statement contains error in syntax. There are two reasons for the syntax error:
DoCmd.RunSQL (“SELECT ….. “)
Step 6: Once you are done with the subroutine and confirm that there is no error, you can put the name of the subroutine to the table of “Switchboard Items”. Your subroutine will be activated by the users when the users click the button on the Switchboard.
Step 7: Go to the “Switchboard Item” on the Tables through the navigation pane.
Step 8: In the Switchboard table, put the “8” under the “Command” field (to activate a VB Module) and put the name of the subroutine under the “Argument.
Step 9: If you create a new module, Access will ask you if you want to save the new module. Click “Yes” and give the new module a name and click “OK’ to save.