Question

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; https://www.techrepublic.com/article/how-to-print-one-or-more-labels-for-a-single-access-record/ 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
.AddNew
!Date = Me.Date
!FirstName = Me.FirstName
!LastName = Me.LastName
!Company = Me.Company
!HostsName = Me.HostsName
!VehicleReg = Me.VehicleReg
.Update
End With
Next i

DoCmd.OpenReport "Single Visitor Pass", acViewPreview

rst.Close
Set rst = Nothing

Exit Sub

errHandler:

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

End Sub

Many thanks
Kate
0 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Share your knowledge
Back to Software Forum
0 total posts (Page 1 of 1)  

Related Discussions

Related Forums