To build SQL statements in VBA, you should usually load them into string variables by concatenating various phrases together. Some of the phrases will be exact SQL text that you will supply, while others will be the contents of variables in VBA or controls on forms or reports. When the SQL statement is complete, you'll be able to use it in queries, in the recordsource of forms or reports, or in the rowsource of combo boxes or list boxes. This will allow you to deliver power and flexibility in your Access applications.
The first thing to learn about building SQL statements in VBA is handling concatenation and quotes. They may seem simple, but many programmers have stared at VBA strings with multiple nested quotes and struggled to make them work.
Consider a SQL string that selects a record for a particular business from a table of businesses:
Select * From tblBusiness Where BusinessKey = 17
In actual usage, we need to replace the 17 in the above statement with the BusinessKey that the user is currently working with. To build this SQL statement in VBA, using the BusinessKey from the current form, you would use something like this:
strSQL = "Select * From tblBusiness Where BusinessKey = " _ & Me!BusinessKey
One reason this is so simple is that BusinessKey is a numeric value. In SQL, numeric values are just stated, without quotes around them. This is great for primary key values, which are often AutoNumbers ...