Access VBA Programming - How to Use SQL and VBA in Forms to Retrieve Data

If you have been using Access for a while, you may be familiar with SQL. SQL (Structured Query Language) is the language used to manipulate the records in your database. It is the language behind the queries and the recordsets. In fact, when you built a query in Access, you actually use a SQL statement.

You can also use SQL statements in VBA (Visual Basic for Applications) programming language. VBA and SQL work very well together and are powerful tools to use in your Access applications. With them, you can add more functionality to your programs.

Sometimes it is useful to limit the number of records that are displayed on a form, based on certain criteria. You may also want to sort the information on a column.

Access VBA Programming - How to Use SQL and VBA in Forms to Retrieve Data

How can you accomplish this?

First, you open a form in design view. Then bind a query to this form. Let 's say you have a query named qryCustomers, which contains the names of your customers and the names of the cities where they reside.

Next, you define which records from this query will be selected when the user clicks an option on the form. For example, if the user can choose from three options, you write a Select SQL statement with three different Where and Order by clauses to retrieve the data.

In addition, you put an option group with three option buttons on the form. Every option will select another set of records to be displayed on the form.

Finally, assign a name to the option group control. Right-click the control and from the menu choose Build Event. Then from the Builder menu, choose Code Builder. This opens the VBA editor. Select the After Update event. Now put the SQL statements in the code module.

First, define two variables to compose a SQL string.

Dim strQuery as string
Dim strWhere as string
Next, put a select statement in a string to retrieve all the records from your query.
strQuery = "Select * From qryCustomers "
Now write a Where clause for every option. For example, you want to view only the customers based on a city and sorted by name.
Select Case Me!OptionGroupName
Case 1
strWhere = "Where City = 'New York' Order by Name"
Case 2
strWhere = "Where City = 'Washington' Order by Name"
Case 3
strWhere = "Where City = 'Atlanta' Order by Name"
End Select
Finally, bind the string to the form.
Me.Recordsource = strQuery & strWhere
Me.Requery

When the form is open and the user selects an option, the after update event of this control is triggered and the record source of the form is changed. The form is reloaded with the resulting details, based on the option that was clicked.

Access VBA Programming - How to Use SQL and VBA in Forms to Retrieve Data
Check For The New Release in Health, Fitness & Dieting Category of Books NOW!
Check What Are The Top Cooking Books in Last 90 Days Best Cheap Deal!
Check For Cookbooks Best Sellers 2012 Discount OFFER!
Check for Top 100 Most Popular Books People Are Buying Daily Price Update!
Check For 100 New Release & BestSeller Books For Your Collection

Tom Vanhasselt is a MS Access developer with over 15 years of experience. If you want to acquire the necessary programming skills there are of lot of free courses and sources on the internet. A point of reference could be the Microsoft Developer Network (http://msdn.microsoft.com).

watches mobile phone Buy 2008 2010 Mitsubishi Lancer Without Turbo Radiator Best Price Art S8 Balanced 8 Channel Microphone