Access 2003 Multi WhereCondition Question 1

[cafulford]

Hi everyone,

I am not a programmer at all and I am trying to figure out how to get a WhereCondition to print a second page of a report if a flag called "MULTI_PAGE" is set which is available in the 1st reports table. The 1st report, or 1st page, prints fine with the code below.

My question is: Is it possible to look at that "MULTI_PAGE" flag in the existing VB print routine below and have it print the form (page 2+) only when the "MULTI_PAGE" flag is set? The page 2 form is called "BL 2".

So, for example, there may be 5 BOLs with only 1 page so those 5 will print, then the 6th might be a 2 page BOL, so both pages will print in sequence, etc.

They originally select the BOL groups to print from a listbox.

Private Sub cmdPrintBOLs_Click()
Dim sWhere As String, vItem As Variant
With lstSelectTrailers
For Each vItem In .ItemsSelected
If Len(sWhere) <> 0 Then sWhere = sWhere & " OR "
sWhere = sWhere & "(CARRIER_NA='" & .Column(1, vItem) _
& "' and TRAILER_NU='" & .Column(2, vItem) & "')"
Next vItem
End With
If Len(sWhere) = 0 Then
MsgBox "You have not selected any trailer(s)"
Else
' Debug.Print sWhere
DoCmd.OpenReport "Bill OF Lading", , WhereCondition:=sWhere
End If
If MsgBox("Do you wish to mark all these BOLs an 'Printed'?", vbQuestion Or vbYesNo) = vbYes Then
CurrentDb.Execute "Update bl_File set Printed = True where " & sWhere
End If
End Sub

 

[dhookom]

I am having an issue determining if this all one report or multiple reports. You mention "print the form" but forms are typically for data entry and navigation, not printing.

The number of pages is generally determined by the number of records in the record source of the report.

You can cancel the printing of a section in a report in the On Format event of the section.

Duane
Hook'D on Access
MS Access MVP

 

[cafulford]

I am sorry, my lack of experience in Access is showing but I am learning.

It prints a report called "Bill OF Lading" which is usually 1 page long. There are cases where the BOL will be more than 1 page in which case, the report "BL 2" need to run. I set a flag called "MULTI_PAGE" to true earlier on before this and when it actually prints the 1st page, I want it to look at that flag and if true, print the "BL 2" report.

Thanks so much for the help!

Charlie

 

[dhookom]

I would not have anything in the Bill Of Lading report that does anything but render itself. I would add to the code that opens Bill of Lading to determine from the data in a table if BL 2 needs to be printed.

Duane
Hook'D on Access
MS Access MVP

 

[cafulford]

Duane, could you provide a sample piece of code for me?

Thanks,

Charlie

 

[dhookom]

Can you describe

I set a flag called "MULTI_PAGE" to true earlier on before this and when it actually prints the 1st page, I want it to look at that flag and if true

Where did you set a flag? Is this in a table somewhere?
How are you opening the current BOL report? Can you share the code?


Duane
Hook'D on Access
MS Access MVP

 

[cafulford]

Hi Duane...sure:

I inherited this application and it was designed very poorly from what I can tell. I am more of a Web UI (Dreamweaver, etc.) type of person but for the most part understand programming. With that said:

I created a .jpg with snapshots of the code for you at:

http://cafulford.com/stuff/bol_process_pics.jpg

- all the data resides in a table called bl_file
- there are 2 reports that get called, 1 is "Boll OF Lading" which only prints the 1st page of BOLs (99% og BOLs are 1 page). Then there is the "BL 2" report which prints any additional pages.

The existing app consists of the following sections:

