[RESOLVED] question about Rs.Open Rs.Close-VBForums
Results 1 to 17 of 17

Thread: [RESOLVED] question about Rs.Open Rs.Close

  1. #1

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Resolved [RESOLVED] question about Rs.Open Rs.Close

    if my code is like this
    Code:
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM Customers", CN, adOpenStatic, adLockOptimistic
    If rs.RecordCount = 0 Then
        MsgBox "no Customers Found ", vbExclamation
        Exit Sub
        Else
    FrmPhoneBook.Show 1
    End If
    do i need to add Rs.Close?
    if so where do i add this?

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    8,630

    Re: question about Rs.Open Rs.Close

    You should always close recordsets that you open, where you should close it depends on what you are doing with it.
    In general you close it when you are done with it
    Code:
    Rs.Open .......
    ' Do something with RS
    Rs.Close
    Or
    Code:
    Set RS=Cn.Execute .....
    'do something with RS
    Rs.Close
    VB does its best to clean up behind you but is always better to close the objects when done with them to free up memory and avoid possible unforeseen issues.


    Of course in your case as usual you have an Exit Sub in there that should not be there and that could cause an issue.

    You really need to get out of that habit, it is almost as bad as using Goto

  3. #3

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Re: question about Rs.Open Rs.Close

    so after every set Rs and Rs.Open
    i need to use a Rs.Close?

    second question
    how do i check in my project if there is any connection or any Rs open?

  4. #4

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Re: question about Rs.Open Rs.Close

    and i tell you why i put exit sub
    beacuse somebody told me that if i dont put exit sub then the form will display
    e.x
    if abs = 1 then
    msgbox " wrong password"
    exit sub
    else


    you understand DM?

  5. #5
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    8,630

    Re: question about Rs.Open Rs.Close

    Set really has nothing to do with it

    When you do a Cn.Execute as you often do with an RS as the target that is the same as if you did RS.OPEN

    You should close any recordsets that you open as well as connections, files and anything else that you open in your program.

    in the other question if you mean through code then you check the state property of the recordset or connection
    but if you mean hw to find places where you are not closing them I would say search for .Execute and .Open in most of those cases you will likely find you have opened a recordset that you did not close

  6. #6

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Re: question about Rs.Open Rs.Close

    i understand
    well i take you advice and removed a exit sub from a code
    and stiil it lets me do something when i told him no
    please tell me why?
    Code:
    If CmbUser.ListIndex = -1 Then
    MsgBox "choose a user from the list", vbExclamation
    CmbUser.SetFocus
     'Exit Sub when i removed this the procedure keep going when it is not supposed to :confused:
    End If
    do you use exit sub?
    if so in what conditions?

  7. #7
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    8,630

    Re: question about Rs.Open Rs.Close

    Quote Originally Posted by salsa31 View Post
    and i tell you why i put exit sub
    beacuse somebody told me that if i dont put exit sub then the form will display
    e.x
    if abs = 1 then
    msgbox " wrong password"
    exit sub
    else


    you understand DM?
    Let me try and explain this one for you

    Code:
    Private SomeSub()
    if abs = 1 then
        msgbox " wrong password"
        exit sub
    else
        form1.show
    End If
    End Sub
    Code:
    Private SomeSub()
    if abs = 1 then
        msgbox " wrong password"
        Goto Done
    else
        form1.show
    End If
    Done:
    End Sub
    Code:
    Private SomeSub()
    if abs = 1 then
        msgbox " wrong password"    
    else
        form1.show
    End If
    End Sub
    All three of those will do exactly the same thing except the top two have a line of code that causes a jump to the end the third one is correct and does no jumps.
    One major issue with using Exit Sub or Goto is that you have to pay attention to every line of code to see if there is something there that disrupts the flow and it can make it much harder to read your code and find issues with it

    Code:
    If Something Then
         Do Something
         Exit Sub
    Else
         Do Something Else
    End If
    
    Do another thing
    
    End Sub
    Now in this case the line Do another thing would execute no matter what the If test is without the exit sub in there. The exit sub causes it to skip that line of code but if the code was slightly different it would not need to jump at all and would do the same thing
    Code:
    If Something Then
         Do Something     
    Else
         Do Something Else
        Do another thing
    End If
    End Sub
    In most cases where you have used exit sub it would not be needed if the If block was correct and in some cases the If block is correct and it is not needed. It is ok to use it now and then but it should not be used often unless used as part of an error handler where it is required, as is Goto.

    Code:
    Sub SomeSub()
    On Error Goto ErrHandler
    
    'Some lines of code
    
    Exit Sub
    ErrHandler:
    'Lines of code to handle the error
    End Sub
    This is a common way that exit sub is used here the reason is to skip the error handler when no error has occurred.

  8. #8

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Re: question about Rs.Open Rs.Close

    that is very explained thank you very much DM

  9. #9
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    8,630

    Re: [RESOLVED] question about Rs.Open Rs.Close

    One more example
    Code:
    If Something then
          Msgbox "You can't do that"
          Exit Sub
    End If
    
    If SomethingElse then
        'do some stuff
    End IF
    End Sub
    Now in the code above the exit sub prevents the second If test from executing when the first test is true because the exit sub will execute and cause the code to jump down to the end sub line

    However you could write that same piece of code like this

    Code:
    If Something then
          Msgbox "You can't do that"      
    Else
        If SomethingElse then
            'do some stuff
        End IF
    End If
    End Sub
    Which uses a structured If Block to control the program flow and is much more readable without the abrupt jump in execution

    Both pieces of code will do exactly the same thing

  10. #10
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    4,036

    Re: [RESOLVED] question about Rs.Open Rs.Close

    In this code:
    Code:
    if abs = 1 then
     msgbox " wrong password"
     exit sub 
     else
    If abs is equal to 1 then you bring up a msgbox. That is ALL. You have an else there, SO, anything between that else and your End Sub will not be executed ANYWAY. However, in a lot of your code you have shown on this forum, you use Exit Sub when you SHOULD HAVE HAD an ELSE. In all those cases, you should revisit your code and rewrite your IF-Else statement so that you don't need an Exit Sub.

    For example, you do things LIKE:

    Code:
    Private Sub COmmand1_click()
    IF a = x then
    'do this code
    Exit Sub
    End if
    Label1.caption = cstr(a)
    label2.caption = cstr(x)
    End sub
    This code is more appropriately written with an Else statement, like thus:

    Code:
    Private Sub COmmand1_click()
    IF a = x then
    'do this code
    else
    Label1.caption = cstr(a)
    label2.caption = cstr(x)
    end if
    End sub
    See? The only thing that gets done in that second (like the first--but is bad practice) is that if a equals x, you 'do some code. The stuff inbetween the else and end if never get done. It ONLY gets done if a does NOT equal x.

    Understand SA?

  11. #11
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    4,036

    Re: question about Rs.Open Rs.Close

    Hmmm,,,I see DM just posted same stuff....sorry for interrupting.

  12. #12

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Re: [RESOLVED] question about Rs.Open Rs.Close

    thats ok sami
    so DM you are telling me that if there is a else then the exit sub dosnt have any need?

  13. #13
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    8,630

    Re: [RESOLVED] question about Rs.Open Rs.Close

    Well everything depends on how your code is written

    Code:
    If something then
         'Some code here will execute only if the If tests as true
    Else 
         'Some code here will execute only if the If test Is false
    End If
    ' Once the code in either part has finished the code will go here
    
    'Code here would execute no matter what the If test was
    
    End Sub
    So yes in general you should never need an exit sub unless you have a procedure error trap at the bottom like in the one example I showed.
    This is normally the only place I use them when writing a program
    I do however get lazy and use them sometimes when I am making a change and don;t have much time
    It can be easier to through a exit sub in the middle of a large procedure rather than creating the proper structure but is never the best way to go.

  14. #14
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    8,630

    Re: [RESOLVED] question about Rs.Open Rs.Close

    It is also better to handle the most likely case first and the exception in the else portion

    So for example rather than writing this
    Code:
    If Text1.Text="" Then
         msgBox "Warning"
         Exit Sub
    End If
    
    'Do something
    
    'or
    
    If Text1.Text="" then
        msgBox "Warning"
    Else
        ' do stuff
    End If
    It is better to write it like

    Code:
    If Not Text1.Text="" Then
          'do stuff
    Else
          msgbox "Warning"
    End If
    Of course either way will work but the last one will be the fastest in most cases because there will usually be something in the text box

  15. #15

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Re: [RESOLVED] question about Rs.Open Rs.Close

    mmm
    tell me what you do in this case
    Code:
    If FrmEmployees Is ActiveForm Then
    MsgBox "Please close all windows before exiting the program", vbExclamation
    cancel = True
    Exit Sub
    End If
    
    
    If FrmEnterLog Is ActiveForm Then
    MsgBox "Please close all windows before exiting the program", vbExclamation
    cancel = True
    Exit Sub
    End If
    what you do instead of the Exit Sub?

  16. #16
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    8,630

    Re: [RESOLVED] question about Rs.Open Rs.Close

    Is that all of the code in the sub?

    Since there is no line for the top and end of the sub I can't tell

    If that is all then you would do something like this

    Code:
    If FrmEmployees Is ActiveForm Then
        MsgBox "Please close all windows before exiting the program", vbExclamation
        cancel = True
    ElseIf FrmEnterLog Is ActiveForm Then
        MsgBox "Please close all windows before exiting the program", vbExclamation
        cancel=true
    End If
    Or better still you could
    Code:
    If FrmEmployees Is ActiveForm Or FrmEnterLog Is ActiveForm Then
        MsgBox "Please close all windows before exiting the program", vbExclamation
        cancel=true
    End If
    or you could use a select case or ....
    Last edited by DataMiser; Nov 2nd, 2013 at 06:27 PM.

  17. #17

    Thread Starter
    PowerPoster salsa31's Avatar
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    3,252

    Re: [RESOLVED] question about Rs.Open Rs.Close

    that definitely answered my question
    tnx for the info realy helpful

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.