VB6 Help

[Hightower]

I have to code something in VB6 (don't ask why VB6 but I just do ). Here is the code. Can somebody help me diagnose the problems I am getting?

Private Sub Form_Activate()
    ' Connect to the database and open users table
    Set db = DBEngine.OpenDatabase(App.Path & "\db\mon_bud.mdb")
    Set rs = db.OpenRecordset("tbl_users", dbOpenDynaset)
    
    ' Check to see if users exist in db
    On Error GoTo err_hand1
    rs.MoveFirst
    lblUser1.Caption = rs!Name & "'s Income"
    
    On Error GoTo err_hand2
    rs.MoveNext
    lblUser2.Caption = rs!Name & "'s Income"
    
    Set rs = Nothing
    Exit Sub
    
err_hand1:
    If Err.Number <> 0 Then
        lblUser1.Caption = "Please define users"
        lblUser2.Caption = "Please define users"
        Set rs = Nothing
        Exit Sub
    End If
        
err_hand2:
    If Err.Number <> 0 Then
        lblUser2.Caption = "User 2 not in use"
        Set rs = Nothing
        Exit Sub
    End If
        
End Sub

I have two labels. I want to work it so if no users exist in the db the labels show Please define users. This works when no users are in db. I also want it where if two user exist it will show the name of those users in either label - this works when two users are in the db.

Finally, I want it so that when only one user is in the db it will show this name in the first label and change the second label to User 2 not in use. This is the part that doesn't work. It doesn't come up with an error or anything, but instead the whole program (including VB6) crashes and closes when only one user is in the db.

Any help VB6 guru's?

[srochford]

I don't have VB6 installed at the moment but if you're just checking for 0,1 or 2 users (and there will never be more) then I think you want to just look at the count of records and use a case statement based on that.

Try something like this:

Private Sub Form_Activate()
    ' Connect to the database and open users table
    Set db = DBEngine.OpenDatabase(App.Path & "\db\mon_bud.mdb")
    Set rs = db.OpenRecordset("tbl_users", dbOpenDynaset)

    iCount=rs.recordcount
    select case iCount
       case 0
        lblUser1.Caption = "Please define users"
        lblUser2.Caption = "Please define users"

       case 1
        lblUser1.Caption = rs!Name & "'s Income"
        lblUser2.Caption = "User 2 not in use"

       case 2
        lblUser1.Caption = rs!Name & "'s Income"
        rs.movenext
        lblUser1.Caption = rs!Name & "'s Income"
    end select
    
    Set rs = Nothing
    Exit Sub
End Sub

[Hightower]

Thanks for that. I've used to code and thought it was going to work but it iCount either equals 0 if no records exist or 1 if 1 or more.

I have two records and it still returns 1. 3 records and it still returns 1.

[Hightower]

Scratch that:

rs.movelast
iCount = rs.recordcount
rs.movefirst

That code above sorted it.