-
I have a listbox displaying the names. I'm trying to select more than one name and display the information of the selected names in the grid. But I'm able to display only the last selected name's information. I'm using the following code.
Dim cnn As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim rst As New ADODB.Recordset
Dim strProvider As String
Dim strDataSource As String
Dim strSQL As String
Dim strSQL1 As String
Dim txtName As String
Dim localID As String
Dim I As Integer
strProvider = "Microsoft.Jet.OLEDB.3.51"
strDataSource = App.Path & "/AllNames.mdb"
For I = 0 To List1.ListCount - 1
If List1.Selected(I) Then
txtName = List1.List(I)
End If
Next I
cnn.Open "provider=" & strProvider & ";Data Source=" & _
strDataSource
strSQL = "Select ID from Information where Name = '" & txtName & "';"
rst.Open strSQL, cnn, adOpenStatic
localID = rst![ID]
strSQL1 = "Select Name,Date,Address from TotalInformation where TotalInformation.ID = '" & localID & "
rst1.Open strSQL1, cnn, adOpenStatic
rst1.MoveFirst
While rst1.EOF = False
rst1.MoveNext
Wend
With frmInformation.DataGrid1
Set .DataSource = rst1
End With
Can anyone help me please
-
The problem that you have is when you populate your txtname variable it over writes what you had in it. So when you reach your SQL where clause the condition is where name = txtname. You would need to create a where clause like this where name = txtname or name = txtname1 and so on. This is because your Grid can have only one data provider. You can get around this by using the additem method. This way you can populate the Grid on the fly.
