Inserting blank records to query result for label printing

[NDayave]

How do,

I have a form that outputs addresses in a format that can be printed on to 3x7 label paper for envelopes.

What I want is a way to enter blank (or " ") rows to the query result where the user specifies so no label is printed in that particular place.

I am aware of the problems of inserting blank records to tables. I say this as the other forums I have searched all reply with disbelief and condemnation for inserting blank rows into a database rather than offering support to the question asked.

I am not trying to store the blank rows, just insert them into the Report's record source at user specified intervals.


For example, the rows populate the labels as such:

Row 1 | Row 2 | Row 3
Row 4 | Row 5 | Row 6

etc

If there is no label in the space Row 3 would be printed on, I want to be able to output the rows as:

Row 1 | Row 2 | BLANK
Row 3 | Row 4 | Row 5

etc


Is there anyway of adding the blank record in at the correct place without resorting to creating a recordset line by line?

Many Thanks,


NDayave

[NeoPa]

Yes.

You can be creative in a query and build the labels report on the back of the query.

I don't think we have enough detailed info here to go much further for now, but it will probably involve a table of enough items to populate a full page at least. I'll stop there as I don't want to have to back-track when I get the details.

[NDayave]

Basically, I have a mailing list generator that produces a list of addresses based on the criteria selected.

Ie: Forename, Surname, Address1, Address2, etc, etc

The query is generated via VBA code based on the selections made by the user. As this involves both Individual Contacts and Organisations, there is the possibility of a Union being used, but not always.

Why would it require a full page worth of labels? I ask as they may only print a couple of labels in one sitting, but several pages in another.


What sort of information are you after?

[NeoPa]

NDayave: Why would it require a full page worth of labels? I ask as they may only print a couple of labels in one sitting, but several pages in another.

I was thinking along the lines of a driving data source, that could be used to link your main data to. This may not be very clear so I will try to clarify :

A driving data source is one that you JOIN another source to, using either a LEFT or RIGHT JOIN (See SQL JOINs for more details of this). For now I will continue using the LEFT JOIN concept as that seems simpler to understand. If you want a blank record anywhere, the data source you use must be able to identify that item, and it would be set up so as not to find a matching record from your main data. With no details of how the items are identified (what indexes may be used) I'm not in a position to get into details.

NDayave: What sort of information are you after?

Some more detailed understanding of terms like user determined. On its own it provides no information at all. If you can explain how a user specifies this and what determines a selected item, then we have something we can build on.

Sometimes one cannot tell what data is required without the context of the question. That is why it is mainly down to you to include what is required. I can point to items that may be necessary, but other information, when supplied, may make some of that superfluous.

Using your example where record #3 should be blank, you need to consider what you have that could identify that record #3 is blank. but that all others match. This is true whether you do this via a table (creating blank records somewhere) or do it in a LEFT JOIN query.

[NDayave]

Well, I've made a very idiot proof query generator for use by the most computer illiterate people around. It functions by making sentences that correspond to the generated SQL.

Eg: Include | Organisations | Working in the Area of | Schools

returns the SQL that brings out all schools.

From here they can add more groups of individuals/organisations by adding more "Include" criteria, remove certain inds/orgs by adding "Exclude" criteria and refining either by adding "And" criteria.

Eg:
Include | Contacts | That are a part of | School 1
And | -------- | Aged | 11 to 15 years old
Exclude | Contacts | That are a part of | Event 1

Would return all the contacts aged 11-15 that go to School 1 and are not currently associated with Event 1.

When only Individuals are involved, the FROM and WHERE criteria is:

