from listbox to textbox on same form from query

[zgtrman]

I just know that the answer to this question is going to be simple but it elludes me at the moment and I cannot find an answer through a search on this site.
I am developing an application for a friend dealing with ticket sales. I have included the block of code below.
I am using VB6.0 SP5 and an Access 2000 database and accessing my recordsets through code.
What has to happen is the user clicks a phone number from a list box and the .text from the listbox is assigned to a string. Then I use an SQL statement to extract information from the database using the string to populate text boxes with data from certain fields of the table. (all the data is coming from the same table)
I am stumped as to how to make the data appear in the textboxes..I have tried
txtfirstname.text = rscustomer(strsql.. no results
with txtfirstname
.datafield = !firstname
set .datasource = rscustomer(strSQL)... error...
along with a host of other attempts.
I think that I am so wrapped up in trying to find a solution that I cannot see the answer.

Here is the code:
Private Sub lstCustPhNumber_Click()
On Error GoTo Errclose
txtFirstName.Text = ""
txtLastName.Text = ""
txtSalesAddress.Text = ""
txtSalesPhone.Text = ""
Dim strSQL As String
Dim strPhone As String
strPhone = lstCustPhNumber.Text
strSQL = "SELECT firstname, lastname, address,phone FROM customer WHERE phone = '" & strPhone & "'"
rsCustomer.Open (strSQL)
<insert the textbox code here>
Exit Sub
Errclose:
rsCustomer.Close
rsCustomer.Open
Resume Next
End Sub

just for extra measure here is the block of code that fills the listbox

Private Sub LoadCustomer()
On Error GoTo Errclose

lstCustPhNumber.Clear
With rsCustomer
.MoveFirst
Do While rsCustomer.EOF = False
lstCustPhNumber.AddItem !phone
.MoveNext
Loop
End With
Exit Sub
Errclose:
rsCustomer.Close
rsCustomer.Open
Resume Next
End Sub

any help would be greatly appreciated.
Thank You
zgtrman To effectively improve in the efficiency of your performance, one must be proficient in the implementation of a positive mental attitude.

 

[vbkris]

example code
if not rsCustomer.eof and not rsCustomer.bof then
txtFirstName.Text = rsCustomer(&quot;DatabaseColumnName&quot
else
msgbox &quot;No records.&quot;
end if

 

[zgtrman]

ok, I have worked with the code example provided by vbkris without the results I am looking for. The code does put a field in the text box however it is only the first record of the recordset and there is no change when a different selection is made. Since another part of my program will hinge entirely on this problem being solved I need it to work. Is there anyone else out there that has done this sort of thing before?

Any help will be greatly appreciated

zgtrman To effectively improve in the efficiency of your performance, one must be proficient in the implementation of a positive mental attitude.

 

[TIGREBLANCO]

The following example may set you in the right path:

Private Sub Form_Load()
Dim db As Connection
Set db = New Connection
db.CursorLocation = adUseClient
' In the next line oledb ver may be 3.51 or 4.00
' depending on your MSAccess version.
db.Open &quot;PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=C:\Yourpath\YourDatabase.mdb;&quot;

Set adoPrimaryRS = New Recordset
adoPrimaryRS.Open &quot;select Field1,Field2,[Field 3] from YourTable Order by Field1&quot;, db, adOpenStatic, adLockOptimistic

Dim oText As TextBox
'Bind the text boxes to the data provider
For Each oText In Me.txtFields
Set oText.DataSource = adoPrimaryRS
Next

mbDataChanged = False
End Sub

Regards.

 

[jpittawa]

This works:

Option Explicit

Public cnn As ADODB.Connection
Public rs As ADODB.Recordset

Private Sub ClearTextBoxes()
    Dim ctl As VB.Control
    
    For Each ctl In Me.Controls
        If TypeOf ctl Is VB.TextBox Then ctl.Text = ""
    Next ctl

End Sub

Private Sub Form_Load()
    
    Set cnn = New ADODB.Connection
    With cnn
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Northwind.mdb"
        .CursorLocation = adUseClient
        .Open
    End With
    Set rs = New ADODB.Recordset
    With rs
        Set .ActiveConnection = cnn
        .Source = "SELECT CompanyName, ContactName, ContactTitle, Phone " & _
                  "FROM Customers " & _
                  "WHERE Country='USA';"
        .Open
        While Not .EOF
            lstPhone.AddItem rs("Phone")
            lstPhone.ItemData(lstPhone.NewIndex) = rs.AbsolutePosition
            .MoveNext
        Wend
        .MoveFirst
    End With
End Sub

Private Sub lstPhone_Click()
    Debug.Print lstPhone.Text
    ClearTextBoxes
    With lstPhone
        rs.AbsolutePosition = .ItemData(.ListIndex)
    End With
    Me.txtCompany = rs("CompanyName")
    Me.txtContact = rs("ContactName")
    Me.txtTitle = rs("ContactTitle")
    Me.txtPhone = rs("Phone")
End Sub

This saves doing a ADO Filter or Find. The caviet is that cache is reloaded when you move the record pointer via the AbsolutePosition, but this beats the heck out of bound controls.

Also, note the use of the ListBox's ItemData and NewIndex properties. This is handy when you want to build a pick-list from a table with a numeric key and a text field. The user sees the text. The Click event dials out the ItemData that corresponds to that text, and you have index from which to build the WHERE clause of a query, or whatever.

The reason for loading the ItemData with the NewIndex property is that it will work even if the ListBox has it's Sort property set.

And, anything the same routines work for ComboBox's as well.

Good hunting!!