Change text box when listbox item is selected

[johnmcd]

Hi all,

I'm a newbie with Access (although I do have some experience programming other MS products (like VB)) so I'm not sure I'm even asking this the right way, but here goes:

I have a database with 3 tables:

tblRequirements contains requirements and has the following fields:

Number (Text)(PK) - Requirement number (e.g. 10.2.3)
Title (Text) - Requirement title
Description (Memo) - A detailed description of the requirement

tblSolutions contains descriptions of various solutions:

Key (AutoNumber)(PK)- An auto-generated primary key
Vendor (Text) - Name of vendor that supplies the solution
Name (Text) - Name of product/solution
Description (Memo) - Details of the solution

The third table, tblReqtsSolsMap, is a junction table that maps requirements and solutions. Since a given requirement can be adressed by more than one solution, and a given solution can address more than one requirement, it's a many-to-many mapping. Here are the fields:

Key (AutoNumber)(PK)- An auto-generated primary key
RequirementKey (Text) - The key of the requirement being mapped (from tblRequirements)
SolutionKey (Number) - The key of the solution being mapped (from tblSolutions)

I've managed to create individual forms for entering the requirements and solutions info, and I've started on a form that allows the user to map solutions to requirements, but I'm stuck on one piece of functionality I'm trying to add. Right now the 'mapping' form (a data entry form) has two side-by-side list boxes. The one of the left lists the number and title of the requirement, and the one on the right lists the company and name of the solution. If a user selects a requirement in the left listbox and a solution in the right listbox and clicks on the 'Add Map' button, everything works fine - a new (correct) mapping record is created in the tblReqtsSolsMap table.

What I'd like to add is a text box(?) underneath the left listbox (Requirements) that shows the 'Description' memo field for whatever requirement the user selects in the 'Requirements' listbox to provide them with more details so they can map the correct solution. Since the 'Description' field in tblRequirements is a memo field and usually fairly long I can't effectively include it in the requirements list box.

What would be the most effective way to display detailed information for whatever item is currently selected in the listbox? I've looked through the Access help and several books, and a sub-form might work, but I have no clue how to trigger an update when the selection in the Requirements listbox changes, then pass the associated Requirements Number to the sub-form so it can retrieve and display the appropriate Description.

If someone can just describe the basic steps involved that would be great - I'm not even sure I'm searching on the right words when I'm researching this.

Thanx.

John

 

[CEH]

This might help you John... Just a little DB to demonstrate different ways to open forms.
"I have no clue how to trigger an update when the selection in the Requirements listbox changes" This one would be a Requery command. If you used a subform you could put this code on the after update Actually right in the Property box (right click you textbox and look for "AfterUpdate") =SubformName.Form!ItemID.requery ...... "ItemID" would be on the subform.

 

[boblarson]

John:

If I'm understanding you correctly, you want a text box to display the text while the combo box stores the value. If you include the text in the rowsource of the combo box, you can display it by using this code in the After Update event of the combo box:

Me.YourTextBoxName = Me.YourComboBoxName.Column(1)

assuming that the text is the second column in the rowsource, but you can change it to whatever column it actually is, remembering that it is zero-based so if it is the third column you would use 2 and if the fourth column you would use 3, etc.

 

[johnmcd]

Thanx!

Bob & Curtis,

Thank you both very much. I'm going to play around with both suggestions and see what I can come up with. I appreciate the quick responses.

Have a great New Year!

John

 

[johnmcd]

Got it!

All,

Thanx to your help I've figured it out. I modified the VB code in the 'Click' function for the Requirements listbox to include the following:
===========================================
Dim myConnection As ADODB.Connection
Dim myRecordSet As New ADODB.Recordset
Dim mySQL As String
Dim selectedRequirementKey As String

'Connect to our database
Set myConnection = CurrentProject.AccessConnection
Set myRecordSet.ActiveConnection = myConnection

'Get the Key of the currently selected requirement item in the listbox
selectedRequirementKey = Me.List12.ItemData(Me.List12.ListIndex)

'Build the SQL string to obtain the associated Requirement record
mySQL = "SELECT Description FROM [tbl_Requirements] " & _
"WHERE [tbl_Requirements].Number='" + selectedRequirementKey + "'"
myRecordSet.Open mySQL

' Set the control description textbox to the description for the current control
Me.ReqDescDesc1 = myRecordSet.Fields(0)

'Clean up
myRecordSet.Close
myConnection.Close
Set myRecordSet = Nothing
Set myConnection = Nothing
=============================================
ReqDescDesc1 is an unbounded text box that now displays the detailed description of whatever requirement is selected in the listbox.

Thanx again.

John