Trying to run code to print 1 record as a label - Error 3219

By kate1527074741 ·
Hi everyone,
I am a complete novice when it comes to VBA so this is particularly challenging for me, I really hope you can help!

I have created a database to be used as a visitor guestbook and fire register for our site. What I am trying to do is figure out a way where I can press a button and it generates a label for only 1 specified record which I can then print as a visitor's pass.

I have used this article from this site; and I downloaded the accompanying sample file.
I have copied and pasted the code directly from the sample file (amending relevant table names etc to my own) and after finding several errors which I seem to have rectified, I now have 1 final error which I cannot fathom.

This is my code in full below but when I click the button in my form to test, I receive 2 errors: "-2147352573: Member not found" and then "Run-time error '3219': Operation not allowed in this context".

Once clicked through the second error window, it highlights rst.Close in the errHandler at the very bottom in yellow. I'm at a complete loss as to what the problem is?

Option Compare Database
Option Explicit
Private Sub cmdPrintMultipleLabels_Click()
'Print multiple labels for current record.

Dim i As Integer
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

'Delete previous label data.
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE FROM [Temporary_Contacts]"
DoCmd.SetWarnings True

On Error GoTo errHandler

'Catch blank control.
'If set to 0, label report is blank but runs.
If IsNull(Me!txtNumberofLabels) Then
MsgBox "Please indicate the number of labels you want to print", _
vbOKOnly, "Error"
DoCmd.GoToControl "txtNumberOfLabels"
Exit Sub
End If

rst.Open "[Temporary_Contacts]", CurrentProject.Connection, _
adOpenDynamic, adLockPessimistic

For i = 1 To Me!txtNumberofLabels.Value
With rst
!Date = Me.Date
!FirstName = Me.FirstName
!LastName = Me.LastName
!Company = Me.Company
!HostsName = Me.HostsName
!VehicleReg = Me.VehicleReg
End With
Next i

DoCmd.OpenReport "Single Visitor Pass", acViewPreview

Set rst = Nothing

Exit Sub


MsgBox Err.Number & ": " & Err.Description, vbOKOnly, "Error"
DoCmd.SetWarnings True
Set rst = Nothing

End Sub

Many thanks

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Share your knowledge

Related Discussions

Related Forums