[RESOLVED] ListBox to TextBoxes

[AIS4U]

I'm using VB6 and an Access 2003 database.

The database has a table named Clients that contains first name, last name, phone number, and fax number.

On my form I have a ListBox which I have filled with the last name (ClLName) field from the database and several empty TextBoxes.

When I click on the last name in the ListBox I would like to populate the TextBoxes with first name, last name, phone number and fax number that correspond to the last name selected in the ListBox.

My ListBox is populated with code in the form load event and that works fine.
The code below doesn't work to populate the TextBoxes. All I am getting is the information for the last record in the database.

I am sure this is pretty simple, but I can't seem to make it work.

I would appreciate any help you can give.

Thanks

Private Sub ListBox1_Click()
    Dim RS As ADODB.Recordset
    Dim CN As ADODB.Connection
    
CString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\LOMAP\LOMAPMAIN.mdb;"
 
Set RS = New ADODB.Recordset
Set CN = New ADODB.Connection
CN.ConnectionString = CString
CN.Open
RS.ActiveConnection = CN
RS.Open "SELECT * FROM Clients"
 
Dim intN As Integer
For intN = ListIndex - 1 To 20
 
Text1.Text = RS.Fields.Item("ClFName").Value
Text2.Text = RS.Fields.Item("ClLName").Value
Text3.Text = RS.Fields.Item("ClPhone").Value
Text4.Text = RS.Fields.Item("ClFax").Value
 
Next intN
 
End Sub

[Pasvorto]

Your loop is the issue

For intN = ListIndex - 1 To 20

Text1.Text = RS.Fields.Item("ClFName").Value
Text2.Text = RS.Fields.Item("ClLName").Value
Text3.Text = RS.Fields.Item("ClPhone").Value
Text4.Text = RS.Fields.Item("ClFax").Value

Next intN

It goes through the list and keeps overwriting what was there before.

[AIS4U]

Pasvorto:

Yes, I know the loop is the problem.

What I don't know is how to correct it.

Thanks

[Pasvorto]

Do something like this:

RS.Open "SELECT * FROM Clients where [cifname] = " & "'" & listindex.text & "'"

Text1.Text = RS.Fields.Item("ClFName").Value

[pianoman]

When I click on the last name in the ListBox I would like to populate the TextBoxes with first name, last name, phone number and fax number that correspond to the last name selected in the ListBox.

Private Sub ListBox1_Click()
    Dim RS As ADODB.Recordset
    Dim CN As ADODB.Connection
    
CString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\LOMAP\LOMAPMAIN.mdb;"
 
Set RS = New ADODB.Recordset
Set CN = New ADODB.Connection
CN.ConnectionString = CString
CN.Open
RS.ActiveConnection = CN
RS.Open "SELECT * FROM Clients"

This opens ALL records in the Table. If you only want the ones that correspond to the name selected in the ListBox, you need to do

RS.Open "SELECT * FROM Clients WHERE [clLName]  = '" & ListBox1.Text & "'"

Unless you have multiple records with the same last name, you will not need to loop through the records. Just populate the TextBoxes with the records

Text1.Text = RS![ClFName]
Text2.Text = RS![ClLName]
Text3.Text = RS![ClPhone]
Text4.Text = RS![ClFax]

[AIS4U]

Pasvorto:
Pianoman:

Thanks guys, that works great, but it caused another problem.

In some of the database records there is not a fax number. So when I click on a last name in the ListBox that does not have a fax number entered in the record I get an error message:
Run-time error 94.
Invalid use of null.
The fax number field is set to text, but no information is entered into that field if the
client does not have a fax number.

Do you have any suggestions about how I can correct this problem?

Thanks

[dglienna]

Just add NULL to each record that may be null.

Text4.Text = RS![ClFax] & ""

[AIS4U]

dglienna:

Thanks a lot! That made it work.

Acutally I had a problem at first, but I used your code suggestion in an
If/Then statement and it works just fine.

Thanks again.

I'll mark this one Resolved.