Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2017
    Posts
    2

    Unhappy Unanswered: Compile error when trying to display an ADO recordset in a form

    Hello all,

    I am attempting to run a query, and display the ADO recordset in a form. However I keep getting an error that says "Compile error: Invalid use of Me keyword."

    Though I am not new to Access, I am new to using ADO...anything. Any and all help is much appreciated! Below is my code:

    Code:
    Option Compare Database
    
    Public Sub automate_query()
    
    Dim strSQL As String
    
    Dim adoRecSet As New ADODB.Recordset
    Dim rs As ADODB.Recordset
    Dim connDB As ADODB.Connection
    
    Set connDB = CurrentProject.Connection
    
    strSQL = "SELECT DISTINCT TESTTBL01.Control_Zone_ID, TESTTBL01.[CVR_ Start_Date], TESTTBL01.[Registration Status] FROM TESTTBL01"
    
    Set rs = New ADODB.Recordset
    With rs
        Set .ActiveConnection = connDB
            .Source = strSQL
            .LockType = adLockOptimistic
            .CursorType = adOpenDynamic
            .Open
    End With
    
    
    DoCmd.OpenForm "frm_ado_test"
    
    Set Me.Recordset = rs
    
    Set rs = Nothing
    
    End Sub

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    630
    Provided Answers: 35
    There's really no reason to use ADO in access. Everything is connected to the db already.

    All of the above can be done with 1 command:
    docmd.openForm "myForm"


    Also, ME is for controls on a form.
    recordsets are not on a form.

  3. #3
    Join Date
    Sep 2017
    Posts
    2
    Thank you for your reply. I realize that everything within this database is connected, but I am using Access as a shell, and before I go testing this with our SQL database, I want to make sure the code is working as it should. That is why I am using ADO.

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    805
    Provided Answers: 2
    Hi

    You can only use the Me key word (Me.Recordset) when the code is written in a class object (a form is in effect a class object).

    You could try this
    Code:
    DoCmd.OpenForm "frm_ado_test"
    
    Set Forms("frm_ado_test").Recordset = rs
    I don't know if this will work though.

    Alternatively, you could just cycle through the records and print to the Debugger to see if what records are returned, if any!?

    MTB

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •