VB6: Need help filling textboxes

[yoyoaz77]

I have a form written in vb6 that the user is able to show listed items in a listbox from criteria from 4 different comboboxes. The data is being connected to an Access db. Where I am stuck at is after the search lists the data in the listbox the user is to select an item then click a command button that opens a new form, the new form has numerous textboxes, each corresponding to a field in the db, I need the textboxes to be filled with the record from the db that matches with the item that the user selected from the search screen. I am just confused if I should use an IF statement or a select query statement to fill all the listboxes. Any help would suffice. I already have the connection to the db through ADO code and I know how to fill the textboxes with the data, I just need to make sure that the textboxes are being filled with just the matching record from the search screen. Sorry this may be confusing. Any help would be great.

[Teme64]

Yes, a bit confusing. Without seeing the "big picture", I would allow user make the selection criteria in the first form. When user presses the button I would build a new SQL select statement with selection criteria, open the data form and show whatever was returned from the SQL statement.

There may be zero, one or multiple matching records, but like I said, I don't see the "big picture" ;)

[debasisdas]

since you will need to display a lot of records why not use a grid instead of number of textboes.

[yoyoaz77]

I have not used a grid before, so I would not know how to go about it. Is there a suggested article to review or just google it?

[yoyoaz77]

Here is the code I have for the form load of the new form that the data is to be populated to, I am only showing 4 items since there are many more textboxes to show:

Private Sub Form_Load()
Dim search As String
Dim statement As String
   
    'Connect to db and open recordset
        Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source= C:\...\Data.mdb"
    cn.Open
    Set rs = New ADODB.Recordset
    rs.Open "tbl_001_RawData", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    
    search = "[Manager] = '" & frmSearch.Combobox1.Text & "' AND [Name] = '" & frmSearch.Combobox2.Text & "' AND [Agent#] = '" & frmSearch.Combobox3.Text & "' AND [Call_Date] = '" & frmSearch.Combobox4.Text & "'"

statement = "SELECT AuditNum FROM tbl_001_RawData Where " & search
Set rs = cn.Execute(statement, , adCmdText)

'frmAuditViewer.Controls.Clear
Do While Not rs.EOF
        frmAuditViewer.txtInfo(0).AddItem rs!Name
        frmAuditViewer.txtInfo(1).AddItem rs!Manager
        frmAuditViewer.txtInfo(2).AddItem rs!Agent#
        frmAuditViewer.txtInfo(3).AddItem rs!Call_Date

        rs.MoveNext
        Loop
    
    rs.Close
    cn.Close
    
  
End Sub

[Teme64]

Your code will actually display the last record found. Instead of the loop, check that the recordset is not empty and display only one (first) record. Add "Next"-button to the form and when the user presses Next, use recordset's MoveNext method and fill the textboxes with the second record. You may also add "Prev"-button and in the similar way allow user to "navigate" between all the records found.

[yoyoaz77]

What I need to show is the record based on the search criteria

[Teme64]

Your code is then ok. Assuming you do get only one record :)

[yoyoaz77]

It works, but I am only getting the first record even if I select, say the third item in the listbox, it is not matching the records properly

[jireh]

can you please put a screenshot of your searchform here. As what you have said you have a multiple criteria for your search, ergo it has a 99% of possibility that it will retrieve more than one record as what Teme64 said. And as what debasisdas said it more good/nice to use a grid for displaying the result of your search.

[yoyoaz77]

Here is a screenshot of the searchscreen, as for the grid, per client request of the results form, they want it a certain way that a grid would not work for them.

[yoyoaz77]

I figured it out, I basically took the search string I used to fill the listbox on the search screen and modified it to include the text from the listbox and had it fill in all the textboxes on form load on the view form