MS Access: Making a subform field combo box populate with data from separate table?

0
votes

I'm trying to convert a form previously made in an old Excel to something that can be used in current versions of Access. The Excel version has a feature where values put in a field in one table can be referenced to be used as values in another table. Ex:

Table A: Name Field has values {Joe, John, Lisa, Mary, Max}

Table B: Click on name field, values to choose from {Joe, John, Lisa, Mary, Max}

I'm attempting to replicate this in MS Access with subforms connected to tables. My current set-up is:

Table A: Name Field has values {Joe, John, Lisa, Mary, Max}

Table B: Name field connected via Lookup Wizard to Name Field in Table A

Subform: Table B displayed in subform. Name field of Table B is a combo box.

I want the combo box to drop down containing the contents of Table A's Name field. However, when I click on that combo box, I simply get an error noise and nothing happens.

I haven't attempted any coding, as I'm not well versed in VBA, so there may be a better way.

If this isn't enough information, I'll try to provide more.

1
ms-accessvbams-access-2010
Did you use the wizards? There is a combobox wizard and a form wizard. – Fionnuala
I used the form wizard to create the subform. – 9Deuce
Use the combobox wizard. It works in one way with a bound form and another way with an unbound form. Play around a bit. – Fionnuala
@Remou Thanks. I simply skipped over Table B and linked the Combo Box in the Subform with Table A's Name Field. Hopefully this doesn't cause any problems later down the road. – 9Deuce

1 Answers

0
votes
  1. Create a MS Access query of the field containing the values in table A (should look like this "Select Field from TableA;").
  2. Copy the query SQL and paste SQL string into the combobox property "Row Source".
  3. Set the combobox property "Row Source Type" to "Table/Query".