Re: Sorting, filtering, wrapping

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.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BruceM" <BruceM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:24F617A7-B13E-40F9-B070-55BC8F54D009@xxxxxxxxxxxxxxxx
> 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" wrote:
>
>> 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
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "BruceM" <BruceM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:35193372-11D4-4F37-90DA-7158972D2A51@xxxxxxxxxxxxxxxx
>> > 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" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >> "BruceM" <BruceM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:861C0961-C8EE-4189-8218-87A5FF693A79@xxxxxxxxxxxxxxxx
>> >> >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.
>>
>>
>>


.

Quantcast