Can help to find what wrongs with my code ??? keep giving me run time error 9. What is the solutions to prevent this kind of things to happen again ??
I'm trying populate each of the textbox base on the individuals index selected in the listbox. however, when i selected the sixth index it shows error.. i dun knw what wrongs with the code.
Thank in advance and appreciated your helps.
I couldnt upload images its because i dun have 10 countpost.
Table and fields
field5
f,g,h,t,y,I
field6
1,2,3,4,5
field7
1,2,3,4,5,6
field8
1,2,3,4,5,6
field9
1,2,3,4,5,6
field10
1,2,3,4,5,6
field11
1,2,3,4,5,6
I'm using this code to populate the value base on rowsource of the combo box and sameindex as the listbox
Code:
Private Sub List0_AfterUpdate()
With Me
.Text2.Value = Split(.Combo14.Column(1), ",")(.List0.ListIndex)
.Text4.Value = Split(.Combo14.Column(2), ",")(.List0.ListIndex)
.Text6.Value = Split(.Combo14.Column(3), ",")(.List0.ListIndex)
.Text8.Value = Split(.Combo14.Column(4), ",")(.List0.ListIndex)
.Text10.Value = Split(.Combo14.Column(5), ",")(.List0.ListIndex)
.Text12.Value = Split(.Combo14.Column(6), ",")(.List0.ListIndex)
End With
End Sub
However I do not understand what you are trying to achieve, your code implies you have a rowsource of 6 columns each column of which is a string which can be split into an array
but this bit of code won't work
.Text2.Value = Split(.Combo14.Column(1), ",")
to do so, .Text2.Value would have to be an array and you cannot have an array as a control type.
you're right.. what im trying to achieve.. i have a rowsource of 6 columns each column of which is a string which can be split into an array. so whenever i select an item in listbox, the rest of the textboxes will populate the value accordance to listbox index where .list0.listindex comes into the lead combo arr to populate the same index.
however, field 6 to 11 not always contains 6 array.. sometime might have 4 or 5 or even 0 arr in the field.
combo14.rowsource = "select field5, field6, field7, to field11 from table1 where field5=combo14.value "
Can help to find what wrongs with my code ??? keep giving me run time error 9. What is the solutions to prevent this kind of things to happen again ??
I'm trying populate each of the textbox base on the individuals index selected in the listbox. however, when i selected the sixth index it shows error.. i dun knw what wrongs with the code.
Thank in advance and appreciated your helps.
I couldnt upload images its because i dun have 10 countpost.
Table and fields
field5
f,g,h,t,y,I
field6
1,2,3,4,5
field7
1,2,3,4,5,6
field8
1,2,3,4,5,6
field9
1,2,3,4,5,6
field10
1,2,3,4,5,6
field11
1,2,3,4,5,6
I'm using this code to populate the value base on rowsource of the combo box and sameindex as the listbox
Code:
Private Sub List0_AfterUpdate()
With Me
.Text2.Value = Split(.Combo14.Column(1), ",")(.List0.ListIndex)
.Text4.Value = Split(.Combo14.Column(2), ",")(.List0.ListIndex)
.Text6.Value = Split(.Combo14.Column(3), ",")(.List0.ListIndex)
.Text8.Value = Split(.Combo14.Column(4), ",")(.List0.ListIndex)
.Text10.Value = Split(.Combo14.Column(5), ",")(.List0.ListIndex)
.Text12.Value = Split(.Combo14.Column(6), ",")(.List0.ListIndex)
End With
End Sub
I also have same problem with run time error 9 subscript out of range,my code is below
Private Sub Command0_Click()
Dim MyArray As Variant
Dim fso As Variant
Dim objStream As Variant
Dim objFile As Variant
Dim sSQL As String
Dim myString As String
Dim db As Database
Dim rs As DAO.Recordset
Dim sql As String
Dim i As Integer
i = 0
Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1")
sSQL = "SELECT * FROM Table1"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists("C:\Users\herath\Desktop\Online Application\exep.csv") Then
Set objStream = fspenTextFile("C:\Users\herath\Desktop\Online Application\exep.csv", 1, False, 0)
End If
Private Sub Command0_Click()
Dim MyArray As Variant
Dim fso As Object
Dim objStream As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Const FILE_PATH As String = "C:\Users\herath\Desktop\Online Application\exep.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(FILE_PATH) Then
Set objStream = fso.OpenTextFile(FILE_PATH, 1, False, 0)
Else
Exit Sub
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
Do While Not objStream.AtEndOfStream
MyArray = Split(objStream.ReadLine, ",")
If UBound(MyArray) > 0 Then
With rs
.AddNew
!experiance = MyArray(0)
.Update
End With
i = i + 1
End If
Loop
objStream.Close
rs.Close
Set objStream = Nothing
Set rs = Nothing
Set db = Nothing
MsgBox "Uploaded " & i & " records"
End Sub
The main reason is that strLine doesn't contain a "," which cause that MyArray isn't assign anything.
Use the code vbaInet gives you, he has taken care of the above in his code.
Private Sub Command0_Click()
Dim MyArray As Variant
Dim fso As Object
Dim objStream As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Const FILE_PATH As String = "C:\Users\herath\Desktop\Online Application\exep.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(FILE_PATH) Then
Set objStream = fso.OpenTextFile(FILE_PATH, 1, False, 0)
Else
Exit Sub
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
Do While Not objStream.AtEndOfStream
MyArray = Split(objStream.ReadLine, ",")
If UBound(MyArray) > 0 Then
With rs
.AddNew
!experiance = MyArray(0)
.Update
End With
i = i + 1
End If
Loop
objStream.Close
rs.Close
Set objStream = Nothing
Set rs = Nothing
Set db = Nothing
MsgBox "Uploaded " & i & " records"
End Sub
[/QUOT]
Thank you for your response.
When I try with my whole code set still I have same run time error..my whole code set is below,do you have any Idea??
Private Sub Command0_Click()
Dim MyArray As Variant
Dim fso As Object
Dim objStream As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Const FILE_PATH As String = "C:\Users\herath\Desktop\Online Application\exep.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(FILE_PATH) Then
Set objStream = fspenTextFile(FILE_PATH, 1, False, 0)
Else
Exit Sub
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM Users", dbOpenDynaset)
Do While Not objStream.AtEndOfStream
MyArray = Split(objStream.ReadLine, ",")
If UBound(MyArray) > 0 Then
With rs
.AddNew
' !indexNo = MyArray(0)
' !experiance = MyArray(1)
!entryNumber = MyArray(0)
!DateCreated = MyArray(1)
!dateUpdated = MyArray(2)
!Campus = MyArray(4)
Private Sub Command0_Click()
Dim MyArray As Variant
Dim fso As Object
Dim objStream As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Const FILE_PATH As String = "C:\Users\herath\Desktop\Online Application\exep.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(FILE_PATH) Then
Set objStream = fso.OpenTextFile(FILE_PATH, 1, False, 0)
Else
Exit Sub
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM Table1", dbOpenDynaset)
Do While Not objStream.AtEndOfStream
MyArray = Split(objStream.ReadLine, ",")
If UBound(MyArray) > 0 Then
With rs
.AddNew
!experiance = MyArray(0)
.Update
End With
i = i + 1
End If
Loop
objStream.Close
rs.Close
Set objStream = Nothing
Set rs = Nothing
Set db = Nothing
MsgBox "Uploaded " & i & " records"
End Sub
[/QUOT]
Thank you for your response.
When I try with my whole code set still I have same run time error..my whole code set is below,do you have any Idea??
Private Sub Command0_Click()
Dim MyArray As Variant
Dim fso As Object
Dim objStream As Object
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer
Const FILE_PATH As String = "C:\Users\herath\Desktop\Online Application\exep.csv"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FileExists(FILE_PATH) Then
Set objStream = fspenTextFile(FILE_PATH, 1, False, 0)
Else
Exit Sub
End If
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT * FROM Users", dbOpenDynaset)
Do While Not objStream.AtEndOfStream
MyArray = Split(objStream.ReadLine, ",")
If UBound(MyArray) > 0 Then
With rs
.AddNew
' !indexNo = MyArray(0)
' !experiance = MyArray(1)
!entryNumber = MyArray(0)
!DateCreated = MyArray(1)
!dateUpdated = MyArray(2)
!Campus = MyArray(4)
It looks like the index of MyArray() is less than 50. Run the code again and when you get the error click on "Debug" and in the watch window add a Watch for "Ubound(MyArray)". If it shows <50, you have a problem. Your code will have to handle the unexpectedly low number of item in the stream.
herath381, like Solo712 mentioned you will need code to handle the unexpectedly low number of items in the array and to do this you will need to loop through the array instead. Something like this:
Code:
If UBound(myArray) > 0 Then
With rs
.AddNew
For x = LBound(myArray) To UBound(myArray)
.Fields([COLOR="blue"]ResolveFieldName([/COLOR]x[COLOR="Blue"])[/COLOR]) = myArray(x)
End Select
Next
.Update
End With
i = i + 1
End If
Create the ResolveFieldName() function that will return the name of the field as a string or as the number index.
penTextFile("C:\Users\herath\Desktop\Online Application\exep.csv", 1, False, 0)