Populating fields with List Box Selection * I am a Beginner

[JRMS]

I have created a database currently with one table and will eventually with grow with more. I have created a form with a list box. My question is how do I create a list box that once an item is selected from the list, other boxes are update with information from the table. For example, if I select the sysid from a list box, how can I get the other boxes to list the system name, owner, start date, etc. Please help. I am using Access 2003. Thanks in advance.

 

[Remou]

Are you set on using a listbox? The combo box wizard will guide you through creating a combo that finds records in your form based on the combo selection. This can also be done with a listbox, but if you are just starting out, you may wish to look at some of the wizards.

 

[JRMS]

Remou, thanks for your reply. I have attempted to use the wizards. It is my understanding that the combo box will allow you to include multiple selections at once. My goal is to select one item from a list and the other boxes (List Boxes??) will populate with its respected information. I hope I am explaining it correctly. Please help. Thanks.

 

[Remou]

Very roughly, there are two broad ways to populate controls, generally textboxes:

- You can fill data into fields from a listbox, combobox or with other code. This way of populating controls is very useful for unbound forms but means that any changes to the data have to be written back to the table.

- You can filter a recordset, create a recordset or move to a record in a recordset based on a selection in a list box or combobox. This means that 'live' data is returned and when it is changed, the change directly affects the table.

With the first method, a combo box or listbox would have several columns, say, ID, FirstName, LastName, and controls would be populated by referring to the column:

Combo:
cboOne.Column(0) 'ID
cboOne.Column(2) 'Lastname

Simple ListBox:
lstOne.Column(1) 'Firstname

The controls can either have a control source that refers to the column property or they can be populated programmatically in a suitable event.

With the second method, selecting an item in the listbox or combo boxes fires code that either filters the recordset or moves to the relevant record.

Using the combo box wizard, you will get this code:

Private Sub cboOne_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Me![cboOne])
    Me.Bookmark = rs.Bookmark
End Sub

This can be modified to suit a listbox.

 

[JRMS]

Remou, thanks for the response. I discovered that by creating subforms it is possible to select "system" from the combo box and the the info should populate. I used the following website:

http://www.techonthenet.com/access/forms/filter_form.php.

However, my confusing is still how to populate the other detailed information. Please visit the about website to see exactly what I need. Thanks for your help. Please respond.

 

[Remou]

Please view the Orders form in the Northwind sample database.

 

[JRMS]

I will do so. Thx.