Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2002
    Location
    Western Mass.
    Posts
    5

    Unanswered: ADO recordset problems in 2002

    Hi all

    I've been having problems getting this to work: We have a search function which creates a recordset in ADO from the given search parameters, through a SQL statement. I want to be able to then navigate throught that recordset via a button click. This is to allow viewing of multiple identical entries in the table. (patients actually, it is a healthcare-oriented database project). When I attempt a rs.MoveNext method on a button_click event I get runtime error 3704 - "Operation is not allowed when object is closed". Argh! How do I keep that recordset open????

    email me if you have ideas and I'll send code if needed.

    Thanks all!

    Fred

  2. #2
    Join Date
    Mar 2002
    Location
    Santos - SP - Brazil
    Posts
    13
    Hi SpaceCowboy,

    Before do you use rs.movenext, do you need to use the Open method the sintax is describe in following.

    rs.Open "select..."
    or
    rs.Open "[Table]"

    Regards,
    Rafael

  3. #3
    Join Date
    Apr 2002
    Location
    Western Mass.
    Posts
    5
    Hey, thanks. I've already opened the recordset, in my Find sub. here is what works.... I can do an initial search, which creates the recordset based on my search parameters and populates the controls on my form with the appropriate information, but if I want to move to the next duplicate, the error in generated, because is says the recordset is closed. I have made the find procedure a seperate sub and so the button_click() event is outside that sub. Here is a sample of the working code:

    Sub Find()

    Dim Conn As New ADODB.Connection
    Dim rsPtInfo As New ADODB.Recordset
    Dim strCriteria As String, strLastN As String, strStreet As String, strSQL As String, strFields As String
    Dim strRsFields As Variant
    Dim strID As Double
    Dim MultSrch As Integer

    Set Conn = CurrentProject.Connection

    If Not IsNull(Me![txtID]) Then
    strID = Val(Me![txtID])
    strSQL = "SELECT tblMailingList.* FROM tblMailingList WHERE tblMailingList.ID=" & strID & " "
    rsPtInfo.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
    rsPtInfo.MoveFirst
    End If

    'populate controls if match if found. If no match is found a messagebox pops up.
    End Sub

    Sub cmd_Find_click()
    If Found= True Then 'Found flag is a global boolean which indicates that
    rsPtInfo.MoveNext 'a recordset has been created with at least one
    'matching patient based on the search.

    ***This is where the error occurs***
    End If

    --End Code--

    this all works fine until I want to move through the recordset by clicking on a button (cmd_Find).

    thanks

    Fred

  4. #4
    Join Date
    Mar 2002
    Location
    Santos - SP - Brazil
    Posts
    13
    Hi SpaceCowboy "Fred",

    rsPtInfo recordset is a local variable, declared in Sub Find, because that rsPtInfo doesn't exist in Procedure cmd_Find_click().
    Ok?

    Regards,
    Rafael

  5. #5
    Join Date
    Apr 2002
    Location
    Western Mass.
    Posts
    5
    yeah, that sure is the problem. Is there any way to extend it to a global scope or extend it outside its own procedure?


    Thanks for the help, mi amigo!

  6. #6
    Join Date
    Mar 2002
    Location
    Santos - SP - Brazil
    Posts
    13
    Hi,


    You need to create the recordset in a Global declaration.

    Ok meu Amigo?

    Um Abraço ( Regards),

    Rafael

  7. #7
    Join Date
    Apr 2002
    Location
    Western Mass.
    Posts
    5
    Gracias! I appreciate that!

    Fred

  8. #8
    Join Date
    Mar 2002
    Location
    Santos - SP - Brazil
    Posts
    13
    I'm not speak spanish, only portuguese soo my friend ...


    Obrigado a vc (thanks for you),


    Rafael

  9. #9
    Join Date
    Apr 2002
    Location
    Western Mass.
    Posts
    5
    Pesaroso! Eu não soube. Eu espero que este traduza bem. Obrigado para a ajuda!


Posting Permissions

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