error in procedure

[Joggerboy2220]

I have a well-functioning database, I now want to have multiple envelopes printed from a query with addresses with a logo at the top left and then a different address per envelope each time, I have written the following procedure The envelopes are printed perfectly,

but afterwards an error message appears: Set rpt = Reports("ledenbriefRap")

what am I doing wrong?



Public Sub KnopBriefAfdruk_Click()

' On Error GoTo Err_KnopBriefAfdruk_Click



Dim db As DAO.Database

Dim rst As DAO.Recordset

Dim qdf As DAO.QueryDef

Dim rpt As Report



' Open the query that contains the addresses

Set db = CurrentDb

Set qdf = db.QueryDefs("ledenbrief query")

Set rst = qdf.OpenRecordset()



‘ Verify that the recordset is not empty

If rst.EOF Or rst.BOF Then

MsgBox "Geen gegevens gevonden in de query.", vbExclamation

Exit Sub

End If



' Open the report designed for the envelopes

DoCmd.OpenReport "ledenbriefRap", acViewPreview





' Go through the record set and print each envelope

Do Until rst.EOF

' Adjust the fields on the report based on the data in the recordset

Set rpt = Reports("ledenbriefRap")

rpt.Controls("txtName").ControlSource = rst!Name

rpt.Controls("txtAdres").ControlSource = rst!adres

rpt.Controls("txtZip").ControlSource = rst!zip



'Go to the next record

rst.MoveNext

Loop



' Print the envelope

DoCmd.PrintOut acPrintAll



' Close the report and recordset

DoCmd.Close acReport, "ledenbriefRap"

rst.Close

Set rst = Nothing

Set qdf = Nothing

Set db = Nothing



'Err_KnopBriefAfdruk_Click:

' MsgBox Err.Description

' Resume Exit_KnopBriefAfdruk_Click



End Sub

 

[Gasman]

Please use code tags.

And the error message is?

 

[June7]

Never seen anything like this.

Why code looping recordset and setting ControlSource property as opposed to just a report bound to data and sent to printer. Design report to start new page after each record.

Since print action is outside loop, not clear to me how setting ControlSource within loop accomplishes anything.

 

[Joggerboy2220]

Please use code tags.

And the error message is?

error message is:
error 2465 during execution

cannot find the txtname field referenced in the expression.

------------------
after the message if I go into the procedure than i find "txtname" is not empty

 

[Joggerboy2220]

Waarom de recordset in een lus plaatsen en de eigenschap ControlSource instellen in plaats van alleen een rapport dat aan gegevens is gebonden en naar de printer wordt gestuurd. Ontwerp een rapport om na elke record een nieuwe pagina te starten.

How to do that?

 

[CJ_London]

@Joggerboy2220 - to use code tags, paste your code to the thread, highlight and click the </> button to set the tags. e.g.

If rst.EOF Or rst.BOF Then

    MsgBox "Geen gegevens gevonden in de query.", vbExclamation

    Exit Sub

End If

 

[June7]

Perhaps with ForceNewPage property of Detail section.

 

[Gasman]

error message is:
error 2465 during execution

cannot find the txtname field referenced in the expression.

------------------
after the message if I go into the procedure than i find "txtname" is not empty

It is not complaining about rst.name but txtname?

 

[MajP]

Public Sub KnopBriefAfdruk_Click()

    ' On Error GoTo Err_KnopBriefAfdruk_Click
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim rpt As Report
    
    ' Open the query that contains the addresses
    Set db = CurrentDb
    Set qdf = db.QueryDefs("ledenbrief query")
    Set rst = qdf.OpenRecordset()
    
    'Verify that the recordset is not empty
    If rst.EOF Or rst.BOF Then
        MsgBox "Geen gegevens gevonden in de query.", vbExclamation
        Exit Sub
    End If
    ' Open the report designed for the envelopes
    DoCmd.OpenReport "ledenbriefRap", acViewPreview
  
   ' Go through the record set and print each envelope
    Do Until rst.EOF
        ' Adjust the fields on the report based on the data in the recordset
        Set rpt = Reports("ledenbriefRap")
        rpt.Controls("txtName").ControlSource = rst!Name
        rpt.Controls("txtAdres").ControlSource = rst!adres
        rpt.Controls("txtZip").ControlSource = rst!zip
        'Go to the next record
        rst.MoveNext
    Loop
    
    ' Print the envelope
    DoCmd.PrintOut acPrintAll
    ' Close the report and recordset
    DoCmd.Close acReport, "ledenbriefRap"
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set db = Nothing
    'Err_KnopBriefAfdruk_Click:
    ' MsgBox Err.Description
    ' Resume Exit_KnopBriefAfdruk_Click
End Sub

 

[XPS35]

As said in post #3 there is no use in looping through all the records. The real action (print the report) is outside the loop. This will do:

Public Sub KnopBriefAfdruk_Click()
   DoCmd.OpenReport "ledenbriefRap"
End Sub

 

[Josef P.]

BTW:

DoCmd.OpenReport "ledenbriefRap", acViewPreview
...
rpt.Controls("txtName").ControlSource = rst!Name

acViewPreview + change ControlSource ... I would expect an error

 

[arnelgp]

the problem is that acViewPreview is Modal, so it will not run your Recordset Loop.

also you do not need VBA or recordset, use Query and make it the Recordsource of your Report.