Displaying multiple similar names in a listbox

[Shilpa Jain]

Hi all,

I am searching and fetching the data in a form based on the name entered in the text field. When the submit button is clicked, another form is displayed containing all the fetched data.Right now the functionality works only for the name entered in the textbox. I want that when the name is entered in textbox, all the similar names related to the text entered should be displayed in the list box (changing textbox to listbox at that time) and then when the user selects the required name, the data should be displayed.

How can it be done? Please help.

Here is the code that I am using right now:

Private Sub SearchCmd_Click() 

Dim conn As New ADODB.Connection 
Dim cmd As New ADODB.Command 
Dim sConnString As String 
Dim sName As String 
Dim iCount As Byte 
Dim sSQL As String 
iCount = 0 
Dim rs As Recordset 

sName = NameTxt.Text 

Set rs = New ADODB.Recordset 

If IsNull(sName) Or sName = "" Then 
    MsgBox "Please enter name for search!!", vbOKOnly + vbExclamation, "Empty Field" 
    Me.NameTxt.SetFocus 
Else 

'Connecting to the Database 
    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Tool\P_and_E\P_and_E.mdb" 
     
    conn.Open sConnString 
    cmd.ActiveConnection = conn 
     
    If Me.PrjOpt = True Then 
     
     
    'Query to fetch details from Personal_Info table 
    sSQL = " SELECT P.Emp_No,P.Name, P.NID,P.Location, P.Passport_No, P.PIN, P.[D-O-B], P.Aet_Join_Date, " _ 
    & "P.Infy_Mail_Id, P.Ph_Res, P.Ph_Off, P.Ph_Cell, P.Address, P.Status " _ 
    & "From Personal_Info P " _ 
    & "WHERE ((P.Name)Like '" & "%" & sName & "%" & "' ) " 
     
    rs.Open sSQL, conn, adOpenStatic, adLockOptimistic 
    rs.Requery 
    'MsgBox (rs.RecordCount) 
     
    
     
    If Not rs.BOF Then 
     
            'Load form to display details 
            frmSearchResult.Show 
     
            'Display values from database in form 
            frmSearchResult.EmpTxt.Text = rs.Fields("Emp_No").Value 
            frmSearchResult.NameTxt.Text = rs.Fields("Name").Value 
            frmSearchResult.LocTxt.Text = rs.Fields("Location").Value 
            frmSearchResult.NidTxt.Text = rs.Fields("NID").Value & "" 
            frmSearchResult.PassportTxt.Text = rs.Fields("Passport_No").Value 
            frmSearchResult.PinTxt.Text = rs.Fields("PIN").Value & "" 
            frmSearchResult.DOBTxt.Text = rs.Fields("D-O-B").Value & "" 
            frmSearchResult.AetJDTxt.Text = rs.Fields("Aet_Join_Date").Value 
            frmSearchResult.IDTxt.Text = rs.Fields("Infy_Mail_Id").Value & "" 
            frmSearchResult.ResTxt.Text = rs.Fields("Ph_Res").Value & "" 
            frmSearchResult.OffTxt.Text = rs.Fields("Ph_Off").Value & "" 
            frmSearchResult.MobTxt.Text = rs.Fields("Ph_Cell").Value & "" 
            frmSearchResult.AddTxt.Text = rs.Fields("Address").Value & "" 
            frmSearchResult.StatusTxt.Text = rs.Fields("Status").Value & "" 
     
    Else 
     
        MsgBox "Record not found for Personal Info!! " 

   End If 
    rs.Close 

   Set rs = Nothing 
       
    'Close connection to database 
    conn.Close 
    Set conn = Nothing 

End If 

End Sub

[QVeen72]

Hi,

For Access database(.mdb) , Like operator works with *

Try this :

" Where P.Name Like '*" & sName & "*' "

Regards
Veena

[Shilpa Jain]

Thanks Veena for replying. But, the query that I am using for fetching data is working perfectly fine. I was using the query as you have mentioned(with "*") earlier but the records were not fetched. It was fetching results when I ran it in access DB but when I used it in VB code, it did not returned any record. So, I had to use "%" operator while writing VB code and it started working fine.

Now, my problem is that I am able to display the list box containing all the names related to entered string but selecting the required name and then displaying data for that name is giving nightmares to me.

I have added few lines of code for displaying listbox and fetching the names in it.

Here is the code:

If Me.PrjOpt = True Then
    
       
    'Query to fetch details from Personal_Info table
    sSQL = " SELECT P.Emp_No,P.Name, P.NID,P.Location, P.Passport_No, P.PIN, P.[D-O-B], P.Aet_Join_Date, " _
    & "P.Infy_Mail_Id, P.Ph_Res, P.Ph_Off, P.Ph_Cell, P.Address, P.Status " _
    & "From Personal_Info P " _
    & "WHERE ((P.Name)Like '" & "%" & sName & "%" & "') "
    
    rs.Open sSQL, conn, adOpenStatic, adLockReadOnly
    rs.Requery

If Not rs.BOF Then
            NameTxt.Visible = False
            lstName.Visible = True

            rs.MoveFirst

            Do While Not rs.EOF
                lstName.AddItem rs!Name
                rs.MoveNext
            Loop

             Me.lstName.Selected(1) = True

frmSearchResult.Show
//rest of the code is same

 End If
   
    rs.Close

How do I select the required name and then display data on another form?

Regards,
Shilpa

[Shilpa Jain]

The problem is resolved. :)