Question Subscript out of range in vba (1 Viewer)

[n3wguys]

Dear All,

What the Variant value on Ms Access latest version is same with Ms Access previously(97,2003).?

Im using office 2010

I have face error again in line

-----------------------------------------
bd = ""
[B]bd = Split(bx(1), " ")[/B]
For i = 0 To (UBound(bd) - 1)
Me("p" & i).Caption = bd(i)
Me("p" & i).Visible = True

Next i
----------------------------------------

messagebox said is "subscript out of range(error 9) and cursor stop in

[B]bd = Split(bx(1), " ")[/B]

.


for code program previously ,

========================================
Private Sub show()
Dim at, bb, url As String
Dim bc, bd, be, j, bx As Variant
Dim i, t As Integer
Dim tbb, tb2 As Variant

For i = 0 To 10
Me("p" & i).Caption = ""
Me("p" & i).Visible = False
Next i
status.Caption = ""

If InternetGetConnectedState(0&, 0&) Then 

url = "my_site.com/showdata_rev.php"
at = fcSend("&page=" & fpage, url) 
Text5 = at 
If Text5 <> "" Then 

f1.SourceObject = ""

f1.Visible = False
Text7.Visible = False

Text5 = Replace(Text5, vbTab, "") 
Text5 = Replace(Text5, vbCr, "") 
Text5 = Replace(Text5, vbLf, "") 
bb = ""
bx = Split(Text5, "<>")
bd = Split(bx(0), "~") 
tbb = "try_"

hp_tb (tbb)

DoCmd.RunSQL "CREATE TABLE " & tbb _
& " (idemployee Number,rectime datetime," _
& " FirstName Text(255), MiddleName Text(255)," _
& " LastName Text(255), Placebirth Text(255)," _
& " datebirth datetime, passport text(10)," _
& " 1starrivedate datetime," _
& " leavedate datetime,address text(255)," _
& " email1 text(255),telp number,status text(50),nationality text(255));" ', DateBirth DateTime);"

Set db = CurrentDb
For i = 0 To (UBound(bd) - 1)
bb = ""

bc = Split(bd(i), "|") 
For t = 0 To UBound(bc)
bb = bb & cek_isi(HtmlToText(bc(t)), IsNumeric(HtmlToText(bc(t)))) & ", " 
Next t
bb = Trim(bb)
bb = Left(bb, (Len(bb) - 1))
db.Execute "insert into " & tbb & " values" _
& " (" & bb & ")"
Next i

db.Close
Set db = Nothing

bd = ""
[B]bd = Split(bx(1), " ")[/B]
For i = 0 To (UBound(bd) - 1)
Me("p" & i).Caption = bd(i)
Me("p" & i).Visible = True

Next i

status.Caption = Trim(bx(2))

If UBound(bd) < 1 Then 
bb = "Nothing found data"
f1.Visible = False
Text7.Visible = True
Else

f1.SourceObject = "f1"
f1.Form.RecordSource = tbb
f1.Visible = True
End If
Else
bb = "Wrong Destination" 
End If

For i = 0 To 10
If Me("p" & i).Caption = "" Then
Me("p" & i).Visible = False
End If

Next i

Else
MsgBox "Connection Internet is problem." & vbCrLf _
& "Please try again" & vbCrLf _
& "with other session", , ":: Disconnect"
End If


End Sub


=====================================

Thanks for your help

 

[pr2-eugin]

Please use Code Tags when posting VBA Code

 

[n3wguys]

Sorry.. I will amend my post...

 

[pr2-eugin]