1) Edit BOLs, 2) Print BOLs(the only in question here), and 3) Export BOLs (this exports data to a FoxPro system - yes, I know don't say it )

Every BOL has to be edited in a form before printing so that is where I check and set the bl_file "MULTI_PAGE" field" accordingly (boolean yes/no) if it is multi-page.

From there I group common carrier/trailers together in a query by printed and unprinted (also a field in bl_file).

Thanks!

 

[dhookom]

I would add the Multi_Page field as a column(3) in the list box and print each report by itself. Caution air code follows:

Private Sub cmdPrintBOLs_Click()
  Dim sWhere As String, vItem As Variant
  Dim sBigWhere as String
  With Me.lstSelectTrailers
    For Each vItem In .ItemsSelected
      sWhere = "(CARRIER_NA='" & .Column(1, vItem) _
        & "' and TRAILER_NU='" & .Column(2, vItem) & "')"
      sBigWhere = sBigWhere & sWhere & " OR "
      'Print 1 BOL
      DoCmd.OpenReport "Bill OF Lading", , WhereCondition:=sWhere
      'check to see if BL2 needs to be printed
      If .Column(3, vItem) = True Then
        DoCmd.OpenReport "BL2", , WhereCondition:=sWhere
      End If
    Next vItem
  End With
  If Len(sBigWhere) > 0 Then
    sBigWhere = Left(sBigWhere, Len(sBigWhere)-4)
    If MsgBox("Do you wish to mark all these BOLs an 'Printed'?", vbQuestion Or vbYesNo) = vbYes Then
      CurrentDb.Execute "Update bl_File set Printed = True where " & sWhere
    End If
  End If
End Sub

Another option might be to add the BL2 as a group footer section in the main report and cancel the printing if the value of MULTI_PAGE is false.

Duane
Hook'D on Access
MS Access MVP

 

[cafulford]

Hi Duane,

Am I correct in thinking that the approach you recommended may not work because the multi_page is at the record level and the query is at the group level or am I missing something?

Here is the query. How would I modify it for the record level?

I am so close! Thanks for your continued help.

SELECT CARRIER_NA & TRAILER_NU & " (" & Count(*) & ")" as Display, CARRIER_NA, TRAILER_NU
FROM bl_file
GROUP BY CARRIER_NA, TRAILER_NU
HAVING CARRIER_NA is not Null and TRAILER_NU is not Null;

I named the query qrySelectTrailers. When I run the query, I get something like:

Display CARRIER_NA TRAILER_NU
UPS12345 (11) UPS 12345
FEDEX343243 (25) FEDEX 343243
Freightliner4132 (3) Freightliner 4132
... etc

Next, I created an unbound form and on it created an unbound listbox with these properties:

* Name: lstSelectTrailers
* RowSource: qrySelectTrailers
* ColumnCount: 3
* BoundColumn: 1
* ColumnWidths: ;0;0
* MultiSelect: Simple

 

[dhookom]

Did you consider just using a subreport? It seems to me this would be the easiest solution.


Duane
Hook'D on Access
MS Access MVP

 

[cafulford]

I am so new to access I am literally hearing all these terms for the first time. Can you point me in the right direction? How would I check for the multi_page flag?

I tried looking at adding the bl2 as a group footer section int he main report and cancelling the printing if the value of multi_page is false but I could not get it to work. Is the subreport another idea or the same one?

 

[dhookom]

The subreport is a different solution from the earlier suggestion. I'm not sure how the Bill Of Lading and BL2 reports are related. My guess is the linking field is BL_NUM. This would suggest the BL2 report would be placed in the BL_NUM group footer with the Link Master/Child set up with this field. If the BL2 report needs different linking fields then change them.

I think this is a much more robust solution and would not require hardly any coding.

BTW: the table structures seem highly un-normalized.

Duane
Hook'D on Access
MS Access MVP

 

[cafulford]

Bill of Lading is the 1st page and BL2 is the second page of the same report and believe it or not, both reports get their data from the same table - bl_file.

The person who designed this was not familiar at all with database design or worried about normalization. He left long ago and it now has gotten put in my lap.

I'll use my best friend Google and see if I can work through your suggestion.

Thanks!

 

[dhookom]

They are not two pages on the same report. They are different reports. I am simply suggesting you open the main report in design view and inserting the BL2 report into the appropriate section so the BL2 displays in the appropriate position. If this is the Report Footer section then that's where it should go. You use the Link Master/Child properties to filter the records displayed in the subreport.

If the values displayed in the reports are the same then it seems to me you only need one report with the BL2 fields displayed in a group footer. The group can be based on the primary key of the report's record source. You can then check the value of the Multi_Page field to determine if you should cancel the formatting of the section.

If you are suggesting that CUST_PO1, CUST_PO2, CUST_PO3,... are fields in the table bl_file then you really have a table structure issue.

Duane
Hook'D on Access
MS Access MVP

 

[cafulford]

No, I was wrong. Report "Boll of Lading" uses the table "bl_file" and report "BL2" uses table "bl_file2" to get it's data.

I kind of have a subreport inserted in to Bill of lading but how to I get it not to print the BL2 piece if bl_file.multi_print is not true?

 

[dhookom]

You need to have a text box in the same section of the report:
Name: txtMulti_Page
Control Source: Multi_Page
Visible: No
Then in the On Format event of this section, use code like:

   Cancel = (Nz(Me.txtMulti_Page,0) = 0)

If this doesn't work as desired, come back with the section of the main report containing the subreport as well as the field(s) used in the Link Master/Child properties.

Duane
Hook'D on Access
MS Access MVP