Populate the selected data from sub form to drop down list

[albert_leung]

How can I populate the selected data from sub form to the drop down list? My sub form has 5 columns and only column 1 data of selected record will be populated in the drop down list. How can I do that and what VBA code I should use? Thank you.


Albert

[June7]

Why do you need this? Is the combobox bound or unbound?

[albert_leung]

Why do you need this? Is the combobox bound or unbound?

The combobox is unbound. Indeed, the users prefer to select the item (one item at a time) in the sub form rather than populate the value by using SQL and populate in the combobox (I populate the values on combobox by using SQL already). Just wonder if i can do both. Thanks.

[June7]

I still don't understand what is going on. Why does this one value need to go to an unbound combobox? I am really confused by your second post. What is meant by 'users prefer to select the item (one item at a time) in the sub form rather than populate the value by using SQL'. How would users use SQL? What are the 'both' you refer to? If you have something working what needs to be different?

[albert_leung]

Currently, we have a sub form in order to display the borrowed items and the combobox for the librarian to select the item and click the return button to check in the item. We populate the borrowed item # on the combobox by using SQL.
Now, user would like to select the item on the sub form and populate the item # in the combobox automatically.

That's what I need. Please help.
Albert

[June7]

Still confused. If the item is in the subform why is it not already in the combobobx RowSource? Guess I have to see to understand if you want to provide the project. What is nature of this database? Sounds like a lending library. Have you checked out the MS Lending Library template?

[albert_leung]

Yes, it is an Access database for library, I have attached the screen shot for your reference.
We populate the checked out items' call number by using SQL in combobox already. However, users do not want to pick it from the combobox, they want to select the item on the sub form, which is the checked out record, and populate the call number on the combobox automatically. That's what I need your help.


Albert

[June7]

Oh, you don't need to add to the combobox RowSource, you want to set the Value of the combobox.

Is the combobox unbound? Is it multi-column. Show the combobox RowSource.

You could use the Click event of the Subform or its Detail section or of a particular control of the subform. Then possibly: Forms!mainformname.comboboxname = Me!CallNumber

[albert_leung]

I got it. Thanks everyone. I used the following code for that.

Private Sub YourFieldNameHere_Click()

' change the Field name and Combobox name accordingly
Me.Parent.YourComboBoxNameHere = Me.YourFieldNameHere

End Sub

[goodguy]

Just a curious question: why do you need the combobox if you just want to give the users an option to 'return' the book? You could do it just as easily directly for the selected row from the subform!

[albert_leung]

At the beginning, I thought the user select the call # in the combobox instead of selecting the call # from the sub-form. I have SQL to populate the call # as same as the sub form. Now, the user want to pick the call # from the sub form instead.
Albert