-----Original Message-----
(comments interspersed with quoted text)
GaryS said:
Well, Dirk, are you sure you want this logic? Here it is,
although it would best be represented using a decision
table. (I STILL would like to know the basics of
sequential processing, since it's part of my Access
learning program.)
That's fine, but it's important also to learn that relational database
systems are primarily geared toward set operations -- that's what SQL is
all about, really -- and whenever you are faced with a choice between
sequential record processing and doing the whole thing with a SQL
statement or two, the SQL approach will almost always be faster and more
efficient. I spent 20 years doing procedural programming, and I still
often find myself writing procedural code where a couple of SQL
statements would do the job.
The label printer is a specialized printer for labels to
be affixed to plant tags.
Its layout can either be 2 or 3 lines, depending on the
contents of the 4 fields that need to be printed (genus,
species, variety, and most frequent common name).
The goal is to use 2 lines whenever possible, since the
font can be larger and therefore more readable from a
longer distance away. (The plant tags will be read by
people on a garden tour.)
There are 3 cases, dependent on the contents of the GSVC
fields, where 2 lines (and hence a larger font) can be
used, and 3 cases where 3 lines must be used.
Is there some mechanism for switching the printer from large font to
small font on the fly? A control code or escape sequence, perhaps? If
you're sending a print file to the printer, and some of the "pages"
(labels) to be printed can be in a large font while others must be in a
small font, can the printer driver determine this on its own, or do you
have to tell it somehow? If so, how?
I could give you those business rules if you want. But
the possibilities include examining the LEN(genus &
species), whether any combination of species, variety, or
common name is null.
I've laid out the decision table for those rules. The
output of this procedure will result in either combining
genus and species into one line (field), keeping them as
separate lines (because together they are too long to
fit), deciding which field should go into line 2 (field 2)
(which could be almost any of the fields except genus),
and deciding whether or not a 3rd line is necessary based
on length and nullity of the variety and common name.
It is much easier to program this in Access than in the
label driver software. The latter CAN select a layout
consisting of 2 lines of large font or 3 lines of smaller
font based on what is exported to it, but it is not
possible (or easy) to examine individual field contents
and perform moving and concatenation.
IOW, I have already laid out the logic for all this string
manipulation and determined that it would be *much easier*
(and more transparent to a maintenance programmer) to do
it programmatically and sequentially rather than try to
construct some obscure but tricky set of queries in SQL!
Hope this sheds some more light on the situation and hope
someone sees the need to address my original 5
questions.
This is what I think I would do, subject to a better understanding of
the capabilities and requirements of this printer. I would write a
function that would receive as arguments the four GSVC fields and return
a string containing the formatted entry to be printed, consisting of 2
or 3 lines separated by new-line characters (the standard carriage
return/line feed combination). Thus, this function would embody all
your business rules and string manipulation, operating on the fields of
one record at a time.
Suppose that function had a signature like this:
Sub fncFormatGSVCForPrinter( _
Genus, Species, Variety, CommonName) _
As String
Then I would create a report, designed for the label printer, with a
recordsource query like this:
SELECT
fncFormatGSVCForPrinter(
[Genus], [Species], [Variety], [CommonName])
AS GSVC
FROM PlantData;
I'm assuming that [Genus], [Species], [Variety], and [CommonName] are
fields in the source table, which I'm calling "PlantData". If the
report needs to be sorted, any fields required for sorting it would also
have to be included in the SELECT list. In the report design, the label
content would then be represented as a single text box bound to the
calculated field [GSVC]. Generating the labels would then be a simple
matter of printing the report.
I can foresee a possible hitch in this approach. That is if you need to
send a special binary escape sequence to the printer to switch fonts
between 2-line and 3-line entries. In that case, I think you really
will have to process the records and send both text and control
sequences to the printer on a record by record basis (though you could
still use the fncFormatGSVCForPrinter() function to encapsulate the
formatting logic). On the other hand, if there is a control or escape
sequence that can be embedded in the text sent to the printer, you could
have the function itself create that sequence and embed it in the
returned string, or else you could have VBA code in the report's
Detail_Format event that modifies the FontSize property of the text box
depending on how many lines are in it. This last actually sounds to me
like the most likely way to do it -- that should allow the printer
driver to do its thing without you having to know what the control codes
are.
Note, by the way, that if you (wrongheadedly, IMO) wanted to save the
output of fncFormatGSVCForPrinter() as a separate field in each record,
you could do it using a simple update query that calls the function;
like this:
UPDATE PlantData
SET GSVC =
fncFormatGSVCForPrinter(
Genus, Species, Variety, CommonName);
where GSVC is a text field you have previously defined in the table.
The update query, in turn, would be more efficient than the following
code using sequential recordset processing:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("PlantData")
With rs
Do Until .EOF
.Edit
!GSVC =
fncFormatGSVCForPrinter( _
!Genus, !Species, !Variety, ! CommonName)
.Update
.MoveNext
Loop
.Close
EndWith
Set rs = Nothing
But you should not be storing calculated data in the table, in the first
place.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
