Skipping Over Used Mailing Labels

Suppose that you often use Access to print mailing labels on individual sheets. Each time you print a partial sheet of labels, you end up with some extra unused labels on the sheet. If you reuse that sheet of labels in the printer, Access will print right on the missing labels. Basically, you can't reuse a sheet of labels that's already partially used. That's not good because labels aren't cheap.

A solution to the problem is to pop up a dialog box like the one shown in Figure 8-5 just before Access is about to print the labels. There, the user can specify how many empty places are on the first sheet. Then the user clicks the Print button. Access prints the labels, skipping over the places left behind by used labels. No more wasted labels!

The solution to the problem requires a form and some VBA code. The form is needed because you need some way of telling the procedure how many labels to skip. In the example shown in Figure 8-5, the form itself is named Skip-LabelsForm. The control in which the user types the number of labels to skip is named LabelsToSkip. The form also contains a couple of buttons named CancelBttn and PrintBttn, to which you can tie code later. Figure 8-6 shows the exact name of the form and controls in Design view.

You don't need to type any code or create any forms if you just download

SkipLabels from www.coolnerds.com/vba.

LabelsToSkip SkipLabelsForm

Figure 8-6:

Names of some objects referred to in code.

LabelsToSkip SkipLabelsForm

Figure 8-6:

Names of some objects referred to in code.

CancelBttn PrintBttn

The procedure that you're about to create doesn't actually print labels. Your database needs a report format for that. You can easily create a report for printing labels via the Access Label Wizard. In Access, click Reports in the database window and then click New on the toolbar. Choose Label Wizard and choose the table or query from which the report will get names and addresses. Then click Next and follow the instructions presented by the Label Wizard.

For this example, I created a label format report named Avery 8462 Labels that's bound to a query named SkipLabelsSampleQry. However, you won't use those names in the VBA code because you want your SkipLabels procedure to work with any label-printing report, regardless of what table or query that report is bound to. So within the VBA code, refer to the report that prints the labels as ReportName and the reports underlying table or query as RecSource (see Figure 8-7).

ReportName

Figure 8-7:

Label report and record

B Avery 8463 Labels : Report

BiieHedo

Figure 8-7:

Label report and record

BiieHedo

RecSource

SampleQuery : Select Query i □

Lasthlame

Address*!

•Kryzwicki v-erxjci

97 Roberts Dr. T45 Albany Road 197 Taskbar Drive Havere'lon'-Square Schumack 226 Hollywood Drive Lopez P.O. Bok 10 Sarah 137D Washington Lahe

Costello 10 Pacific Ave. Stickler 1205 Huntingdon Ct.

Kane 51.5 Levick Street_

Unctuous 734 N. Rainbow Dr.

Biasin_79 Evergreen' St._

Citrus 5143 Shore Drive

1 Kimbert^ Road ~ 1200 E. Meda Ave i05 Chesterfield Blvd. 590 Belmont Ave Apt J 241 GrMfi§£bro Dr

For SkipLabels to work, it needs to pad the top of the record source for the report with one blank record for each label to be skipped over. For example, if SkipLabels needs to skip over seven empty spots on a sheet of labels, it inserts seven blank records at the top of the label report's record source. That way, when the sheet actually prints, the empty records get "printed" first (on the empty spots), and real data starts printing on the first available label. Figure 8-8 illustrates the basic idea.

Getting those blank records to the top of the report's record source is no small feat. Plus, you don't want SkipLabels to insert blank records into any real tables or make changes to any real reports in your database. SkipLabels will create and work with copies of the necessary objects: It always creates a report named TempLabels report that prints data from a table named LabelsTempReport. It creates both of those objects, on-the-fly, each time.

Of course, you can't write SkipLabels in such a way that it always skips the same number of labels on the same report. You need to make it flexible enough to work with any number of empty labels on any label report. To provide flexibility, treat the number of labels to skip and the report names as parameters (values that get passed to an argument). In other words, write the SkipLabels procedure so that it can be executed at any time, using the following syntax:

SkipLabels(ReportName, LabelsToSkip)

where ReportName is the name of the report to print, and LabelsToSkip is a number indicating the number of blank labels at the top of the page. For example, the following statement tells SkipLabels to print the report named Avery 8462 Labels, skipping over the first seven used labels on the first page:

SkipLabels("Avery 8462 Labels",7)

The code required to meet all these goals isn't brief, but you don't even need to look at it if you don't want to. All you need to really know about SkipLabels is how to get it into a standard module in your own database and how to call it to work with your own labels. You can skip to the section, "Calling a Procedure from an Event," later in this chapter if you'd rather skip the morbid details for now.

