Print Each PostCode rs Loop

[DMT Dave]

Hi Guy's, i am unsure if I have got this correct, my first attempt returned msgbox after masgbox continually with 80 intQty for the 1st record when i know the intQty should have been x 2 records so had to force close by task manager



I am looking for a msgbox to pop up to print each post code for the given shipment date, delto and postcode so once you click yes, the report opens (eventually change to viewnormal once working) then the msgbox displays the next record to print

I think I may have the looping incorrect or the msgbox in the wrong stage of looping ?

Once you click yes, I need the report to open, the report opens for Me.txtPC (PostCode) and Me.cboDeliveryPlanning (Del To) and Me.cboShipmentDateIndex1 (shipment date)

 myDate = Me.cboShipmentDateIndex1    Set rs = CurrentDb.OpenRecordset("Select * from tbEdit WHERE ShipmentDate = #" & myDate & "# Order By DelTo;")
    If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    strPC = rs.Fields("PostCode")
    Me.txtPC = strPC
    strDelTo = rs.Fields("DelTo")
    Me.cboDeliveryPlanning = strDelTo
    intQty = DCount("DelTo", "tblEdit", "[PostCode] = '" & strPC & "'")
        If MsgBox("Print: " & strDelTo & " - " & strPC & " " & intQty & " Items", vbQuestion + vbYesNo, "PRINT NOW") = vbNo Then
        Exit Sub
        Else
        DoCmd.OpenReport "rptShipmentInNameOne", acViewPreview 'Changing acViewNormal Once Working
        End If
    rs.MoveNext
    Loop
    End If

[DMT Dave]

I think I have spotted one mistake i made, please correct me, did not select date in the intQty line therefore looking at full history for that delto and postcode instead of specific ShipmentDate ?

Would still rather you guy's confirm if the code will loop ok ?

intQty = DCount("DelTo", "tblEdit", "[PostCode] = '" & strPC & "' And [ShipmentDate] = #" & myDate & "#")

[DMT Dave]

I would use strPC as there can be 6 DelTo Names with various branches so postcode is most unique

[DMT Dave]

Hi Guy's changing the intQty criteria to ShipmentDate Works fine, it's not moving to the next Delto (Postcode) ??

[DMT Dave]

OK, the report that opens is opening twice, once each click

click yes
aa opens
click yes
aa opens again
click yes
ab opens
click yes
ab opens

So it's opening the report again before moving to next record, where am i wrong on this one please

thank you very much

[orange]

Dave,
Please post all of the code for this event.

[Welshgasman]

Use Debug.Print instead of MSGBOX, much easier to debug with.
Indenting correctly also helps with debugging.

myDate = Me.cboShipmentDateIndex1
Set rs = CurrentDb.OpenRecordset("Select * from tbEdit WHERE ShipmentDate = #" & myDate & "# Order By DelTo;")
If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
        strPC = rs.Fields("PostCode")
        Me.txtPC = strPC
        strDelTo = rs.Fields("DelTo")
        Me.cboDeliveryPlanning = strDelTo
        intQty = DCount("DelTo", "tblEdit", "[PostCode] = '" & strPC & "'")
        If MsgBox("Print: " & strDelTo & " - " & strPC & " " & intQty & " Items", vbQuestion + vbYesNo, "PRINT NOW") = vbNo Then
            Exit Sub
        Else
            DoCmd.OpenReport "rptShipmentInNameOne", acViewPreview    'Changing acViewNormal Once Working
        End If
        rs.MoveNext
    Loop
End If

You have tbEdit and tblEdit?

[Micron]

msgbox after masgbox continually with 80 intQty for the 1st record when i know the intQty should have been x 2 records

Your loop is governed by the number of records in the recordset, not some field value, so I don't get that statement.

[DMT Dave]

Hi guy's yes i realized i had a typo then corrected the tbEdit, to tblEdit

This opens up the report and displays the same record on 2nd click then it moves to next then same again, it will open that same name (postcode) twice

Full code pasted

Will try debug.print as per WGM suggestion

do you need me to send record source properties of the report ?

Dim rs As DAO.RecordsetDim strDelTo As String, strPC as String
Dim myDate As Date
Dim intQty As Integer


If Me.cboPrint = "Print One" Then
    myDate = Me.cboShipmentDateIndex1
    Set rs = CurrentDb.OpenRecordset("Select * from tblEdit WHERE ShipmentDate = #" & myDate & "# Order By DelTo;")
    If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    strPC = rs.Fields("PostCode")
    Me.txtPC = strPC
    strDelTo = rs.Fields("DelTo")
    Me.cboDeliveryPlanning = strDelTo
    intQty = DCount("DelTo", "tblEdit", "[PostCode] = '" & strPC & "' And [ShipmentDate] = #" & myDate & "#")
        If MsgBox("Print: " & strDelTo & " - " & strPC & " " & intQty & " Items", vbQuestion + vbYesNo, "PRINT NOW") = vbNo Then
        Exit Sub
        Else
        DoCmd.OpenReport "rptShipmentInNameOne", acViewPreview 'Changing acViewNormal Once Working
        End If
    rs.MoveNext
    Loop
    End If
End If

[DMT Dave]

Tried debug.print, just opens the report, doesn't loop through next name (postcode) in line

If Me.cboPrint = "Print One" Then    myDate = Me.cboShipmentDateIndex1
    Set rs = CurrentDb.OpenRecordset("Select * from tblEdit WHERE ShipmentDate = #" & myDate & "# Order By DelTo;")
    If Not (rs.BOF And rs.EOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    strPC = rs.Fields("PostCode")
    Me.txtPC = strPC
    strDelTo = rs.Fields("DelTo")
    Me.cboDeliveryPlanning = strDelTo
    intQty = DCount("DelTo", "tblEdit", "[PostCode] = '" & strPC & "' And [ShipmentDate] = #" & myDate & "#")
       Debug.Print
       ' If MsgBox("Print: " & strDelTo & " - " & strPC & " " & intQty & " Items", vbQuestion + vbYesNo, "PRINT NOW") = vbNo Then
      '  Exit Sub
       ' Else
        DoCmd.OpenReport "rptShipmentInNameOne", acViewPreview 'Changing acViewNormal Once Working
        'End If
    rs.MoveNext
    Loop
    End If
End If

[Welshgasman]

Just what is the report supposed to show, data for each postcode?
If so, you are not filtering the report with anything?

Comment out the report line and add your fields/variables to the debug.print
I expect you will see what you want then?

[orange]

Change your
Debug.Print to Debug.Print "IntQty = " & intQty

[June7]

DoCmd.OpenReport "rptShipmentInNameOne", acViewPreview, , "PostCode='" & rs!PostCode & "'"

Or design report using Sorting & Grouping and force each postcode to a new page. Then just print one report filtered to shipment date and/or other parameters. No recordset looping.

[DMT Dave]

Thank you all, i have taken some of your suggestions and adapted to work, I have managed to generate space on a different tab control which i didn't have so now populating a list and using a combo to select Auto Print;View Print so the user just keeps clicking down the list whilst Auto print is selected, we have managed to save some time by this, similarly my initial thought was just keep clicking yes on the MsgBox but finding the space to add a list has saved about 75% of time.

Thanks again