Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    close set.....connection... (6.0)

    head fired!!!!!!!!

    When i click on button4 "opearation not possible if object is open....." error 3705
    WHY!!!!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: close set.....connection... (6.0)

    You don't close any of the Connection and Recordset objects after using them, so next time when you try to open them, they are still open. Before a line such as

    Set rst1 = Nothing

    you should insert

    rst1.Close

    and similar for the other Connection and Recordset objects.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: close set.....connection... (6.0)

    Sal,
    In addition to what Hans has said, you have opened 3 connections to the same database. It would be far better to simply reuse one connection and then terminate it when you have finished.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    i have make you suggestion but after, the textbox not is filled during the scroo of scrollbar?!?
    in effect the cuesror scroll but the record no?!?!?

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    Hans... but after this modify the textbox in userform not is filled with value of record set...???!!!!
    Work only the scrollbar and the cursor of scrollbar go up and down witouth to fill the texttbox...(?)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: close set.....connection... (6.0)

    It's hard to follow what is going on, but I would recommend the following (also based on Rory's suggestion):
    - Instead of cn, cnc1 and cnt1, use only one Connection object for STORICO_INPS.mdb. Open this connection when the form is loaded, and close it when the form is unloaded.
    - Use this single Connection object when opening recordsets for STORICO_INPS.mdb.
    - rst1 is used only in CommandButton4_Click, so open rst1 at the beginning of this procedure, and close it at the end of this procedure. You can declare rst1 in this procedure instead of at the top of the module.
    - rsc1 is used only in Conta_Tot, so open rsc1 at the beginning of this procedure, and close it at the end of this procedure. You can declare rsc1 in this procedure instead of at the top of the module.
    - rs is used in several procedures, so open it when the form is loaded, and close it when the form is unloaded.

    Also:

    - cnt and rst are used only in CARICA_COMBO, so open them at the beginning of this procedure, and close them at the end of this procedure. You can declare cnt and rst in this procedure instead of at the top of the module.

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    Hans... good word, but not understand all(?!)
    step by step
    first part of corection is correct:

    'Sub UserForm_Activate
    Dim cn As New ADODB.Connection, rs As New ADODB.Recordset
    'Sub UserForm_Activate

    'Sub CARICA_COMBO
    Dim cnt As New ADODB.Connection, rst As New ADODB.Recordset
    'Sub CARICA_COMBO

    'Sub CommandButton4_Click
    ' Dim cnt1 As New ADODB.Connection, rst1 As New ADODB.Recordset
    'Sub CommandButton4_Click

    'Sub CONTA_TOT
    'Dim cnc1 As New ADODB.Connection, rsc1 As New ADODB.Recordset
    'Sub CONTA_TOT

    correct...?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: close set.....connection... (6.0)

    You don't need cnt1 and cnc1 at all, you can use cn instead.

    The only variables you use in several procedures are cn and rs. The others are used in one procedure only, so you should move their declarations to the procedure they are used in. In my previous reply, I indicated which variables belong to which procedure.

  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    now, in this case with this code i can comment the code in this mode:
    and use the first conn opened in Sub UserForm_Activate...

    Private Sub CONTA_TOT()

    '''''''''''''''''
    'cnc1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:TEST_MDBSTORICO_INPS.MDB;Persist Security Info=False"
    '''''''''''''''''
    rsc1.Open "SELECT Count(PROVA13) AS CONTA FROM INPS_02 WHERE PROVA1='" & ENTER_PROVA1 & "'", cnc1, adOpenStatic, adLockReadOnly

    CONTA_RISP = rsc1.Fields("CONTA")

    Label41.Caption = CONTA_RECORD
    Label49.Caption = CONTA_RISP
    Label51.Caption = Label41.Caption - Label49.Caption

    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: close set.....connection... (6.0)

    If cnc1 is the connection you open in Form_Activate, you can use it here.

    Since rsc1 isn't used elsewhere, you should declare (Dim) it here, and close it at the end of this procedure.

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    tks but not for me...

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: close set.....connection... (6.0)

    If you want to clear PROVA12 en PROVA13, change

    rs3!PROVA12 = Me.TextBox11.Text
    rs3!PROVA13 = Me.ComboBox1.Text

    to

    rs3!PROVA12 = Null
    rs3!PROVA13 = Null

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    wow!!!!!!!
    AFTER 6 HOURS OF WORK ALL IS FINE....
    Tks to you!

    but.... naturally but...

    have this code to update mdb. I use for index the filed prova29 is a unique id.
    I filtre the record with this is this correct?

    Longish code moved to attachment by HansV

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    project 85%... finished ...

    but how to make the reverse mode of this sub????(in effect i would want the delete/clear) the value in prova12 and prova13...

    i use this sub to insert data in mdb...

    Code moved to attachment by HansV

  15. #15
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: close set.....connection... (6.0)

    project attached, change the path of mdb...
    substituite this code in userform MODIFICHE...
    to test the code insert in SELECTION userform 4500 or 4561, or 4555

    Very long code fragment removed by HansV since it's also available in the attachment

Posting Permissions

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