Replacing Access query with SQL Select VBA?

[Corta]

I am in the process of extending my Access 2007 knowledge and
hopefully VBA skills. I am reviewing samples, reading many of the
posts on this group, etc. In the long run I need to learn VBA inside
and out based on my professional goals so I am trying balance between
learning the basics of the language and working through examples. So
I have two questions.

1.) What is the best online reference for the VBA language for Access
2007?

2.) In my test database a have several tables and made a query
selecting the information I want based on my criteria for a special
report that I developed. The query and subsequent report all work as
designed. I want to stretch my skills by using the SQL select code
generated by the query by way of attaching it to a command button on a
form. Functionally I understand that I need to select the data via
the SQL statement and I need to call the report I created for the
query when I select the command button. I just need help getting
pointed in the right direction with the VBA code.

Thank you for your time and assistance.

Corta

 

[Albert D. Kallal]

1.) What is the best online reference for the VBA language for Access
2007?

The built help is likely he best reference you have.

Just remember to launch help while in the code editor...not the forms etc.

On the right side, you see a VBA reference, broken down by function etc.

However, asking for a list of food ingredients DOES NOT help you become a
better cook. While you do need and want a reference for the vba language (or
food), it not going to help your coding skills (or cooking skills).

However, an *excellent* place to find code examples, grouped by forms,
reports etc. is here:

http://www.mvps.org/access/

I understand that I need to select the data via

the SQL statement

No, not really. What you need to do is provide a filter, or what is called a
"where" clause to restrict the sql already in place for that report. So, you
don't generate the sql in code, nor do you modify the sql in code. You
provide a "where" clause that the report will use for its criteria.

So, the normal approach is to build a un-bound form (a un-bound form is a
form that is NOT attached to a table - these forms are typically designed
for user interface face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKallal/ridesrpt/ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:


dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <> "" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For a date range, we could put two calendar controls on the screen. The code
could be:


dim strWhere as string
dim strStartDate as string
dim strEndDate as string


strStartDtae = "#" & format(me.StartDateContorl,"mm/dd/yyyy") & "#"
strEndDate = "#" & format(me.EndDateContorl,"mm/dd/yyyy") & "#"

strWhere = "InvoiceDate is between " & strStartDate & " and " & strEndDate

docmd.openReport "InvoiceReport",acViewPreview,,strWhere

Note the above command does not modify the reports sql, but simply provides
the "where" clause.

You can and will do the same for forms.