Question Populating Combo Box with delimited field

[NeutronFlux]

Hello,

So I'm working on an Access 2007 application for my company's suppliers list, but I am restricted to using a single Excel worksheet for the back-end. One of the fields, called [Files], is a list of files in .pdf format related to a supplier. The number of files can be 0, 1 or many. I currently have this field set up so that each file is separated by a semi-colon. A sample first record in [Files] is:
K:\Users\test user\2008-12-24-ASTM D5857-08.pdf;
K:\Users\test user\2012-03-12APCPP25-Black.pdf;
K:\Users\test user\DIN_16901.pdf

The front-end contains a multi-item form. I'm trying to add a ComboBox (cboFiles) for each record, which is populated by the value in [Files]. Then, selecting an item in the ComboBox should automatically open that file.

I've tried a few approaches so far for populating the ComboBox:
Setting [Files] as the Control Source results in a single item appearing as
"K:\Users\test user\2008-12-24-ASTM D5857-08.pdf;K:\Users\test user\2012-03-12APCPP25-Black.pdf;K:\Users\test user\DIN_16901.pdf"

I also tried setting the ComboBox Record Source Type to Value List and created a hidden TextBox (txtFiles) with the Control Source set as [Files]. Then I used VBA in Form_Load to equate the ComboBox Row Source to the TextBox.
The code is simply:

Private Sub Form_Load()
    Me.cboFiles.RowSource = Me.txtFiles
End Sub

While this does create a ComboBox with the correct items for the first record, it also populates the ComboBox for all the other records with the same thing, ie. every ComboBox has the items:
- K:\Users\test user\2008-12-24-ASTM D5857-08.pdf
- K:\Users\test user\2012-03-12APCPP25-Black.pdf
- K:\Users\test user\DIN_16901.pdf

I'm thinking maybe this approach could work with some modification in the code or approach. Unfortunately, I'm not too clear on why the code goes through all the Me.cboFiles.RowSource, but only once on Me.txtFiles or how to get it to check the txtFile for each row. Otherwise, I will need to figure out some other way to fill in the ComboBoxes.

Suggestions for fixes or alternative solutions will be greatly appreciated.

Edit: Actually, I've noticed something peculiar. Each time I select an item from a ComboBox, the other ComboBoxes for the other records also change to the same item. I think this has to do with cboFiles being unbound. Changing it to bound doesn't seem to be quite right though as it just gives the same default values as txtFiles while the items are still just copied from the first cboFiles. Also, I can't actually select any of the items when it's bound. I imagine it might have to do with the read-only attributes of a linked Excel File.

 

[MarkK]

You can delimit each item in your value list, like...

'K:\Users\test user\2008-12-24-ASTM D5857-08.pdf';'K:\Users\test user\2012-03-12APCPP25-Black.pdf';'K:\Users\test user\DIN_16901.pdf'

Use single quotes or quotes.
Mark

 

[Beetle]

Sounds like you're using a continuous form, which contributes to the problem because of the nature of that type of form. It is really just a single row of controls repeated for each record, so when you have unbound controls like your combo box that rely on data from other columns in each record it becomes problematic. Is it necessary in your circumstances to use a continuous form, or could you use a single form? If the former then we may be able to come up with a work around but it might be a bit tricky in this situation.

 

[NeutronFlux]

Sounds like you're using a continuous form, which contributes to the problem because of the nature of that type of form. It is really just a single row of controls repeated for each record, so when you have unbound controls like your combo box that rely on data from other columns in each record it becomes problematic. Is it necessary in your circumstances to use a continuous form, or could you use a single form? If the former then we may be able to come up with a work around but it might be a bit tricky in this situation.

Yes, you're right.

To elaborate a bit, it's a search form where I have some Text and Combo Boxes on the main form as well as a few continuous subForms that display the records, filtered according to the controls on the main form. But due to page-size restrictions, I was asked to reduce the height of the subForms to show one record each, so maybe I can just change the subForms into single forms. Although I haven't quite thought through how this will affect the search, display and navigation of stuff.

I'm currently basing a lot of the search function off of Allen Browne's Search criteria example.

 

[Beetle]

A couple of questions;

1. How many rows are we talking about in the Excel file?
   
2. Is there a column in the Excel file that can be used to uniquely identify each row?

 

[NeutronFlux]

Sorry for the late response. The website wasn't responding for me for a few hours before I left work yesterday.

1. There's a bit over 300 rows.
2. I haven't explicitly set up something to be used as a key. I could though.

I actually managed to make something work with your suggestion of using Single Forms. The filtering stayed the same, and I just had to change the up/down arrow keys and mouse scroll to navigate records. Although if you have another solution, I'd love to hear it as it might be a lot better than my own.

Thanks!

 

[Beetle]

If your current solution is working well for you then I wouldn't try to sway you from that, but if you just want to look at another alternative I have attached a sample file.

This example uses a local table to store the files. When the db is opened some VBA code runs that loops through the linked Excel table and writes each individual file name to a separate row in the local table. The code runs in the Open event of a start up form, which then closes and opens the main form. The code takes the semi-colon delimited string from the Files column in the Excel file and creates an array, then writes the items from the array to the local table. In order for this to work you would need a column in the Excel file that can uniquely identify each row. That's also why I asked how many rows are in the Excel file. With only 300 or so rows this loop probably wouldn't take too long. If you had thousands (or tens of thousands) of rows, then the loop might take too long for this to be a feasible option.

The main form displays the general fields from the Excel table and it has a sub form (based on the local table) that displays the files. The zipped file includes the db and a sample Excel file. You will need to re-link the Excel file in the db when you first open it.