Populating an Unbound Combo Box Using VBA

This tip relates to question related to populating an unbound combo box using VBA

Question:

How can I load my table data into an unbound combo box using VBA code. I am using front-end back-end two different databases.

 
Answer:

The answer I provided utilises VBA code.  I have provided the basics here but if you wish to learn more (for a very resonable price), then Click Here.

The code to achieve what the questioner requires, is as per below.


 '*****

 Dim strSQL As String

 strSQL = "Select [Field1],[Field2] FROM [tblYourTable]"

 Me!Combo15.RowSource = strSQL

 '*****

A couple of points re the above code:

You should be able to access the back end tables using the above, as I am presuming they are linked to the front end.

Change the field names, combo name and table name to suit. Add in as many fields as you need from the table, putting a comma between each one.

You may need to change the column count and column width properties, depending on how many fields you have.

 I.e.:

Me!Combo15.ColumnCount = 2

Me!Combo15.ColumnWidths = "0cm ; 2 cm"

Add this to the 'On Load' event on the form - or somewhere else, depending on what you are hoping to achieve.

Where to put the code:

If you are unfamiliar with VBA coding for Microsoft Access, and you are not sure where to put the above code then these instructions may help.

The best place to put this code is the 'On Load' event for the form.  This means when the form is loaded, the above code will run and will add will result in populating an unbound combo box using VBA.

To add the above code to the 'On Load' event for the form:

  • Open the form in 'Design View'
  • Open the 'Properties' for the form (Right click in the grey square in the top left hand corner of the form, where the two rulers meet, and select 'properties' from the drop down list)
  • Select on the 'Event' tab
  • Place your curser next to 'On Load' and click on the button with the three dots, that should now appear next to 'on Load'
  • Select 'Code Builder' then OK.
  • The following three lines of code will appear:

Option Compare Database

Private Sub Form_Load()

End Sub

  • Copy the code above, and past it in between the last two lines of code. (I suggest you delete and add back the talking marks if you are copying and pasting the code as VBA has specific type of talking marks)
  • Close the VBA window by click on the most outer cross.
  • Close and save your from.
  • Re-open your from and check to see if that your checkbox loads properly.

I hope this helps you in populating an unbound combo box using VBA

If this is your first taste of VBA and would like to learn more (for a very resonable price), then Click Here.