data source from listbox problem

Hiiiii

I'm using this code to loop data through listbox just to open data from the source

public sub11()

FileCount = List11.ListCount - 1
filename = List11.List(i)
strSQL = "select * from " & filename

------------------------------------------
I have 11 listbox with different files to be loop
My question is how to make this 11 listbox work under 1 sub function
(so I just have to call 1 sub in the program, not 11 sub....with diferent name)
so the one that should change:
filecount=list1.listcount -1
filename = List1.List(i)

filecount=list2.listcount -1
filename = List2.List(i)
until list11

under 1 main sub

how to achieve this?

THX

Create a control array of your listboxes.

malloc has a point.
Heres another way. There is no need to create 11 Sub Routines. Only one.
Create a standard procedure

Public Sub MyProc(vListBox As ListBox)
'..... your codes
FileCount = vListBox.ListCount - 1
filename = vListBox.List(i)
strSQL = "select * from " & filename
'..... same here
End Sub


You can now call this procedure using

Call MyProc(ListBox1)
Call MyProc(ListBox2)
Call MyProc(ListBox3)
Call MyProc(ListBox4)
'....

:)

I can't do that way cos every listbox has to open diferent table name such like this:

Public Sub MSTRANTOTAL()
Dim rsData As ADODB.Recordset
Dim rsresult As ADODB.Recordset
Dim strSQL As String
Dim connectionString As String
Dim filename As String
Dim FileCount
Dim a As Integer

For a = 0 To 9 Step 1
FileCount = List2(a).ListCount - 1

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=dBASE IV;" & _
"Data Source=C:\prog"

For i = 0 To FileCount
List2(a).Selected(i) = True
Form1.Refresh

filename = List2(a).List(i)
strSQL = "select * from " & filename

Set rsData = New ADODB.Recordset
rsData.Open strSQL, connectionString, adOpenKeyset

Set rsresult = New ADODB.Recordset
If filename = List2(0).List(i) Then
strSQL = "select * from mintran"
ElseIf filename = List2(1).List(i) Then
strSQL = "select * from mstran"

rsresult.Open strSQL, connectionString, adOpenKeyset, adLockOptimistic
end if


in here, I have create control array for list2 that contains 0 to 9 array
but each list2(a) has to open diferent filename (mintran,mstran,etc) for rsresult

The above code doesn't work :huh:

every listbox has to open diferent table name
Yes you can.... :)


Public Sub MyProc(vListBox As ListBox,vQueryStatement As String)
'....
End Sub

'To Call
Private Sub Command1_Click ()
Call MyProc(ListBox1,"SELECT * FROM MINTRAN")
Call MyProc(ListBox2,"SELECT * FROM MSTRAN")
End Sub

I can't do that way cos every listbox has to open diferent table name
such like this:

Public Sub TOTAL()
Dim rsData As ADODB.Recordset
Dim rsresult As ADODB.Recordset
Dim strSQL As String
Dim connectionString As String
Dim filename As String
Dim FileCount
Dim a As Integer

For a = 0 To 9 Step 1
FileCount = List2(a).ListCount - 1

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=dBASE IV;" & _
"Data Source=C:\prog"

For i = 0 To FileCount
List2(a).Selected(i) = True
Form1.Refresh

filename = List2(a).List(i)
strSQL = "select * from " & filename

Set rsData = New ADODB.Recordset
rsData.Open strSQL, connectionString, adOpenKeyset

Set rsresult = New ADODB.Recordset
If filename = List2(0).List(i) Then
strSQL = "select * from a"
ElseIf filename = List2(1).List(i) Then
strSQL = "select * from b"

rsresult.Open strSQL, connectionString, adOpenKeyset, adLockOptimistic
end if

While Not rsData.EOF
rsresult.AddNew
For x = 0 To rsData.Fields.Count - 1
rsresult.Fields(x).Value = rsData.Fields(x)
Next x
rsresult.Update
rsData.MoveNext
Wend
Set rsData = Nothing
Set rsHasil = Nothing
Next i
Next a
-------------------------------------------------------------------------------------

I have create control array for list2 from 0 to 9
each list has to open its filename such as
list2(0) has to open table a
list2(1) has to open table b
how to achieve this?

above code generate
"error operation not allowed cause the object is closed" and it refer to rsresult.addnew

Thx

Yes you can.... :)


Public Sub MyProc(vListBox As ListBox,vQueryStatement As String)
'....
End Sub

'To Call
Private Sub Command1_Click ()
Call MyProc(ListBox1,"SELECT * FROM MINTRAN")
Call MyProc(ListBox2,"SELECT * FROM MSTRAN")
End Sub


Mr NEOLLE

I didn't get it, If I call vquerystatement for the rsresult, how about my rsdata because each rsdata also define its specific table

Public Sub MyProc(vListBox As ListBox, vQueryStatement As String)
Dim rsData As ADODB.Recordset
Dim rsresult As ADODB.Recordset
Dim strSQL As String
Dim connectionString As String
Dim filename As String
Dim FileCount

FileCount = vListBox.ListCount - 1

connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=dBASE IV;" & _
"Data Source=C:\prog"

For i = 0 To FileCount
vListBox.Selected(i) = True
Form1.Refresh

filename = vListBox.List(i)
strSQL = "select * from " & filename

Set rsData = New ADODB.Recordset
rsData.Open strSQL, connectionString, adOpenKeyset

Set rsresult = New ADODB.Recordset
strSQL = vQueryStatement
rsresult.Open strSQL, connectionString, adOpenKeyset, adLockOptimistic

While Not rsData.EOF
rsresult.AddNew
For x = 0 To rsData.Fields.Count - 1
rsresult.Fields(x).Value = rsData.Fields(x)
Next x
rsresult.Update
rsData.MoveNext
Wend
Set rsData = Nothing
Set rsresult = Nothing
Next i
End Sub

'to call
Call MyProc(List2(0), "SELECT * FROM a")
Call MyProc(List2(1), "SELECT * FROM b")


I have try this, but the one that only process is the a
and the b (select * from b) not proceed at all(blank)

??

Hello blinx,
Perhaps you could use a little modification with this code. :)


Private Sub Proc_Sample()
Dim dbConnection As ADODB.Connection
Dim intListCount As Integer


Set dbConnection = New ADODB.Connection

With dbConnection
.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=dBASE IV;Data Source=C:\prog"

If vListBox.ListCount = 0 Then GoTo ExitRoutine

.BeginTrans

For intListCount = 0 To vListBox.ListCount - 1
.Execute _
"INSERT INTO Table1 " & _
"(SELECT * FROM " & vListBox.List(intListCount) & ")"
Next

.CommitTrans

ExitRoutine:
.Close
End With
Set dbConnection = Nothing
End Sub


Im not sure with the INSERT statement. But If sure this works in Oracle :D