Solved

Skip a Record in a report

Posted on 2002-05-05
22
473 Views
Last Modified: 2008-02-01
Hi,

I am looking for a .MoveNext command to go in Detail_Print

I have a report where I am printing address labels.  The problem is, I need to skip certain addresses that are in the Report's recordset (don't ask why :)  Unfortunately I can't sort by the AddressID due to restrictions in what I'm trying to do.

The way I need to do it is to create a string of all the AddressIDs separated by pipes (|123|324|67457|23|2342| etc.), As I print each label I send the AddressID for that label to a function that checks if the ID is still in the string.  If it is, it returns true, removes the address from the string.  If it's not, it returns false.

I am looking for the syntax for move next (don't print that label) if the function returns false (something like this):

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
    If Not ShouldIPrintAddress(AddressID) Then
        Me.Recordset.MoveNext
    End If
End Sub


Thanks and I hope that made some sense

Sean
0
Comment
Question by:seanmonk
  • 8
  • 6
  • 5
  • +3
22 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6990577
Sean,

This should be solved by changing the query that's used for the report's rowsource.
Just create iso the "pipe string" a table with the addresses to exclude.
Next join your report query with this table, drag and drop the ID field and double-click the join-line. Then select option 2 or 3, making your report query the "master".
Place all fields and add from the "exclude table" the ID.
Add below in the criteria for that ID "Is Null" (without quotes).

Now the report will print as you want..

Nic;o)
0
 

Author Comment

by:seanmonk
ID: 6990586
Nic;o,

It's more complex than that.  The story is this:  I have a pool of volunteers, some with kids in a school.  The school wants me to go through and pull one label for each family (by address), sorted by the grade, so they can give the envelopes to the youngest child in each household, and send out the rest via mail.  I created the string of addresses to be EVERY address for the pool, then as I go through each child by class, I see if that family still needs an envelope.  If not, I want to NOT PRINT that address (move to next record).  I can do this by Cancel = 1, but that just leaves a blank label (waste), I want to remove the label!

Is there no MoveNext command for the reports?

Thanks

Sean
0
 
LVL 1

Expert Comment

by:drben
ID: 6990597
Sean,

Try doing EXACTLY what you're doing, but do it in the Detail_Format sub:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    If Not fncIsPrintAddress(ADDR_ID) Then
        Cancel = 1
    End If
End Sub

This will work because Detail_Format happens before it prints, not AS it prints!

Ben
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6990609
This still should be done in the query that is the record source for the report.  Try creating a query and perform a GroupBy by  AddressID.  The fields Address, City, and  State  should all be selected as Max because they all should be the same.  The Grade and DateOFBirth should be selected as minumum to select the Youngest child in lowest grade for the family.  Now you have a list of unique addresses for the youngest child in the lowest grade in each family in the school.

Example:

SELECT tblAdd.AddressID, Max(tblAdd.Address) AS AddressLine, Max(tblAdd.City) AS City, Max(tblAdd.State) AS State, Max(tblAdd.ZipCode) AS ZipCode, Min(tblAdd.Grade) as Grade, Min(tblAdd.DOB) AS DateOfBirth  
FROM tblAdd
GROUP BY tblAdd.AddressID;

This should be close to what you need.

Bob Scriver

0
 

Author Comment

by:seanmonk
ID: 6990615
Bob,

Thanks for the suggestion.  What about people who are volunteers who don't have kids? Would that handle split families? For example, youngest child lives with mom, older brother lives with both mom & dad (in two households).  Need the young kid to get mom's note and the older to just get dads.

Also, what is the advantage of doing it in the query?  I'm working on drben's suggestion, seems to be working.

Ben
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6990687
Well however you identify the AddressID's to be exluded the best way to handle this is like Nic;o)'s comments suggest.  Instead of a String representation of your exluded AddressID's, put those AddressID's into a table.  Then in a query create a left join from the tblMasterAdd on AddressID to the field AddressID in tblExcluded.  The Criteria line would look for Nulls which would indicate that the record in the tblMasterAddresses should not be excluded as there is no record with that AddressID in the tblExcluded.  This way you never have use a Function in your code and you do not have to perform a cancel of a line in the printing process.  The query does it all for you.  Also, you do not have to be working with strings with "pipes" as delimiters etc.  Use the power of the tables and query process.  

Example:

Select tblMasterAdd.AddressID, tblMasterAdd.FamilyName, tblMasterAdd.Address, tblMasterAdd.City, tblMasterAdd.State, tblMasterAdd.ZipCode, tblMasterAdd.ChildsName
FROM tblMasterAdd LEFT JOIN tblExcludes ON tblMasterAdd.AddressID = tblExcludes.AddressID
WHERE (((tblExcludes.AddressID) Is Null));

The only records returned from above will be the ones that do not have a matching record in the tblExcluded table.

Bob Scriver
0
 

Author Comment

by:seanmonk
ID: 6990695
Bob,

One of us isn't understanding the other.  Either I don't understand what you just said, or you misunderstood what I had said.

To clarify, The string is addresses that NEED to get an envelope.  Once a label has been printed for that address, the AddressID is removed from the string.  So everytime I go to print a label, I check if it is still in the string (they are ALL in the string to begin with).  If not, I cancel that print.

I've tried drben's suggestion, and it ALMOST works.  I am, however, having a small problem at the beginning each page, it's somehow dropping one address!

Bob, if you could clarify your Min/Max suggestion, that would be great.