FROM (tblSource RIGHT JOIN ((tblContact LEFT JOIN (tblLinkType RIGHT JOIN tblLink
ON tblLinkType.TypeID = tblLink.TypeID) ON tblContact.ContactID =
tblLink.ContactID) LEFT JOIN tblSourceRel ON tblContact.ContactID =
tblSourceRel.ContactID) ON tblSource.SourceID = tblSourceRel.SourceID) LEFT JOIN
(tblOrg LEFT JOIN (tblOrgArea RIGHT JOIN tblOrgAreaRel ON tblOrgArea.OrgAreaID =
tblOrgAreaRel.AreaID) ON tblOrg.OrgID = tblOrgAreaRel.OrgID) ON tblLink.OrgID =
tblOrg.OrgID 
 
 
WHERE ((((tblContact.ContactID IN ( SELECT tblContact.ContactID FROM tblContact
INNER JOIN tblLink ON tblContact.ContactID = tblLink.ContactID WHERE (
tblLink.OrgID = 10)))) AND (tblContact.ContactID IN ( SELECT tblContact.ContactID
FROM tblContact WHERE ( DateDiff('yyyy', tblContact.DateOfBirth, Date()) -
IIF(Format(tblContact.DateOfBirth, 'mmdd') > Format(Date(), 'mmdd'), 1, 0) >=
11)AND( DateDiff('yyyy', tblContact.DateOfBirth, Date()) -
IIF(Format(tblContact.DateOfBirth, 'mmdd') > Format(Date(), 'mmdd'), 1, 0) <=
15))))) AND  ( tblLink.OrgID <> 25)
 

The WHERE is generated in VBA from the Criteria list box on the form line by line adding in the correct bracketing and criteria to add the WHERE clauses in the correct order.

When Organisations are being selected as well as Individuals, the same thing happens with the relevant FROM and WHERE etc and is added as a UNION to the final output.

I am currently thinking about adding the search result to a recordset line by line and inserting the blank rows where specified, then basing the report on the recordset instead. Is there any other way of doing this?

[NeoPa]

That'll teach me to ask for more info :D

There is much here to go through and I'm currently in a rush. I'll have to look at this again tomorrow (I'm out this evening) and see if I can understand enough to help you go forward with this. I won't ridicule the idea of using a temporary holding table for this though. Maybe we can find a purer solution, but often situations don't lend themselves easily to such solutions.

I think I'll enjoy going through this anyway. I love it when people come up with innovative - outside the box - type scenarios.

[NeoPa]

As an addendum, would you mind posting a copy of your database for me to have a closer look. I can't help feeling this will be hard to explain clearly and easily without an example to play with. I include below some instructions on how best to do that on here (Not all may be 100% relevant in your case but it's a template post) :

When attaching your work please follow the following steps first :

1. Please consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
2. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
3. If you've done anything in steps 1 or 2 then make sure that the problem you're experiencing is still evident in the updated version.
4. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
5. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
6. Compress the database into a ZIP file.
7. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

PS. If you feel there is too much of a sensitive nature in it then feel free to ignore this request. It is simply to make the process easier. It's not a pre-requisite for posting or getting help.

[NDayave]

I have attached the relevant parts of the List generator and stripped most other things out. The lists can still be generated as they would normally.

I have PM'd the Zip password.

Let me know what you think.

[NeoPa]

Thanks for this. I have also received the password safe & sound. I hope to get time to look at this properly over the weekend, after which I'll comment further.

[patjones]

Good morning,

Not to hijack the thread or anything, but I worked on this exact problem recently, except in the context of smaller mailing labels that come 30 to a page, arranged in three columns and ten rows.

My users were having the same problem...they would print out say five mailing labels, and the next time around need to start on the sixth label. So I made a label generator that would allow them to pick the row and column they wanted to start on and it works nicely.

Let me know if you're interested.

Pat

[NDayave]

That would be very kind of you Pat.

Even if it doesn't work I'm sure I'll learn something new.

[patjones]

@NDayave
I have two drop down boxes that allow the user to pick the row and column they want to start at, and calculate the label number based on those.

Dim bytLblNbr As Byte
bytLblNbr = 3 * (Me.cboRowNbr - 1) + Me.cboColNbr
 
