Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005
    Posts
    1

    Unanswered: Keeping recordset open after code execution stops

    I'm trying to create a userform in Excel that is similar to a form in Access.

    I first opened the database and recordset:
    Private Sub UserForm_Activate()

    Dim db As ADODB.Connection
    Dim rst As ADODB.Recordset
    Set conn = New ADODB.Connection
    With conn
    .Provider = "Microsoft.JET.OLEDB.4.0"
    .Open "C:\Documents and settings\kokopelli\my documents\clients.mdb"
    End With
    Set rst = New ADODB.Recordset
    With rst
    .ActiveConnection = conn
    .Open "Main", conn, adOpenDynamic, adLockBatchOptimistic
    End With

    With rst
    .MoveFirst
    UserForm1.TextBox1.Value = !ID
    UserForm1.TextBox2.Value = !First_Name
    UserForm1.TextBox3.Value = !Middle_Init
    UserForm1.TextBox4.Value = !Last_Name
    UserForm1.TextBox5.Value = !Date_First_Intaked
    End With
    End Sub

    When I use a command button to increment to the next record (rst.movenext), the recordset is gone and get an "Object required" error.

    I then declared the database and recordset object publicly in the workbook declaration page:
    Public db As ADODB.Connection
    Public rst As ADODB.Recordset
    Same error

    How do you keep that recordset object in memory and the cursor static until told to move?
    Please help. Dave

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Try dimming your rst at the Modual level. Put this line at the top of your Module.

    Public rst As ADODB.Recordset

    Make sure you remove the rst declaration from the sub procedure. I assume you created a separate sub procedure for MoveNextRecord() and MovePreviousRecord() referenced from your controls code in the userform.

    Public Statements in the Worksheet Module are only available within that worksheet Module not other worksheets or modules. You should just use the Worksheets Module for worksheet events.
    ~

    Bill

Posting Permissions

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