Subscript out of range - building a list. 1

[pdldavis]

Hi,
I am reading the contents of a list box that contains computer names obtained from a .ldb file.

I split the string and then perform a dlookup on each computer name to get the person's name who is in the database:

Dim StrSql,strsql12 As String
Dim varInstTypes As Variant
Dim varInst0, varInst1, varInst2 As String
Dim User0, User1, User2 As String

StrSql = Me.lstLockFileContents.RowSource
strsql12 = Trim(strsql12)

varInstTypes = Split(strsql12, " ") 'Split the string using space delimiters

If IsEmpty(varInstTypes(0)) = True Then
'If varInstTypes(0) = "" Then
varInst0 = "None"
Else:
varInst0 = varInstTypes(0)
User0 = DLookup("[UserName]", "tblUserName", "[computername] = '" & varInst0 & "'")
strsql12 = User0 & ";"
End If
' ...and so on

Me.lstNamesOnly.RowSource = strsql12

The problem I am having is stopping the building of the string when I reach a VarInst that contains nothing and get the subscript out of range error. I have tried trapping it with an if err.number = 9 then resume next but that is not working.

How is this properly handled?

Thanks, Dan

 

[PHV]

Have a look at the UBound function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cmmrfrds]

What about checking the upper bound on the array.

If UBound(varInstTypes) = 0 then
"no records

 

[pdldavis]

I've looked at that and it works if there is something in the variable. If there is nothing in the variable I get the 'Subscript' error and therein lies the problem.

 

[PHV]

And what about the IsArray function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pdldavis]

No, that is not quite it either. There will always be someone in the db when this is running, therefore varInstTypes will always have at least one person in it so

If IsEmpty(varInstTypes(0)) = True Then
'If varInstTypes(0) = "" Then
varInst0 = "None"
Else:
varInst0 = varInstTypes(0)
User0 = DLookup("[UserName]", "tblUserName", "[computername] = '" & varInst0 & "'")
strsql12 = User0 & ";"
End If

is always filled. In this exxample the next varInstTypes is not filled because there is only one person in the db and I get the subscript error:

If IsEmpty(varInstTypes(1)) = True Then
'If varInstTypes(1) = "" Then
varInst0 = "None"
Else:
varInst1 = varInstTypes(1)
User1 = DLookup("[UserName]", "tblUserName", "[computername] = '" & varInst1 & "'")
strsql12 = User1 & ";"
End If

The IsEmpty was my attempt at trapping the 'Subscript' error - which doesn't work.

I tried the IsArray as well and still get the error so I wonder if I am going about this the right way or not. Is there a better way to approach this?

 

[dRahme]

Hello, if your error handling is correct you might try checking Tools - Options - General - Error Handling and check the box that says break on unhandled errors if unchecked. This is in the VB window, not the DB window.


Hope that helps.

 

[PHV]

What about replacing this:
If IsEmpty(varInstTypes(1)) = True Then
By this ?
If UBound(varInstTypes) >= 1 Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[pdldavis]

varInstTypes is the entire string split. I do not need to test this.

varInstTypes(0) will always contain a computer name.

varInstTypes(1) - the second string may be empty.
varInstTypes(2) - the third string may be empty.

This is where the subscript error is, if one or both of these are empty. I am assigning a value to something that may or may not be there. That is the problem.

Thanks for the comments.

Dan

 

[rubbernilly]

Try this...
Change the varInst0, 1, 2, into an array, and change the User0,1,2, into an array

Dim varInst()
Dim varUser()

Then fill them by walking through your varInstTypes() array.

'this part goes in the code you already have

dim a as integer, b as integer

For a = 0 to ubound(varInstTypes)
   redim preserve varInst(a)
   redim preserve User(a)
   varInst(a) = varInstTypes(a)
   User(a) = DLookup("[UserName]", "tblUserName", "[computername] =  '" & varInst(a) & "'")
   strsql12 = User(a) & ";"
Next a

[green]'The only reason I can see that you would not iterate
'through the varInstTypes() array is if you had to do a
'[i]particular thing[/i] 'x' number of times (in your code,
'it appears to be 3 times). If so, you can do the following:[/green]

b = GreaterOf(2,ubound(varInstTypes)
[green]'2 because you want to do something 3 times

b=GreaterOf(b,a)

for a = a to b
   [green]'put your code here for what you want to do those
'extra times from the Ubound of varInstTypes to [i]your[/i]
'upper limit - here, 2[/green]
   varInst(a) = "None"
next a

Remember, if you do need to do things a set number of times that may be more than your Ubound of the varInstTypes array, you won't be able to reference it for seeding your variables. You will have to do that with your own defaults.

Be careful about the new arrays varInst() and User()... I did not test this code to make sure that the item index matched between them and the varInstTypes... ie, you might need varInst(a+1) = varInstType(a), because they will be 1-based, rather than 0-based.

Finally, if you use the GreaterOf Function, you'll need the following:

Public Function GreaterOf(lngArg1 as Long, _
                               lngArg2 as Long)

if lngArg1 >= lngarg2 then
   GreaterOf = lngArg1
else
   GreaterOf = lngArg2
end if

End Function

Doing this, you should never run into an error with the subscript out of range.

 

[pdldavis]

rubbernilly, thank you for the post and the accompanying explanations. That is exactly what I needed to do - build what needs to be built, thus avoiding the error.

Thanks again, Dan