For j = 1 To bytLblNbr - 1
     DoCmd.RunSQL "INSERT INTO tblTemp VALUES (Null, Null, Null, Null, Null, Null, Null)"
Next j
 
DoCmd.RunSQL "INSERT INTO tblTemp VALUES ('" & Me.txtNameLast & "', '" & Me.txtNameFirst & "', '" & Me.txtStreet & "', '" & Me.txtAptNo & "', '" & Me.txtCity & "', '" & Me.txtState & "', '" & Me.txtZip & "')"

When you open the report, you can set the record source to 'tblTemp'. This puts the necessary null records in the table first and then tags on my address record from the form. In this example, I'm just tagging on a single address from a form, but that last INSERT can easily be modified to add on multiple records, which is what I do in the operational version of my application.

Let me know what you think.

Pat

[NDayave]

We'd thought about temporary tables, but as this is a regular operation that can involve hundreds of rows we are trying to avoid it.

I also want to be able to skip certain labels in case they have ruined some on a sheet (See post original post).

I am hoping for a solution that can add in blank rows into the SQL, which I know is possible in other platforms.

[patjones]

The basic idea for this is something I got from another forum where Allen Browne posted, by the way. So it isn't entirely my idea.

There is another method which involves having a table of null records, and using a UNION ALL to join the appropriate number of those null records to whatever set of records you want to print. I did in fact try this as well, and it works, but in my opinion it is a more complicated means to the end.

Pat

[patjones]

@NDayave
Well, it's just one temporary table, and you can delete it after the operation if you don't want it hanging around your database.

If you are looking to skip some labels in the middle of the data rather than just at the beginning...I would need to think about that one some more.

Pat

[NeoPa]

