Printing from a form, seperate reports

[vgersghost]

I have a form say with a control called route. What I have been trying to do is print out report for each route. (I have a different report for each route) I have a command button on the form with if statements, but it prints all the records from the the form to whichever If statment is true.

If route = 1
print a
else
if route = 2
print b
and so forth.

Thank you for any help.

 

[pbaldy]

Either the report should restrict to the appropriate route (it each is route specific anyway), or you can pass the restriction when you open it using the wherecondition argument of OpenReport.

 

[vgersghost]

Okay

Now explain what you mean. I have tried to filter he reports, but the same thing happens. It prints all the records whether they are route one or two from the if statement that goes true.

 

[pbaldy]

It couldn't happen if the source of the report was a query that included a criteria on the route field. Can you post a sample db?

 

[vgersghost]

Sorry

I have access to the front end of the program and I'm linked to a sql and canot copy tables, so I cannot post the database.

This is the command on the form. Maybe this is where I'm going wrong?

Dim ctl As Control
For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acTextBox
If .Name = "txtPartNumber" Then
'if .Value = True Then

stLinkCriteria2 = txtRoute
If stLinkCriteria2 = 1 Then
' DoCmd.OpenReport "rptTuggerCardNewBatchOne", acViewNormal, "", ""
stLinkCriteria = ""
stLinkCriteria2 = ""
Else
' DoCmd.OpenReport "rptTuggerCardNewBatchTwo", acViewNormal, "", ""
stLinkCriteria = ""
stLinkCriteria2 = ""
'End If
End If
End If
Case Else
End Select
End With
Next ctl

thx

 

[pbaldy]

For starters, that's unnecessarily complex and inefficient. If you're only interested in 1 textbox, just test that. You don't need a loop of all controls. There is nothing there that would restrict records, other than opening different reports, so the restriction would have to occur in the report. What is each report's record source? If a query, does the query contain a criteria?

 

[vgersghost]

Maybe I'm not clear

I have a continous form say with 6 records.

Record Route
1 1
2 2
3 1 and so forth.

When I press my print button I need it to loop through these records only and then print out the report that the route matches.

Record 1 Route 1 to report 1
Record 2 Route 2 to report 2
Record 3 Route 1 to report 1

All the route reports have very different layouts.

Why now it just grabs all the records and print them out on the first if statment that goes true.

thx

thx

 

[pbaldy]

Play with this:

  Dim rs            As Object
  Dim strReport     As String

  Set rs = Me.RecordsetClone
  Do While Not rs.EOF
    If rs!Route = 1 Then
      strReport = "rptTuggerCardNewBatchOne"
    Else
      strReport = "rptTuggerCardNewBatchTwo"
    End If
    
    DoCmd.OpenReport strReport, acViewNormal, , "Record = " & rs!Record

    rs.MoveNext
  Loop
  Set rs = Nothing

 

[vgersghost]

Okay I think were getting closer

I understand what your doing, but now it gives an error "Item not found in this collection.

This is the recordsource for the form
' set record source in SQL to point to data in main.mdb
ssql = "SELECT DISTINCTROW Tugger.[PartNumber], "
ssql = ssql & " Tugger.[CardNO], "
ssql = ssql & " Tugger.[WC], "
ssql = ssql & " Tugger.[TuggerID],"
ssql = ssql & " Tugger.[Route],"
ssql = ssql & " Packaging.[Description], "
ssql = ssql & " Packaging.[QtyPerContainer], "
ssql = ssql & " WarehouseLocations.[Row]& ""-"" &
ssql = ssql & " Suppliers.[SupplierName] "
ssql = ssql & " FROM WarehouseLocations INNER JOIN (Suppliers INNER JOIN ((Packaging INNER JOIN Tugger ON Packaging.PartNumber = Tugger.PartNumber) INNER JOIN PartLocationData ON Packaging.PartNumber = PartLocationData.PartNumber1) ON Suppliers.SupplierID = Packaging.SupplierID) ON WarehouseLocations.WarehouseLocationsID = PartLocationData.WarehouseLocationsID"
ssql = ssql & " WHERE [Tugger].PartNumber = """ & stLinkCriteria & """"
Me.RecordSource = ssql
brun = True

It brings in route.

thx

 

[vgersghost]

Thank you very much

After realizing how brain dead I am, I change you code and made it work.

It works great. Now my routes can go to different reports.

Thank you again

 

[pbaldy]

Well, it looks like there's a field named "route" but not "record", so you'd need to change that to the field that uniquely identifies the record (in 2 places in that line).