Populate listbox from combo

[jegenes]

How would I populate a listbox from choices made in a combo box? I want to be able to choose from the combo box repeatedly. Both boxes have 3 columns.

Basically, I want the selected item in the combo box to be copied to the listbox, adding it to whatever is already in the listbox. Then, upon updating the record, I want to clear the listbox and start over. I've already got the combo box working so that my choices are available. Thanks.

John

[This message has been edited by jegenes (edited 08-25-2001).]

 

[Carol]

For the sake of this example, assume that your existing combo box is called Patient ComboBox (showing the PatientID and LastName) and that you want to add the selections to your table Appointments.

RecordID is the name of the field that is usually your primary key field.
-------------------------------------------
Create a Command Button. Do not use the Wizard. Name this button Add

In the OnClick of the command button, as an Event Procedure, put this code:

Private Sub Add_Click()

If Me.RecordID <> "" And Me.PatientComboBox <> "" Then
Dim mysql As String
mysql = "INSERT INTO Appointments (PatientID, LastName) "
mysql = mysql & "VALUES (" & Me.RecordID & "," & Me.PatientComboBox & ");"

DoCmd.SetWarnings False
DoCmd.RunSQL mysql
DoCmd.SetWarnings True
Me.PatientComboBox = ""

Me.PatientList.Requery
Else
MsgBox "Please choose a patient to add"

End If

End Sub
-------------------------------

Next create an Unbound List Box and name this PatientList.

-------------------------------

Once you make a selection from the combo box, highlight that selection and click the Add button. This then transfers the information into your unbound list box and creates a new record into your underlying table.

You can adjust the code to reflect the fields that you want.

You could also have an Event Procedure within your unbound list box, that on the On Click will delete the current record highlighted from the underlying tables in case you made an error.

Good luck.

 

[jegenes]

Thanks very much, Carol. I'll give this a shot and let you know how it works out.

Best,

John