I now have the db working, and I can see how to select items, or even predefined criteria. What I'm struggling to understand (and this is fundamental to any progress) is why labels should be determined to be blank. If an item is in the list it should print a label. If not then it shouldn't (and wouldn't).

My initial musings, assuming that some items need to stay in the list yet not create a label, would be to have some sort of flag in the record source that indicates your requirement (To print or not to print. That is the question!). The Control Source of the controls on your report would then simply say :

=IIf([Flag],[AddressLine1],Null)
=IIf([Flag],[AddressLine2],Null)
...

Does that make sense?

I expect with a better understanding of what determines a blank label we may get a better solution if this isn't good for you.

[NeoPa]

I have just spoken with Mary (MSquared) and she feels that you are trying to avoid printing on missing, damaged or otherwise pre-used labels on a sheet. Can you confirm this for me please.

[NDayave]

Yes, Mary is correct; I want to be able to specify and 'pass over' any labels that are not suitable for printing on the sheet.

I am not so worried about how to determine which labels to pass over, more how to add in either the blank rows to the query result or blank detail sections in the report.

[NeoPa]

Well, the first step then obviously, is to consider how you will get the operator to specify which labels to skip. Is it limited to the first sheet? I would suggest you design a form of some sort which must clearly be based on data in a table of some kind. A temporary table could work, although I'd favour a bespoke table that handled records for the particular run. It would probably need to handle identifying the user so that multiple users could work on it at the same time. For a set of records that match your labels you will need to consider how you will start. How many labels to handle of course.

Once that has been determined you can use RecordSet processing to identify each valid label with a record of your data, then build the report based on a query linking the two.

Does that all make sense?

[NDayave]

This database will only be used by one user at a time, so I don't have to worry about multiple users.

In order to maintain simplicity, I will have the form resemble the Label paper, allowing the user to click the labels that they wish to pass over. If recordset processing is to be used, why bother with the Tables? Seems like an extra step in the process.

My original thoughts were to have the form look like the label paper, with clickable labels that store the label numbers (multiplied by the page number is another page is added) on the form to be used in a recordset when the report opens.

So the process would be:

Click Labels 3 and 6 --> 3 and 6 stored on the form --> Report opens --> adds data to recordset from Query with blanks in row 3 and 6 and updates RecordSource


I thought this was a little inefficient and was hoping for some SQL side commands to pass over rows. What are the benefits of using the tables over a RecordSet on Report_Load?

[patjones]

@NDayave
A report's recordsource must be set to a query name, table name, or SQL string. You'll get an error if you attempt to assign a recordset.

You could using something like this (pseudo-code) to build a temporary table which will serve as the report's recordsource:

Let rst be the recordset that holds your data and j be an integer counter.

rst.MoveFirst
j=1
 
While Not rst.EOF
     If (label #j marked for printing) Then
          'Insert current rst into tblTemp
          'rst.MoveNext
     Else If (label #j marked to be skipped) Then
          'Insert blank record into tblTemp 
     End If
 
     j=j+1
Wend

Provided that tblTemp does not get ordered differently between the end of this code and loading the report, you can set the report's recordsource to be tblTemp and get blank labels interspersed amongst your printed labels.

Pat

[NeoPa]

NDayave: In order to maintain simplicity, I will have the form resemble the Label paper, allowing the user to click the labels that they wish to pass over.

That's pretty much what I had in mind. Remember that if it's to be much use it must cover at least as many records as the labels you require to print. Any labels selected to be skipped would increase that number of course.

NDayave: If recordset processing is to be used, why bother with the Tables? Seems like an extra step in the process.

Because you cannot build your report on a Recordset (As Pat says).

NDayave: My original thoughts were ...

I thought this was a little inefficient and was hoping for some SQL side commands to pass over rows. What are the benefits of using the tables over a RecordSet on Report_Load?

As per previous points you cannot use a recordset to drive a report.

As for some clever SQL to apply to the table to get what you need without any recordset processing, I'm afraid you may be underestimating this issue somewhat. This is not something that lends itself well to SQL. SQL deals with recordsets. Relative positions are anathema to SQL except where Aggregate functions are used, and even that is not what it's mainly about. You will need some pretty fiddly SQL anyway, but it won't do what you're after alone if I know anything of the matter. You will need to prepare some master data on which to hang your main label data.

[NDayave]

I have found the simple little trick I was after.

On the 'Rows to skip' form, store the row numbers in a global string variable with a comma at the start and end of each number, Eg: ",2,4,6,7,9,"

In the Report, enter the following code and the specified rows will be passed over, continuing with the next row in the recordsource:

Option Compare Database
Option Explicit
 
Dim fBlankNext As Boolean 'Pass over next Label?
Dim intLine As Integer    'A line counter.
 
 
Private Sub Report_Open(Cancel As Integer)
'Zero the Line Counter
intLine = 0
End Sub
 
 
Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
'Pass over the First Label if Necessary
fBlankNext = (InStr(1, <<BLANK ROW LIST VARIABLE>>, "," & intLine + 1 & ",", vbTextCompare) > 0)
End Sub
 
 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
'Increment Line Counter
intLine = intLine + 1
 
'If passing over the next label, pass over and check if passing over the one after
If fBlankNext = True Then
    Me.PrintSection = False
    Me.NextRecord = False
    fBlankNext = (InStr(1, <<BLANK ROW LIST VARIABLE>>, "," & intLine + 1 & ",", vbTextCompare) > 0)
Else    'If not passing over, print detail and check if passing one after
    Me.PrintSection = True
    Me.NextRecord = True
    fBlankNext = (InStr(1, <<BLANK ROW LIST VARIABLE>>, "," & intLine + 1 & ",", vbTextCompare) > 0)
End If
End Sub
 
 
 

Found and adapted from http://www.allenbrowne.com/ser-12.html

[patjones]

@NDayave
Glad to see that you got it to work out. Clever use of the InStr function. I like it!

Pat

[NeoPa]

Very nice solution. I had no idea that setting Me.NextRecord to False ensured the same record was processed again in a report. Perfect for this scenario.

[Simon5280]

@NDayave
Hi there, is it possible to get a copy of the file.

thanks heaps