How SkipLabels Works

¿jABEfl

¿jABEfl

Those of you who are ready to look at some VBA code in detail can continue reading here. Be forewarned that the SkipLabels procedure (Listing 8-1), which you're about to see in its entirety, is not short. It probably looks more intimidating than need be. However, like all procedures, SkipLabels is just a series of small steps carried out in a specific order to achieve some goal; SkipLabels just has to go through more steps than most procedures.

You don't need to type in SkipLabels yourself. Just download SkipLabels from www.coolnerds.com/vba.

Listing 8-1: SkipLabels

Sub SkipLabels(ReportName As String, LabelsToSkip As Byte, _ Optional PassedFilter As String)

'Declare some variables.

Dim MySQL, RecSource, FldNames As String

Dim MyCounter As Byte

Dim MyReport As Report

'Turn off warning messages. DoCmd.SetWarnings False

'Copy the original label report to LabelsTempReport DoCmd.CopyObject , "LabelsTempReport", acReport, ReportName

'Open LabelsTempReport in Design view. DoCmd.OpenReport "LabelsTempReport", acViewDesign

'Get name of report's underying table or query, 'and store it here in the RecSource variable. RecSource = Reports!LabelsTempReport.RecordSource

'Close LabelsTempReport

DoCmd.Close acReport, "LabelsTempReport", acSaveNo

'Declare an ADODB recordset named MyRecordSet Dim cnnl As ADODB.Connection Dim MyRecordSet As New ADODB.Recordset Set cnnl = CurrentProject.Connection MyRecordSet.ActiveConnection = cnnl

'Load data from RecSource into MyRecordSet MySQL = "SELECT * FROM [" + RecSource + "]" MyRecordSet.Open MySQL, , adOpenDynamic, adLockOptimistic

'Grab field names and data types from Fields collection.

Dim MyField As ADODB.Field

For Each MyField In MyRecordSet.Fields

'Convert AutoNumber fields (Type=3) to Longs 'to avoid insertion problems later. If MyField.Type = 3 Then

FldNames = FldNames + "CLng([" + RecSource + _ "].[" + MyField.Name + "]) As " + MyField.Name + ","

Else

FldNames = FldNames + _ "[" + RecSource + "].[" + MyField.Name + "]," End If Next

'Remove trailing comma.

FldNames = Left(FldNames, Len(FldNames) - 1)

'Create an empty table with same structure as RecSource, 'but without any AutoNumber fields. MySQL = "SELECT " + FldNames + _

" INTO LabelsTempTable FROM [" + _ RecSource + "] WHERE False" MyRecordSet.Close

DoCmd.RunSQL MySQL

'Next we add blank records to empty LabelsTempTable. MySQL = "SELECT * FROM LabelsTempTable" MyRecordSet.Open MySQL, , adOpenStatic, adLockOptimistic For MyCounter = 1 To LabelsToSkip MyRecordSet.AddNew MyRecordSet.Update Next

'Now LabelsTempTable has enough empty records in it. MyRecordSet.Close

'Build an SQL string to append all records from original 'record source (RecSource)into LabelsTempTable. MySQL = "INSERT INTO LabelsTempTable" MySQL = MySQL + " SELECT [" + RecSource + _ "].* FROM [" + RecSource + "]"

'Tack on the PassedFilter condition, if it exists. If Len(PassedFilter) > 1 Then

MySQL = MySQL & " WHERE " & PassedFilter End If

'Append the records DoCmd.RunSQL MySQL

(continued)

'LabelsTempTable is done now.

'Next we make LabelsTempTable the Record Source for LabelsTempReport.

DoCmd.OpenReport "LabelsTempReport", acViewDesign, , , acWindowNormal

Set MyReport = Reports![LabelsTempReport]

MySQL = "SELECT * FROM LabelsTempTable"

MyReport.RecordSource = MySQL

DoCmd.Close acReport, "LabelsTempReport", acSaveYes

'Now we can finally print the labels.

DoCmd.OpenReport "LabelsTempReport", acViewPreview, , , acWindowNormal

'Note: As written, procedure just shows labels in Print Preview. 'To get it to actually print, change acPreview to acViewNormal 'in the statement above.

End Sub

Okay, that was intimidating. In the next sections, I pick apart SkipLabels and see exactly what makes it tick. If you've lost your appetite to get into the details of it all, you can still skip ahead to "Calling a Procedure from an Event," later in this chapter.

0 0

Responses

  • essi
    How to write a where condition inside docmd in vba?
    2 years ago

Post a comment