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]

That would be very kind of you Pat.

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

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]

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.

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