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

103 7
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.
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.
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.
Subscribe to our newsletter
Sign up here to get the latest news, updates and special offers delivered directly to your inbox.
You can unsubscribe at any time

Leave A Reply

Your email address will not be published.