Sorting, filtering, wrapping

[Guest]

I have a query based on the Employees table. The query is sorted by
LastName, and is used as the record source for an Employees form. One of the
fields is a check box bound to a Yes/No field named Inactive, which is used
to indicate employees who no longer work here, but who appear in records we
need to keep. The form's Open event is:
Me.RecordSource = "SELECT * FROM qryEmployee WHERE Inactive = False ORDER BY
LastName"
I have added some buttons to the form to change the sort order to
Department, etc., using code in the Click event similar to the above.
One of the buttons is to show all employees, both current and inactive. I
do this with Me.RecordSource = "SELECT * FROM qryEmploye"
Other command buttons re-apply the Inactive filter. In most cased the
alphabetical listing will be used, but the others are necessary sometimes.
Now the questions. I started by filtering out Inactive employees with the
query, but could not figure out a good way to show all employees. Also, for
the query I used the asterisk, then added LastName for ordering and Inactive
for the criteria, but then in the VBA SELECT statements I needed to specify
the table for the Inactive field. Apparently it is either that or not use
the asterisk in the query design. Is one approach preferable to another
(filter by query vs. filter by code) in a situation like this? Are there
positives and negatives to using the asterisk in query design? If I filter
in the query is there a way to remove that filter in the form?
The SELECT statement is sometimes more complex than the illustration above.
I have been using strSQL = "SELECT * ...", then Me.RecordSource = strSQL, but
I cannot figure out how to wrap the SELECT statement in the code window. I
tried the usual things for a text string (underscores, concatenation), but it
didn't work. I'm probably missing something simple, but those are sometimes
the hardest to see.

 

[Allen Browne]

The most efficient solution is to retrieve the fewest number of records
possible, and the fewest number of fields you need, and to place an index on
any fields commonly used in filtering (e.g. Inactive) and sorting (e.g.
LastName).

If possible make the SELECT statement from the table, rather than from
another query.

This example shows how you can wrap your SQL string onto multiple lines:

Dim strSql As String
strSql = "SELECT Employee.* FROM Employee " & _
"WHERE (Inactive = False) " & _
"ORDER BY LastName, FirstName;"
Me.RecordSource = strSql

 

[Guest]

Thanks for your reply. When you say that the most efficient method is to
"retrieve the fewest number of records possible, and the fewest number of
fields you need" are you saying I would be better basing the form on a query
or on a table? I need all of the fields on the form. The query's function
was sorting by last name. I am now doing that with VBA, and the form is
based on the table. I indexed the suggested fields, but I've never quite
understood indexing.
The wrapping finally worked when I placed a space between the end of the
line and the quote mark. When the last letter and the closing quote on a
line were right next to each other it didn't work. Curious.

 

[Allen Browne]

Microsoft has programmed Access so that it works *very* well even if you
bind the form to the table, provided the table has a primary key. So that's
not something to worry about. The query will be more efficient, though,
where you want only a small number of records from a large table, or where
you want them sorted. Not sure how you are doing the sorting with VBA:
perhaps you have set the form's OrderBy property? The query would be better
(though the difference is academic if the table has only a few thousand
records.)

The spaces at the end of the line is important because otherwise the words
run together. Access can't make sense of:
SELECT ... FROM EmployeeWHERE ...
You could also add line endings to the string:
strSql = "SELECT Employee.* FROM Employee " & vbCrLf & _
"WHERE (Inactive = False) " vbCrLf & _
"ORDER BY LastName, FirstName;"

Indexes are really important. Before libraries had computer terminals, how
did you find a book you wanted? Your options were:

1. Walk through the entire library, looking at the spine of every book until
you find the one you want.

2. Understand the system used to store the books, such as the categories of
the Dewey system, so you could get into the right area, and then scan
through the books in that area.

3. Look up the index cards, to find the book. Typically the librarian
maintained three sets of cards: by author, by title, and by subject.
In a database, scanning all the records in a table to find the ones that
match is called a table scan.

Of course, the computer is enormously faster than scanning a physical
library, but the table scan is still a slow way to match records in a
database.

Method 2 above does not apply to databases. According to relational theory,
there is no physical order to the records in the table, i.e. the rows are
unordered.

Method 3 is by far the most efficient. In database terms, an index is a set
of pointers to each record. You index a particular field - like the Author
or Title of the book - and the database maintains the index and uses it to
find the record instantaneously when you search on that field.

Therefore, you want to index any field used for sorting, or for criteria,
unless that field is a foreign key to another table. Access automatically
indexes the foreign key when you create the relationship with enforced
referential integrity.

HTH

 

[Guest]

Thanks again. As it happens this particular database is for employee records
at a medium (?) sized company (about 75 employees), so indexing is not an
issue, but several databases could become much larger, so I will be vigilant
about indexing. I sorted with VBA by using Me.RecordSource = "Select * ..."
in the form's Open event, which of course causes the form's record source to
be that same statement. Perhaps that is just using a query, except the query
doesn't have a name. I prefer to use named objects because it is easier on
my brain, but I am beginning to doubt there is any functional difference
between a SQL statement in the form's record source property or a named query
that does the same thing. I'm not even sure I am using the right words.
Anyhow, I went back to using a named query.
The concatenation makes perfect sense now that you've explained it. I
should have been able to see that.
One more question, which is a bit of a departure from the previous part of
the thread, but here it is. When I have parameters for a report I prefer to
use a form to specify the parameters (rather than the built-in dialog boxes).
However, for some reason I learned to use a slightly different method that
seems to be standard in that the command button for the report really does
open the report rather than the parameter form. The report's open event
opens the parameter form:
DoCmd.OpenForm "frmMyForm", , , , , acDialog
After entering the parameter, a command button on the parameter form hides
(not closes) the form. The report's Close event closes the form.
This had some advantage in some situation that I can't recall, but it has
the disadvantage of making it awkward to close the parameter form in a case
where the user decides not to run the report after all. Docmd.Close for the
parameter form brings up the standard parameter dialog box (since the report
is still in the process of opening, I guess). Should I just bail out of this
method and go back to the more conventional way of opening the parameter form
first, or can I overcome the difficulty of closing the parameter form before
the report opens?
I won't be returning to this thread until Monday, I expect, so if you
respond and I don't reply at first please don't think me rude. You have been
a great help, and I really do appreciate it.

 

[Allen Browne]

Regarding your new question, use what suits you.

Personally, I always have a form open already that is used to launch the
reports (since I don't let end users anywhere near the Database window), and
so it makes sense to let the user enter their limiting parameters on that
form (rather than pop up another one in Report_Open), and that does avoid
the problem you describe as well. It is also more flexible (does not have to
be modal to hold up the report's Open event.)

 

[Guest]

It was never a question of letting end users near the database window.
Opening the parameter form first does solve a few difficulties I had been
having. I can't remember why I started doing it the other way, but I expect
it was to solve a particular problem and then I stuck with doing it that way
for no reason other than habit.
Thanks again, Allen, for your help. The database is running smoothly, and
the current difficuties have been eliminated.