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.