Sorry to be a real pain, but read this.
=========================================================
Check out this thread.. "Please Use CODE tags when posting VBA Code".. (The Post#2 of Pleas use code tags)

Also make sure you properly indent the code.. The following..

Public Function SelectAll(lst As ListBox) As Boolean
On Error GoTo Err_Handler
[COLOR=Green]'Purpose:   Select all items in the multi-select list box.
'Return:    True if successful
'Author:    Allen Browne. http://allenbrowne.com  June, 2006.[/COLOR]
Dim lngRow As Long

If lst.MultiSelect Then
For lngRow = 0 To lst.ListCount - 1
lst.Selected(lngRow) = True
Next
SelectAll = True
End If

Exit_Handler:
Exit Function

Err_Handler:
Call MsgBox(Err.Number & Chr(10) & Err.Description & Chr(10) & "SelectAll()")
Resume Exit_Handler
End Function

Is as bad as..

Public Function SelectAll(lst As ListBox) As Boolean
On Error GoTo Err_Handler
'Purpose: Select all items in the multi-select list box.
'Return: True if successful
'Author: Allen Browne. http://allenbrowne.com June, 2006.
Dim lngRow As Long

If lst.MultiSelect Then
For lngRow = 0 To lst.ListCount - 1
lst.Selected(lngRow) = True
Next
SelectAll = True
End If

Exit_Handler:
Exit Function

Err_Handler:
Call MsgBox(Err.Number & Chr(10) & Err.Description & Chr(10) & "SelectAll()")
Resume Exit_Handler
End Function

Properly indented code,

Public Function SelectAll(lst As ListBox) As Boolean
On Error GoTo Err_Handler
[COLOR=Green]    'Purpose:   Select all items in the multi-select list box.
    'Return:    True if successful
    'Author:    Allen Browne. http://allenbrowne.com  June, 2006.[/COLOR]
    Dim lngRow As Long

    If lst.MultiSelect Then
        For lngRow = 0 To lst.ListCount - 1
            lst.Selected(lngRow) = True
        Next
        SelectAll = True
    End If

Exit_Handler:
    Exit Function

Err_Handler:
    Call MsgBox(Err.Number & Chr(10) & Err.Description & Chr(10) & "SelectAll()")
    Resume Exit_Handler
End Function

 

[plog]

Subscript out of range generally means you referenced something that doesn't exist: http://msdn.microsoft.com/en-us/library/aa264519(v=vs.60).aspx

My guess is that the array bx() doesn't contain 2 elements. Remember, you start counting array elements at 0, that means bx(1) is trying to look at the 2nd element of the array. If that array has less than 2 elements--subscript out of range. A way to work around this is to test bx to see if it has at least 2 elements before running that line of code.

 

[n3wguys]

So with this code above, how to fix it my friend?

 

[plog]

I just gave you the broad strokes of how to fix it, and looking through your code, you already have lines that do exactly what you need, just on different arrays. So, I'm guessing you didn't write the code, nor understand it?

In that instance, I advise you find someone who knows it to help you work through it or start reading tutorials about VBA.

 

[n3wguys]

I ever change this code using redim or dim example

dim x () as integer

I always face error..

so my question is, what there different on vba rule if I use office 2010
why i say like that? because I use this code in my old machine and office 2003 have installed, and there nothing found error..
but I rewrite again on my new machine that installed office ms access 2010 and office 2013, there I have face some mistake.

 

[plog]

I don't know if there is a difference, but that's pretty simple stuff so I don't think they changed that. Also, you're error has to do with referencing an item in an array that doesn't exist.

You need to check that array to make sure it has 2 elements before you run the line of coding throwing the out of range error.

 

[n3wguys]

You can repair this code, please?
Honestly, for VBA Coding, I'm new.
thanks

 

[jdraw]

n3wguys,

In addition to other comments, you should be aware that in Access vba you must explicitly DIM variables.
THis does NOT work as you think

Dim at, bb, url As String
Dim bc, bd, be, j, bx As Variant
Dim i, t As Integer
Dim tbb, tb2 As Variant

You must use

Dim at  As String, bb As String, url As String Or
Dim at  As String, Dim bb  As String....

When you do NOT explicitly Dim a variable, it will default to type Variant.

 

[n3wguys]

yes.. I have re code again like your suggest..

Like this after I modified and show error again in other line..

'begin
Option Compare Database
Option Explicit
Dim db As Database

--------cut code

Private Sub show()

Dim at As Variant
Dim bb As Variant
Dim url As String
Dim bc As Variant
Dim bd As Variant
Dim be As Variant
Dim j As Variant
Dim bx As Variant
Dim i As Integer
Dim t As Integer
Dim tbb As Variant
Dim tb2 As Variant


------- cut code
------- cut code
end sub

so one by one show error like this in line code

                                                         bb = ""
                                                         bx = Split(Text5, "<>")
'here cursor stop and show error --->       bd = Split(bx(0), , "~") 
'and message error (13), type mismatch

so What next?

 

[n3wguys]

So please amend what you think to correct this code..