subscript out of range

[ktwk]

hi, i am new here, i keep getting this error message "subscript out of range" ever since i added array to my program, i am trying to make a login form, i have a table with username, and password as column, i am trying to store everything into the array on_click which will later be use in an IF statement. Many Thanks.

Option Compare Database
Private Sub lgin_Click()
On Error GoTo Err_lgin_Click
Dim stDocName As String
Dim stLinkCriteria As String
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim str4() As String
Dim cnn1 As ADODB.Connection
Dim rst1 As Recordset
Dim fld1 As ADODB.Field

Dim a As Integer

str1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\db1.mdb;"

Set cnn1 = New ADODB.Connection
cnn1.Open str1

Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = cnn1

str2 = "SELECT LoginTable.Username, LoginTable.Password FROM LoginTable"

rst1.Open str2, , , , adCmdText
a = 0
Do Until rst1.EOF
For Each fld1 In rst1.Fields
str3 = str3 & fld1.Value & vbTab
Next fld1
str4(a) = str3
rst1.MoveNext
a = a + 1
Loop

rst1.Close
cnn1.Close
Set fld1 = Nothing
Set rst1 = Nothing
Set cnn1 = Nothing

Exit_lgin_Click:
Exit Sub

Err_lgin_Click:
MsgBox Err.Description
Resume Exit_lgin_Click

End Sub



KT

[OBP]

KT, how many records does the table hold, you should establish how many using rst1.recordcount and then re dim the str4() to te value in rst1.recordcount or do it directly with
dim str4(rst1.recordcount) after opening the recordset not before.

[Norie]

KT

You've not dimensioned any array anyway.

And you don't actually appear to have an array called on_click?

Or do you mean you are using the on_click event?

OBP

You can't Dim dynamically like your 2nd suggestion.

[Tommy]

Norie

OBP left off the re in redim other than that I am not sure why you say that can't be done. Maybe because a rst1.movefirst then rst1.movelast wasn't done? Can you elaberate please?:dunno

[Norie]

sTommy

You cannot use this.


Dim str4(rst1.recordcount)
Maybe it's a typo, maybe I've misread the post but using Redim had already been suggested so I assumed that's what was being suggested.

[ktwk]

-sorry norie, i mislead you, i actually mean
" i am trying to store everything into the array str4 when i click my command button(on_click) which will later be use in an IF statement" ,
yes, i am using the on_click event, i am using MS ACCESS 2000, i can only see "click" in my compiler's event.

-tommy, thanks for reminding, now i know why my result start at the middle of my table before i added array to my program.

-OBP, thanks, i think i get what u mean, i am newbie to vba, having experience with java and C

i added this to my program


Dim b As Integer
b = 0
b = rst1.RecordCount
MsgBox "" & b, vbOKOnly
ReDim str4(b) As String


but my msgbox give me a "-1", i have uploaded my file, can u guys take a look at my "LoginTable" under Table, is there anything wrong with it, the program i show u guys is the "Login" Form, many thanks, i begin to think its my database that is the problem not my program.

[Norie]

I've downloaded the attachment and the first thing I want to ask is why you are creating a new connection to an already open database?

[OBP]

ktwk,Norie is correct about the new connection and also about using "Dim" with recordcount, it has to be ReDim.
This code will do the first part of what you want, I will leave the stepping through the recordset to you.

Dim rs As Object, recount As Integer, pass As String, passwords() As Variant

Set rs = CurrentDb.OpenRecordset("LoginTable")
recount = rs.RecordCount
ReDim passwords(rs.RecordCount)
pass = rs!Password
MsgBox recount & " " & pass

recount holds the number of records in the recordset and pass holds the first record's password.

[ktwk]

ok, let me explain, "open database" mean i can directly open the table and edit it? sorry, i am very new to access, my previous encounter with database is with oracle (10g or 9i) and mysql where i store my data using sql programming, this is my first project with Access, dont really understand how it works :), very very sorry i don't understand u :doh:

thanks guys, but for my situation, it doesn't matter whether it is open or closed, my objective is to complete the application, my superior only want the application, thanks again guys.

[OBP]

ktwk, the code that I posted in post #8 will actually open the recordset correctly to do what you want.

However have you (and your boss) considered using Access's built in Security to do what you are working on, it has a very good and simple Log on facility with seperate Passwords for each user and has the added advantage of having different levels of access allowed.

[JimmyTheHand]

IF (built-in Security, as OBP recommended, is NOT an option) AND (you really don't care how the job is done) THEN I suggest trying this piece of code:

Dim Str2 As String
Dim Str3 As String
Dim Str4() As String
Dim Rst1 As DAO.Recordset
Dim Fld1 As DAO.Field
Dim a As Integer
Dim b As Integer

Str2 = "SELECT Username, Password FROM LoginTable;"
Set Rst1 = CurrentDb.OpenRecordset(Str2)
Rst1.MoveLast
Rst1.MoveFirst

b = Rst1.RecordCount
ReDim Str4(b) As String
For a = 0 To b - 1
For Each Fld1 In Rst1.Fields
Str3 = Str3 & Fld1.Value & vbTab
Next Fld1
Str4(a) = Str3
'MsgBox Str4(a) '<--- if you want to see the results, enable this line
Rst1.MoveNext
Next a

For it to work you'll have to reference Microsoft DAO 3.X object library. (I tested it with DAO 3.6, but I think any X will do.)
You may also want to decorate the code with the On Error Goto... part, which I just left out. IMO, when in development phase, it's better not to use error handlers, so that you can debug the code. I would disable it in your code, too, so that you would know which line causes error.

One more comment.
Your code concatenates all usernames and passwords so that the elements of the array would look like this:
1. 401 Password125
2. 401 Password125 402 Password126
3. 401 Password125 402 Password126 403 Password224
etc.
Are you sure you want it this way? Maybe a Str3 = "" is missing somewhere?

Jimmy

[ktwk]

hi guys, i have try all your code, great code! but u guys should know this all programmer have their own ways of doing things right, i sort of compare my code to all of you guys, in the end i found out why have i been getting -1 all this while, i added


rst1.CursorType = adOpenKeyset


and no more error, it about the forward moving cursor type, but thanks, i did learn a lot more from you guys. A lot of new ideas, thanks a lot