Populating List Box based on Values in Combo Box

[blairsly]

I have a Main Form frmJOBS in which a subform frmVENDOR_JOBINFO is embedded. As there can be multiple Vendors per Job, I am experimenting with ways add Vendors. Although I like the datasheet view, the address/contact info as well as other information needs to be easily accessible, and therefore I must use a form view.

*IDEALLY* I would like to have a combo box (List of ALL Vendors from tblVendors) with Command Button that allows user to "Add Vendor to Job". Clicking this Button inputs the Vendor's name the Vendor_Job List Box, afterwhich clicking on the name in the List Box, brings up the address, and other info related Job Info unique to this Vendor in the subform frmVENDOR_JOB info.

This forum and all the DB examples that people have posted have been a wonderful resource, but I feel like I have run into a bit of a wall, and would GREATLY appreciate any advice.

Cheers,
Blair

 

[Liv Manto]

From Access Clip:


NotInList Event — Event Procedures Example

The following example uses the NotInList event to add an item to a combo box.

To try this example, create a combo box called Colors on a form. Set the combo box's LimitToList property to Yes. To populate the combo box, set the combo box's RowSourceType property to Value List, and supply a list of values separated by semicolons as the setting for the RowSource property. For example, you might supply the following values as the setting for this property: Red; Green; Blue.

Next add the following event procedure to the form. Switch to Form view and enter a new value in the text portion of the combo box.

Private Sub Colors_NotInList(NewData As String, _
Response As Integer)
Dim ctl As Control

' Return Control object that points to combo box.
Set ctl = Me!Colors
' Prompt user to verify they wish to add new value.
If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then
' Set Response argument to indicate that data
' is being added.
Response = acDataErrAdded
' Add string in NewData argument to row source.
ctl.RowSource = ctl.RowSource & ";" & NewData
Else
' If user chooses Cancel, suppress error message
' and undo changes.
Response = acDataErrContinue
ctl.Undo
End If
End Sub
Note The above example adds an item to an unbound combo box. When you add an item to a bound combo box, you add a value to a field in the underlying data source. In most cases you can't simply add one field in a new record — depending on the structure of data in the table, you probably will need to add one or more fields to fulfill data requirements. For instance, a new record must include values for any fields comprising the primary key. If you need to add items to a bound combo box dynamically, you must prompt the user to enter data for all required fields, save the new record, and then requery the combo box to display the new value.