Listbox to textbox

P

Peter L Kessler

Hi Group

I've managed to link a range in an Excel spreadsheet so that the data is
shown in a listbox on a Word 97 userform. The data consists of a series of
company names, contact names, addresses, etc. The userform reads all the
spreadsheet's data but only displays the company name and contact name in
one column in the listbox.

What I'm now struggling with is that when the user clicks on a company name
in the listbox list, the data for that entry should then appear in a series
of text boxes on the same userform.

This is what I have so far (shown below), but I can only get the first entry
to appear. It doesn't matter which section of the listbox I click on, only
that first entry appears. Can anybody help, please?

Best wishes
Peter




Kessler Associates
E: (e-mail address removed)
W: http://homepages.tesco.net/~plk33/plk33/index.htm



******************************
Private Sub lstCompany_Click()

Dim strKey As String

If lstCompany.ListIndex = -1 Then GoTo Ending

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' strEDBPath = path called from Registry entry
' strEDBTable = Excel range name called from Registry entry

' Open the database
Set db = OpenDatabase(strEDBPath + "", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM " + strEDBTable)

With lstCompany
If .ListIndex < 0 Then GoTo Ending
strKey = .List(.ListIndex, 1)
End With

With rs 'MyRecordSet
'.Seek Array(strKey), adSeekFirstEQ
If .EOF Then
MsgBox "Unable to seek to: " & ", Company: " & strKey,
vbOKOnly, Me.Caption
GoTo Ending
End If

' Populate Form controls
txtToAddress2.Text = AssignBlankIfNull(![name])
txtToAddress3.Text = AssignBlankIfNull(![Company])
txtToAddress4.Text = AssignBlankIfNull(![ADDRESS1])
txtToAddress5.Text = AssignBlankIfNull(![ADDRESS2])
txtToAddress6.Text = AssignBlankIfNull(![ADDRESS3])
txtToAddress7.Text = AssignBlankIfNull(![City])
txtToAddress8.Text = AssignBlankIfNull(![PostCode])
txtToAddress10.Text = AssignBlankIfNull(![Fax])
txtToAddress11.Text = AssignBlankIfNull(![TEL])
txtToAddress12.Text = AssignBlankIfNull(![County])
txtToAddress13.Text = AssignBlankIfNull(![Country])
End With

rs.Close
db.Close
End If

Ending:
End Sub

Public Function AssignBlankIfNull(ByVal varValue As Variant) As String

AssignBlankIfNull = IIf(IsNull(varValue), vbNullString, varValue)

End Function
 
D

Doug Robbins - Word MVP

Hi Peter,

Why don't you load all of the data into to ListBox and then use the .Column
property of the ListBox to populate the textboxes on the Change event of the
ListBox.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
Peter L Kessler said:
Hi Group

I've managed to link a range in an Excel spreadsheet so that the data is
shown in a listbox on a Word 97 userform. The data consists of a series of
company names, contact names, addresses, etc. The userform reads all the
spreadsheet's data but only displays the company name and contact name in
one column in the listbox.

What I'm now struggling with is that when the user clicks on a company name
in the listbox list, the data for that entry should then appear in a series
of text boxes on the same userform.

This is what I have so far (shown below), but I can only get the first entry
to appear. It doesn't matter which section of the listbox I click on, only
that first entry appears. Can anybody help, please?

Best wishes
Peter




Kessler Associates
E: (e-mail address removed)
W: http://homepages.tesco.net/~plk33/plk33/index.htm



******************************
Private Sub lstCompany_Click()

Dim strKey As String

If lstCompany.ListIndex = -1 Then GoTo Ending

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' strEDBPath = path called from Registry entry
' strEDBTable = Excel range name called from Registry entry

' Open the database
Set db = OpenDatabase(strEDBPath + "", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM " + strEDBTable)

With lstCompany
If .ListIndex < 0 Then GoTo Ending
strKey = .List(.ListIndex, 1)
End With

With rs 'MyRecordSet
'.Seek Array(strKey), adSeekFirstEQ
If .EOF Then
MsgBox "Unable to seek to: " & ", Company: " & strKey,
vbOKOnly, Me.Caption
GoTo Ending
End If

' Populate Form controls
txtToAddress2.Text = AssignBlankIfNull(![name])
txtToAddress3.Text = AssignBlankIfNull(![Company])
txtToAddress4.Text = AssignBlankIfNull(![ADDRESS1])
txtToAddress5.Text = AssignBlankIfNull(![ADDRESS2])
txtToAddress6.Text = AssignBlankIfNull(![ADDRESS3])
txtToAddress7.Text = AssignBlankIfNull(![City])
txtToAddress8.Text = AssignBlankIfNull(![PostCode])
txtToAddress10.Text = AssignBlankIfNull(![Fax])
txtToAddress11.Text = AssignBlankIfNull(![TEL])
txtToAddress12.Text = AssignBlankIfNull(![County])
txtToAddress13.Text = AssignBlankIfNull(![Country])
End With

rs.Close
db.Close
End If

Ending:
End Sub

Public Function AssignBlankIfNull(ByVal varValue As Variant) As String

AssignBlankIfNull = IIf(IsNull(varValue), vbNullString, varValue)

End Function
Click to expand...
 
P

Peter L Kessler

Hi Doug

After posting that message, I approached the problem in a different way, and
ended up with the same result as you then posted.

So I now have all the company names visible in my listbox, all other columns
hidden, and all details relating to that company name appear in my textboxes
when the company name is clicked. The only problem now is that the required
format for the listbox is "company name" + ", " + "contact name" in one
column with no gaps other than those shown here. Do you have any idea as to
how I would go about altering the display in this way?

Best wishes
Peter




Kessler Associates
E: (e-mail address removed)
W: http://homepages.tesco.net/~plk33/plk33/index.htm






Doug Robbins - Word MVP said:
Hi Peter,

Why don't you load all of the data into to ListBox and then use the ..Column
property of the ListBox to populate the textboxes on the Change event of the
ListBox.

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.

Hope this helps
Doug Robbins - Word MVP
Peter L Kessler said:
Hi Group

I've managed to link a range in an Excel spreadsheet so that the data is
shown in a listbox on a Word 97 userform. The data consists of a series of
company names, contact names, addresses, etc. The userform reads all the
spreadsheet's data but only displays the company name and contact name in
one column in the listbox.

What I'm now struggling with is that when the user clicks on a company name
in the listbox list, the data for that entry should then appear in a series
of text boxes on the same userform.

This is what I have so far (shown below), but I can only get the first entry
to appear. It doesn't matter which section of the listbox I click on, only
that first entry appears. Can anybody help, please?

Best wishes
Peter




Kessler Associates
E: (e-mail address removed)
W: http://homepages.tesco.net/~plk33/plk33/index.htm



******************************
Private Sub lstCompany_Click()

Dim strKey As String

If lstCompany.ListIndex = -1 Then GoTo Ending

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim NoOfRecords As Long

' strEDBPath = path called from Registry entry
' strEDBTable = Excel range name called from Registry entry

' Open the database
Set db = OpenDatabase(strEDBPath + "", False, False, "Excel 8.0")
' Retrieve the recordset
Set rs = db.OpenRecordset("SELECT * FROM " + strEDBTable)

With lstCompany
If .ListIndex < 0 Then GoTo Ending
strKey = .List(.ListIndex, 1)
End With

With rs 'MyRecordSet
'.Seek Array(strKey), adSeekFirstEQ
If .EOF Then
MsgBox "Unable to seek to: " & ", Company: " & strKey,
vbOKOnly, Me.Caption
GoTo Ending
End If

' Populate Form controls
txtToAddress2.Text = AssignBlankIfNull(![name])
txtToAddress3.Text = AssignBlankIfNull(![Company])
txtToAddress4.Text = AssignBlankIfNull(![ADDRESS1])
txtToAddress5.Text = AssignBlankIfNull(![ADDRESS2])
txtToAddress6.Text = AssignBlankIfNull(![ADDRESS3])
txtToAddress7.Text = AssignBlankIfNull(![City])
txtToAddress8.Text = AssignBlankIfNull(![PostCode])
txtToAddress10.Text = AssignBlankIfNull(![Fax])
txtToAddress11.Text = AssignBlankIfNull(![TEL])
txtToAddress12.Text = AssignBlankIfNull(![County])
txtToAddress13.Text = AssignBlankIfNull(![Country])
End With

rs.Close
db.Close
End If

Ending:
End Sub

Public Function AssignBlankIfNull(ByVal varValue As Variant) As String

AssignBlankIfNull = IIf(IsNull(varValue), vbNullString, varValue)

End Function
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

populate listbox form database table 0
Listbox not working 2
listbox - questions 2
building an array for a listbox 2
listbox question - automatic selection if only one entry 0
Filling a ListBox with unique values 3
Listbox 4
Textbox help 2

Top