I have a search form which lists the results from a database in a ListView. Is there a way in that when I double click on any of the records in the listview, i can be able to launch another form which has details of the member i had searched on based on the ID? I dont know whether it is clear.

In Event YourListViewName_DblClick you get selected Item. And then you may use some SQL string to search detail information about it.

e.g.

Private Sub ListView1_DblClick()
Dim strSQL as String
dim ID as String

  ID = 'I don't know, where you store current ID (it's first column, or where?)
  strSQL = SELECT * FROM your_table WHERE ID = " & ID

  'open recordset (via ADO, or DAO)
  'open form with full details information and fill it's fields with specified record
End Sub

Yes my ID (UserId field) is in the first column in both the table and the listview control. I am still not sure of how to open the form that has the details. Should I use the form.show method or how exactly should I implement this? And how best should I declare the ID variable?

Hi,
ID can be declared as String, because it concat with SQL string which is string too.
Private Sub ListView1_DblClick()
Dim ID as String

  ID = Me.ListView.SelectedItem.Text

  frmDetail.ID = ID
  frmDetail.Show vbModal

End Sub

'open form Detail:


'Type in detail form:
'add new Property e.g. ID in declaration section of form
Private mstrID as String

Public Property Let ID (ByVal NewID As String)
  mstrID = NewID
End Property

Public Property Get ID As String
  ID = mstrID
End Property

'form Open event
Private Sub Form_Open()
Dim strSQL as String
Dim rs as New ADODB.Recordset

  strSQL = "SELECT * FROM your_table WHERE ID = " & mstrID
  rs.Open strSQL, yourConnectionObject, yourLocks

  me.Field1.text = rs.Field(0).Value
  ...

  rs.Close
  Set rs = Nothing

End Sub

Hi peko,

I have tried your solution but i get an error with the recordst. This is how i implemented the code.

For the form with the listview items i added:-

And for the details table i added:-

The error given is "No value given for one or more parameters"
When I debug the code it pints at the rs.Open line and shows the sql to be:

strSQL = "SELECT * FROM users WHERE UserId = john"

Yet UserId should be picking the UserID field but in this case it is picking the LastName field. Where am I going wrong?

I will really appreciate your help.

Thanks

Hi osemollie,
if you get LastName when you want to get ID from ListView, then ID isn't first column in ListView, because ListView1.SelectedItem.Text return value of first colunm.
How do you fill the ListView?

Peko

P.S. I find something wrong:
UserId = Me.ListView1.SelectedItem.Text - there you use UserID variable
frmUser.UserId = UserNo - and there UserNo, it's correct?

I have a procedure called PopulateList which I call when I want to populate the ListView. The code is

Private Sub PopulateList(pList As ListView, _
                         pRst As ADODB.Recordset)
    On Error Resume Next
    Dim i As Long
    Dim iColCount As Long
    Dim sColName As String
    Dim sColValue As String
    Dim oCH As ColumnHeader
    Dim oLI As ListItem
    Dim oSI As ListSubItem
    Dim oFld As ADODB.Field

    With pList
        .View = lvwReport
        .GridLines = True
        .FullRowSelect = True
        .ListItems.Clear
        .Sorted = False
        pRst.MoveFirst
        ' set up column headers

        For Each oFld In pRst.Fields
            sColName = cn(oFld.Name)
            Set oCH = .ColumnHeaders.Add()
            oCH.Text = sColName
            iColCount = iColCount + 1
        Next oFld


        Do Until pRst.EOF
            i = 0
            ' setup fiprst column as a listitem
            sColValue = cn(pRst.Fields(i).Value)
            Set oLI = .ListItems.Add()
            oLI.Text = sColValue
            ' add the remaining columns
            'as subitems

            For i = 1 To iColCount
                Set oSI = oLI.ListSubItems.Add()
                oSI.Text = cn(pRst(i))
            Next ' next column
            pRst.MoveNext
        Loop ' Next record
        ' refresh it all
        .Refresh
        ' make sure 1st row can be seen
        .ListItems(1).EnsureVisible
    End With
End Sub

The recordset for populating the ListView is:-

rst.Open("SELECT LastName, FirstName, UserId, Institution, MobileNo FROM users"

Or is there an easier way of populating the ListView with recordsets from the database?

The users table structure is:

UserNo, NUMBER, PrimaryKey
UserId, TEXT
LastName, TEXT
FirstName, TEXT
Address, TEXT
Telephone, TEXT
MobileNo, TEXT
E-mail, TEXT

My sample program is for registering members of a club. Members can be adults or children. Adults have ID cards whereas children don't have ID cards. That is why I opted to use UserNo as my primary key instead of UserId, since children members won't have ID cards.

Thanks

Hi Peko,

I managed to sort out the problem. It was to do with my recordset and listview display. Thanks for your patience and help. Be blessed.