By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,773 Members | 2,583 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,773 IT Pros & Developers. It's quick & easy.

Close recordset (snapshot)

P: 34
Hi,
Do I need to close a recordset on form_load() if it is a dbOpenSnapshot?

Thanks
Apr 10 '17 #1

✓ answered by NeoPa

Ah. With that code displayed it now becomes clear what your question should have included!

Here are some tips :
  1. When using Recordsets in VBA it's important to specify which type of Recordset you mean as both DAO & ADODB have Recordset objects and if you don't specify then your code is ambiguous depending on which library is first in the list. So, Dim rs As DAO.Recordset
  2. It's actually not critical that you close the Recordset at the end of the procedure as you close the form that it's declared within anyway. Thus it goes out of scope and is tidied away automatically for you. That said, it's certainly good practice to close and tidy any objects created within your code.
  3. The fact that the Recordset is a spnapshot doesn't really come into it.
Hopefully these tips will answer all your questions.

Share this Question
Share on Google+
6 Replies


jforbes
Expert 100+
P: 1,107
Seeing your code would be helpful in answering this question.

Generally speaking, it's best to explicitly close a RecordSet when you are finished with it. The only time I can think of that you wouldn't would be when opening a RecordSet through code and then setting the Form's RecordSet to that of the opened RecordSet. I don't normally set a Form's RecordSet in this manner, so I'm just going from memory of what I've seen others do.
Apr 10 '17 #2

P: 34
this is the code, still working on it though. I never used snapshot before but reading about it I thought in this case it might fit ok.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.     On Error GoTo ErrorHandler
  3.  
  4.     Dim db As Database
  5.     Dim rs As Recordset
  6.     Dim cmdCategory As CommandButton
  7.  
  8.     Set db = CurrentDb()
  9.     Set rs = db.OpenRecordset("tblEmployees", dbOpenSnapshot, dbReadOnly)
  10.  
  11. 'loop through recorset and find value
  12.     rs.FindFirst "EmployeeName='" & Me.txtUserName & "'"
  13.  
  14. 'check username
  15.     If rs.NoMatch = True Then
  16.         Me.lblWrongUser.Visible = True
  17.         Me.txtUserName.SetFocus
  18.         Exit Sub
  19.     End If
  20.     Me.lblWrongUser.Visible = False
  21.  
  22. 'check password
  23.     If rs!Password <> Nz(Me.txtPassword, "") Then
  24.         Me.lblWrongPassword.Visible = True
  25.         Me.txtPassword.SetFocus
  26.         Exit Sub
  27.     End If
  28.     Me.lblPassword.Visible = False
  29.  
  30.     DoCmd.OpenForm "frmMain"
  31.     DoCmd.Close acForm, Me.Name
  32.  
  33. 'close recordset
  34.     rs.Close
  35.  
  36. ExitSub:
  37.     Set rs = Nothing
  38.     Set db = Nothing
  39.     Exit Sub
  40. ErrorHandler:
  41.     MsgBox "Error No: " & Err.Number & vbNewLine _
  42.          & "Error Details: " & Err.Description & vbNewLine _
  43.          & "Calling Sub: frmLogin\cmdLogin_Click()"
  44.     Resume ExitSub
  45. End Sub
Apr 10 '17 #3

NeoPa
Expert Mod 15k+
P: 31,417
This is a bit of a strange question. If the recordset you're talking about is form's recordset then you neither need to, nor can, close it without closing the form.
Apr 10 '17 #4

NeoPa
Expert Mod 15k+
P: 31,417
Ah. With that code displayed it now becomes clear what your question should have included!

Here are some tips :
  1. When using Recordsets in VBA it's important to specify which type of Recordset you mean as both DAO & ADODB have Recordset objects and if you don't specify then your code is ambiguous depending on which library is first in the list. So, Dim rs As DAO.Recordset
  2. It's actually not critical that you close the Recordset at the end of the procedure as you close the form that it's declared within anyway. Thus it goes out of scope and is tidied away automatically for you. That said, it's certainly good practice to close and tidy any objects created within your code.
  3. The fact that the Recordset is a spnapshot doesn't really come into it.
Hopefully these tips will answer all your questions.
Apr 10 '17 #5

P: 34
I'll include the code next time, you are right. Will use tag too.

1) Got it wrong in 2 procedures. Dao is first in library.
2) Got it
3) Got it

Excellent! they certainly answer them, Thanks
Apr 11 '17 #6

NeoPa
Expert Mod 15k+
P: 31,417
I'm always pleased to help Neruda, but especially when someone is so ready to learn and progress. Good for you :-)
Apr 11 '17 #7

Post your reply

Sign in to post your reply or Sign up for a free account.