Thanks

Sean
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 75 total points
ID: 6990698
Hi Sean,

By your explanation it's even easier.
Just create a query with the addresses table and the children table and connect them by the addressID.
There you select the fields addressID and the child's grade.
Now change the query to a Groupby query (The "E" looking button) and select for addressID the "Groupby" (default) and for the child's grade "MIN".
This delivers all unique adressID's and the grade the envelope has to be deliverd. By joining your report query with this query you can even print the grade on the label...

See my point?

Nic;o)
0
 

Author Comment

by:seanmonk
ID: 6990704
Nic;o),

Thanks for the input.  Will that handle addresses for people who are not students?  See, most of the volunteers are parent, some are just community members.  I need to find out which volunteers have students, hand those out and give the send the rest USPS.  Will your solution allow for a grade called "USPS" created if the student is not enrolled?  Can I sort those to the end?

Sorry for my stupidity, but doing complex queries and Min/Max and group by is new to me.

Thanks

Sean
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6990754
Hi Sean,

All you've been specifying can be delt with using queries.

Once you have all "handout" lableaddresses selected with the query "qryMINgradeLabels" (what's in a name), you can use the same query "reversed".
Just copy/paste the query and doubleclick the relation of addressID. There select option 2 or 3, making the addresses table "always appear". Now place the address fields needed and as extra the addressID of the report. Underneath that address type "Is Null" (without quotes).
Now all addresses NOT printed will appear.

Clear ?

If not, drop (part of) the .mdb zipped in my nico5038 mailbox "at" yahoo.com and I'll have a look.

Nic;o)
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6990763
Here is the SQL that I posted previously:

SELECT tblAdd.AddressID, Max(tblAdd.Address) AS AddressLine, Max(tblAdd.City) AS City, Max(tblAdd.State)
AS State, Max(tblAdd.ZipCode) AS ZipCode, Min(tblAdd.Grade) as Grade, Min(tblAdd.DOB) AS DateOfBirth  
FROM tblAdd
GROUP BY tblAdd.AddressID;

Now we are understanding what you need.  The above query SQL does just what Nic;o) explained in his comment of 5:23 pm.  The Max(fieldname)'s just selects the max value of the particular address fields which should be the same for all AddressID's that are being Grouped By. (i.e. 5 children from one family/household - Same AddressID, address, city, state, zipcode.) The GroupBy AddressID gives you one row for that particular AddressID instead of 5.  Since the address information should be the same for all 5 then the Max(Address_Info)'s should give you the same information that each have, all in one row.   The Min(Grade) just selects the lowest grade for the AddressID and the Min(DOB) give you the youngest child.

As for the volunteers I am not understanding that situation completely but so I will follow Nic;O)'s comments there.  I am not sure if the Volunteers are in the tblAddMaster table.  If they are you should have no problem using the last suggestion that Nic;o) has made.  You see the first query that is being suggested creates a recordset for printing the labels for the one/youngest child in the lowest grade.  So if you use that query to match back against the tblAddMaster and look for the Nulls you will have identified the tblAddMaster records that did receive a mailed notification.

Bob Scriver
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 54

Expert Comment

by:nico5038
ID: 6990766
BTW, I see you just want all labels.
If you add a Grade field (e.g. bottom right), then you can use the last query slightly modified.
Just remove the "Is Null" and change the Grade field into:
GradeCode:IIF(IsNull([Grade]),"USPS",[Grade])

By adding an ascending sort on this field, all labels for USPS will be in one section and Identifyable.....

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6990767
Hi Bob,

Just a parallel comment entry ;-).
I think we gave Sean lots of SQL homework.....

Nic;o)
0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6990775
Nic;o),

You bet.  If the light comes on with the power of GroupBy and using the Min/Max Functions, he will be thrilled with the possibilities.  Just a step at a time and finally it all comes together.

Seems like we've been pounding the comments out today without much company.

Bob Scriver


0
 
LVL 3

Expert Comment

by:Bob Scriver
ID: 6990780
Nic;o),

Side issue.  

Are you receiving your email notifications on any of these questions.  I am not.  I posted a comment to CS questioning if their email server is working or not.  I am receiving email from other sources.

Bob Scriver
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6991021
Correct Bob, email-notifs are out, but there was already an entry in CS ;-)

Nic;o)
0
 
LVL 57
ID: 6991161
Sean,

FWIW,

  The report engine is in control of the cursor for it's recordset, you you can't do a movenext like your thinking.

  To skip a record with the report engine, it would be:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   If Not ShouldIPrintAddress(AddressID) Then
       MoveLayout = Flase
       NextRecord = True
       PrintSection = False
   End If
End Sub

  Many don't realize how flexable the report engine can be when you learn to use MoveLayout, NextRecord, and PrintSection.

Jim.
0
 
LVL 2

Expert Comment

by:kiddiec
ID: 7060002
ry getting a copy of Access 97 developers handbook ISBN 0-7821-1941-7 there is a whole section on printing labels and skipping over records
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7215722

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Author Comment

by:seanmonk
ID: 7215896
Thanks everyone for your help.  Sorry I never finished this up.  I got it working with the help of you all!

Thanks again

Sean
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7217098
Thanks for finalizing the Q Sean.
I'm just cleaning all older Q's and wasn't sure you got it working.

Nic;o)
0
 

Author Comment

by:seanmonk
ID: 7217483
Thanks again
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now