Trying to write code to generate 1 record as a label - Error 3219

[EBG]

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 modify and print as a visitor's pass.

I have used this article from this site; https://www.techrepublic.com/article...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

[pbaldy]

Temporarily comment out the On Error... line and run the code. That should let you hit Debug on the first error and see where it is. From the sound of it, I'd guess a field name is spelled wrong.

[EBG]

Temporarily comment out the On Error... line and run the code. That should let you hit Debug on the first error and see where it is. From the sound of it, I'd guess a field name is spelled wrong.

Thanks Paul, I wasn't aware of the comment out function but as you stated, it highlighted a field name. When I checked the properties box for each name, this was different to what the actual column name was.

Thanks for your help.

[pbaldy]

No problem Kate, and welcome to the site by the way!

[ssanfu]

Be aware that "DATE" is a reserved word in Access and shouldn't be used as an object name. Maybe use "VisPassDate" or "PassDate".....

I know this is not a mission critical dB, but it is something to be aware of....

[EBG]

Be aware that "DATE" is a reserved word in Access and shouldn't be used as an object name. Maybe use "VisPassDate" or "PassDate".....

I know this is not a mission critical dB, but it is something to be aware of....

Thanks Steve, I'll look into amending that. I know when I created the field a warning did pop up about this but I'll be honest I wasn't entirely sure what it meant.