Using variables to make code faster and more efficient?

[Emily T]

I have a dialog form that asks users for query criteria. It works but it
takes a long time to run. How can I use variables to make the code below
more efficient? (I think Dim has to be used, but I am not sure how to relate
it to my code below.) "Country" and "Program" are text boxes.

Private Sub Form_Load()
Country.Visible = False
Program.Visible = False
End Sub

Private Sub Search_Options_Click()
If Search_Options.Value = 1 Then
Country.Visible = True
Program.Visible = False
Program.Value = ""
ElseIf Search_Options.Value = 2 Then
Program.Visible = True
Country.Visible = False
Country.Value = ""
End If
End Sub

Thank you.

 

[Ken Snell \(MVP\)]

The time factor likely is unrelated to your code. Rather, it's more likely a
function of the form's data taking a long time to load. What is the form's
RecordSource table/query? Does the query or table contain a lot of data?

Speeding up the opening of the form (I assume this is the problem?) would
involve making some changes to the data set that the form contains. Post
back with more details and we can assist you with this.

 

[Emily T]

Yes. One of the three tables that the query references has 890,000 rows and
15 columns. In the query design view, I use 5 rows of "Criteria" to give the
user differnt search scenarios.

The second reason I want to use variables in the form code is that when I
try to bring up the query's report from the form I get an error message
saying the operation is too complicated and that I should declare variables.
Currently in order for me not to get that error message I have to include
only:
textbox.visible = false OR textbox.value = "". Access won't let me include
both the "visible" and "value" statements.

Thanks for responding!
Emily

 

[Ken Snell \(MVP\)]

Please provide details about what the form is doing and how you've designed
it, and what you mean by "bring up the query's report from the form". We
need more information in order to assist you with this issue.

 

[Emily T]

The form allows user to type in query criteria, click on the ok button and
then see a report based on what the user searched for. For example type
"Germany" in the country text box and a date range in two date text boxes.
User would then see a report from the query that only pertains to Germany
records for the date range specified. User can also search by
"program" and "date range"
"item" and "date range"
or only "customer number" without a date range.

There are two option groups: one to select the region (e.g. Europe or Asia)
and a second one in which the user selects what they are searching. For
example, click on option button one if user is searching by "country" and
"date range" or click on option button two if searching by "program" and
"date range", etc.

The tricky thing is that if a user plans on searching only by customer
number and types something in the "customer number" text box BUT then changes
his/her mind and clicks on the "country" option button to search by counry, I
want the "customer number" value to be cleared. (If it is not cleared and
the user clicks on the "OK" button to run the query and bring up the report,
no results would be returned because I did not set up a criteria in the query
to search by "customer number" and "country".

It is an unbound form.

 

[Albert D. Kallal]

For performance reasonse, make sure there is an index on the several fields
that you plan to seach on.

Next, remove ALL critera from the actual query...espically if your using
expresions to check for blank values.

Then, build the seach critera in code and pass it to the reprot as an
"where" clause.

BUT then changes
his/her mind and clicks on the "country" option button to search by
counry, I
want the "customer number" value to be cleared.

Use the county text box on enter event to clear out the customer number

eg:

me.CustomerNumber = null

The following info will give you an idea about how to make a "where" clase
in code....

Putting forms! expression in queries can get really messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignored in the criteria.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

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, BUT 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.

Take a look at the following screen shots to see what I mean:

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

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.

 

[Ken Snell \(MVP\)]

I have a sample database that shows how to create SQL statements in VBA
code, based on the values in various types of controls.
http://www.accessmvp.com/KDSnell/SampleDBs.htm#FilterForm

Although this sample shows how to open a report that is based on the
code-created SQL, the code is easily modifiable to assign the generated
WHERE clause to the form itself.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Emily T]

I went to "Query Design View" and then "SQL View"; can I just copy that SQL
code and paste it into my form's VB code?

If so would the SQL code be in the "Private Sub Form_Load()" section of the
VB code?

For performance reasonse, make sure there is an index on the several fields
that you plan to seach on.

Next, remove ALL critera from the actual query...espically if your using
expresions to check for blank values.

Then, build the seach critera in code and pass it to the reprot as an
"where" clause.

BUT then changes
his/her mind and clicks on the "country" option button to search by
counry, I
want the "customer number" value to be cleared.

Use the county text box on enter event to clear out the customer number

eg:

me.CustomerNumber = null

The following info will give you an idea about how to make a "where" clase
in code....

Putting forms! expression in queries can get really messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignored in the criteria.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

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, BUT 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.

Take a look at the following screen shots to see what I mean:

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

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.

 

[Albert D. Kallal]

I went to "Query Design View" and then "SQL View"; can I just copy that SQL
code and paste it into my form's VB code?

If so would the SQL code be in the "Private Sub Form_Load()" section of
the
VB code?

My suggestion was to leave the form bound and ONLY change the "where" clause
used to open the form.

however you *can* stuff the sql right into the form via code (and, yes you
can use the on-load event).

eg in on-load you can go::

me.RecordSource = "sql goes here"

However, as general rule you not likely changing the table the form is based
on. so, I MUCH prefer passing the "where" clause to the form, as then often
you can use the same code/where clause to open a report also (it can save
coding over time).