Solved RecordSet not appearing in VBA dropdown list (1 Viewer)

[Weekleyba]

I'm trying to learn about RecordSets.
When declaring a rst as a RecordSet, it does not show up in the dropdown.
Why is that?

 

[theDBguy]

Try prefixing it with DAO.

Dim rst As DAO.Recordset

 

[CJ_London]

or ado - depends on what type or recordset you want - may need to add the MS ADO library

 

[Weekleyba]

Neither one is working.
With either the DAO.Recordset or the ADO.Recordset, it throws an Compile error: User-defined type not defined.

To add them, I would look in Tools/References correct? I do not see either but, I'm not exactly sure what I'm looking for.

 

[Weekleyba]

Here's my code. This is from an online class I'm taking through udemy.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
On Error GoTo ErrHandler
'Private is it only works here.
'KeyCode and Shift are buckets to hold values
Dim ctl As Control
Dim fldName As String
Dim rst As DAO.Recordset

'MsgBox (" The keycode is " & KeyCode)
Select Case KeyCode
   Case vbKeyEnd
      KeyCode = 0  'turns off the key stroke, so when you press it, it won't move.
      DoCmd.RunCommand acCmdRecordsGoToLast
   Case vbKeyHome
      KeyCode = 0
      DoCmd.RunCommand acCmdRecordsGoToFirst
   Case vbKeyUp
      KeyCode = 0
      DoCmd.RunCommand acCmdRecordsGoToPrevious
   Case vbKeyDown
      KeyCode = 0
      DoCmd.RunCommand acCmdRecordsGoToNext
   Case vbKeyRight, vbKeyLeft
   Case 48 To 57, 65 To 90 '48-57 number keys, 65-90 upper/lower letters
      Set ctl = Screen.ActiveControl
      fldName = clt.Name
      ' [LastName] Like 'SMI*'
      SrchVal = SrchVal & Chr(KeyCode)
      KeyCode = 0
      SrchCrit = "[" & fldName & "] Like '" & SrchVal & "*'"
      Set rst = Me.RecordsetClone 'this is a duplicate of the control source of the form.
      rst.findfirst SrchCrit
      If rst.NoMatch Then
         MsgBox (" Record not found! ")
      Else
         Me.Bookmark = rst.Bookmark
      End If
      rst.Close
      
   Case 27 'esc key
      SrchVal = ""
      KeyCode = 0
   Case 122 'F11
      'Shift = 1, Ctrl = 2, Alt = 4
      If Shift <> 4 Then
         KeyCode = 0
      End If

    Case Else
      KeyCode = 0
      
End Select
Exit Sub
ErrHandler:
   Select Case Err.Number
      Case 2046
      Case Else 'This means if there is any other kind of error it will show what it is.
        MsgBox (" Error " & Err.Number & " " & Err.Description & "! ")
   End Select
   Resume Next

End Sub

 

[Weekleyba]

Found it!
And now it works.

 

[CJ_London]

what version of access are you using? - DAO is standard for most versions of Access, certainly 2007 and later. But it comes in the Microsoft Office 16.0 Access Database Engine Object Library which should be defined for any new app you create.

The DAO 3.6 library is still valid but perhaps does not have all the features of the office library.

 

[Weekleyba]

Using 2016.
I was using 2019 but when I decided to encrypt the back ends of a couple database, I ran into trouble. Not all users had 2019 and thus did not have 64 bit encryption. So now I’m back to 2016 with 32 bit encryption. That’ll probably start another topic I suppose.