Can you pass an array from a Sub into a Form?

Question

I've written some code that:

• Lets the user import a text file or spreadsheet as an input source.

• Prompts the user to select the field in which the relevant data is held.

• Performs the program actions (which is basically checking some things on a website and then updating the website if criteria are met).

The thing I've had to "bodge" is the prompting for where to find the data. I initially created a simple array to capture the field names from the input source, but then I hit a stumbling block on how to get that into a user form and into a combo box.

I settled in the end for creating ANOTHER temporary table and instead of inserting the field names into the array, saving them into the recordset, which can then be accessed from the Userform by creating another recordset and querying it and deleting everything from the recordset apart from the selected one.

Whilst it works, and from the outside you wouldn't know what it was doing since the data involved is very low volume, it's very quick.

I'm just aware there's probably a better solution and I'd rather ask the question and learn my mistake/oversight while it's fresh in my mind.

Best regards,

Jeff.

Answer 1

If I understand correctly, you want to display the field names from the table in a combo box. If that is correct, simply set the Row source Type' property of the combo box to 'Field List' and set the Row Source to the table.

Answer 2

A combo box has a property "row source type" on the DATA tab. By default isis table query.

however, You can set it to "Value list", which accepts a string of values to be shown in the combo box.

So if it was set to "1,2,3,4,5,6" you would get a one column list of the values shown.

Other properties you may need to adjust are : column widths, column count, and bound column.

this is an easy way to populate a combo with a short list of values.

Don't forget to use the requery/refresh method if you need to.

Instead of using a subform replace it with a list box which can look very much like a subform, otherwise you have to do what you did with a temp table.

The listbox can be populated in the same way as a combo box.