PRINTING ALL RECORDS WITH MATCHING CRITERIA INDIVIDUALLY

Hello StanKobrin,

When you say a matching value from another table, do you mean that this second table just contains one record with a single field containing the value to match against.

Pete

Hi Pete

Here is the scenario:

I have three tables:
tblEntranceTickets
tblFoodVouchers
tblDrinkVouchers

The user of the database enters a record into the tblEntranceTickets table which generates a primary key value. (TicketID) The user then selects a ticket option (TicketOption) (A,B,C,etc)

After creating this record, depending on the value of the TicketOption field a number of records are automatically created in both the tblFoodVouchers and tblDrinkVouchers tables. I.e if the user selects option A then 1 food voucher and 2 drink vouchers are created.

When the vouchers are created in the respective tables, it creates a link to the TicketID field.

Directly after these records have been created, I need to print them. All of them being all the records from both the tblFoodVouchers and tblDrinkVouchers tables where the TicketID equals the TicketID field of the current record (from tblEntranceTickets).

These vouchers then need to print out onto a SLIP printer which automatically cuts the voucher at the end. If the report prints all the records in one report, then the SLIP printer only cuts the report at the end (and not after each voucher)

Therefore I need the code to cycle through the tblFoodVouchers and tblDrinkVouchers tables printing each record where the TicketID feld is equal to the TicketID in the current tblEntranceTickets record seperately before moving onto the next record. That way the SLIP printer will cut each individual voucher seperately while the user only has to click one button to do the print job.

The way I have it working now is that a button sits on a form (linked to tblEntranceTickets) called cmdGenerateVouchers with the following code:

*********

If Me.VoucherPrint = False Then

Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset("SELECT FVN, TicketNumber, VoucherDate, Price FROM tblFoodVouchers Where 1=0", dbOpenDynaset, dbSeeChanges)

Dim i As Integer
For i = 1 To Val(Me!Food)
      rs1.AddNew
      rs1!TicketNumber = Me.TicketID
      rs1!VoucherDate = Me.TicketDate
      rs1!Price = Me.FoodPrice
      rs1.Update
Next

Dim rs2 As DAO.Recordset
Set rs2 = db.OpenRecordset("SELECT DVN, TicketNumber, VoucherDate, Price FROM tblDrinkVouchers Where 1=0", dbOpenDynaset, dbSeeChanges)

Dim j As Integer
For j = 1 To Val(Me!Drink)
      rs2.AddNew
      rs2!TicketNumber = Me.TicketID
      rs2!VoucherDate = Me.TicketDate
      rs2!Price = Me.DrinkPrice
      rs2.Update
Next

MsgBox "Vouchers Printing...", vbInformation, "Voucher Print System"
Me.VoucherPrint = True

Else

MsgBox "Vouchers Already Printed. Please Add new Ticket", vbCritical, "Voucher Print System"
Me.cmdAddTicket.SetFocus

End If
End Sub

*******

Ideally, I would like the code to print these records to sit directly under this code on the same button. The report used to print the vouchers are as follows:

tblFoodVouchers  --> repFoodVouchers
tblDrinkVouchers  --> repDrinkVouchers

Hope this makes more sense to you now...

Stan

Both the tblFoodVouchers and tblDrinkVouchers have these fields in them:

Voucher ID     (FVN and DVN for Food Voucher Number and Drink Voucher Number
TicketNumber  (links to TicketID from tblEntranceTickets)
VoucherDate
Price

If the tables need to change to the same PK (VoucherID instead of FVN and DVN) I can change it.

Stan

first create a query by using the wizard

Query>New
Select Find Duplicates Query Wizard
Select the tables and field (follow the wizard)
saved the query. and use as source record for a form


then check here
How to Print a Single Record from a Form in a Report
http://support.microsoft.com/?kbid=209560

Hi Capricorn

I think you are missing my desired effect here.

I am using "How to Print a Single Record from a Form in a Report" option to print the actual EntranceTicket.

However, I don't want the user to have to navigate to each record in respective tables and click the print button.

I need the user to click one button which will automatically print all corresponding records individually.

Stan

What is the possibility of adding a command similar to:

DoCmd.OpenReport "repFoodVouchers", acViewPreview, "", "[TicketID]=[Forms]![frmEntranceTickets]![TicketID]"

after each record creation?  i.e:

Dim i As Integer
For i = 1 To Val(Me!Food)
      rs1.AddNew
      rs1!TicketNumber = Me.TicketID
      rs1!VoucherDate = Me.TicketDate
      rs1!Price = Me.FoodPrice
      rs1.Update
      DoCmd.OpenReport "repFoodVouchers", acViewPreview, "", "[TicketID]=[Forms]![frmEntranceTickets]![TicketID]"
Next

When I add that DoCmd line into the code it prints the correct reports perfectly.

It prints both the tblDrinkVouchers and tblFoodVouchers seperately and the SLIP printer is cutting in between the two reports.

However, if my same problem exists. If I have more than 1 record in the tblDrinkVouchers or tblFoodVouchers report it prints them together and not seperately?

Any ideas?

Stan