error in procedure (1 Viewer)

[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.

 

[Pat Hartman]

@Joggerboy2220 Welcome to AWF. It is pretty clear from your stab at this that you have programmed in a different environment but don't know anything about Access.

The concept of bound objects isn't unique to Access but is apparently rare enough that not all platforms support the concept. Access is a RAD (Rapid Application Development) tool. It does a lot of stuff for you behind the scenes. You simply design how a form/report will "look" and if you bind it to a table or query, Access will automagically make it work. It will fill the controls, it will scroll the recordset. And with a form, will allow you to add/change/delete data and manage all that behind the scenes. The ONLY place you actually need to provide code is in the Form's BeforeUpdate event and that code should be used to validate the data to keep Access from saving bad data or empty records. Aside from RI rules, only you know the details of what constitutes valid data.

For reports, start by creating a query that joins tables and selects the columns you want. Add a where clause if you need to select records. Then either use the report wizard and give it the name of the saved querydef or manually build the layout and then add the querydef name to the RecordSource property of the report. Then, you can bind each individual control by editing the ControlSource for each control to bind it to a column from the report's RecordSource. I find it easier to create the query and use the wizard and then rearrange the layout than to create the layout manually and then manually bind all the controls. The Report wizard isn't very clever in how it lays things out but sometimes, you'll have other reports with similar data that you can use as templates. Eventually, you establish a rhythm and a standard method of creating form/report objects.