Reading a *.csv file into a combo box

[Joy M.]

Hi,

I have a list of department IDs, which are stored in an Excel *.csv file.

Would it be possible to create a form with a combo box on it, so that:
When the form loads, the department IDs would be read into the combo box
If the user wants to add another department ID, he can add it
If the user wants to delete a department ID he can remove it
When the form unloads, the revised set of department IDs is written to
the Excel *.cvs file

Questions: what if he adds a department ID which is already in the combo
box?
Is it possible to sort the contents of the combo box?

There are only a few department IDs and they won't be changing very often.
Is this worth doing or should I just show the user to how to modify the
Excel *.csv file directly?

If you could describe the code I would need, that would be very helpful to
me.
I have started tackling it on my own, with limited progress.

Thanks for your help,

Joy

 

[Byron]

Hi, Joy,

One approach to accomplishing what you want is to import
the .csv file into a table in Access. Then use this table
or a query based on this table as the record source for
your combo box. You can then let the user add items to
the list (the table) and/or delete items from the list
(table) and check for duplicates thus preventing that
issue.

Then when when the form unloads or when the application
closes you can simply export the table to a .csv file.

As for sorting the list in a combo box: If you use the
approach described above, you can sort on any field in
the query that produces your list for the combo box.

HTH

Byron

 

[dandgard]

First of all a .csv file is not an 'excel' file. It is a comm
seperated value text file. It may have been created from an exce
workbook but it could also be created by Access or any number of othe
programs.

There is a method of importing comma delimited text files availabl
through the File|Get External Data|Import menu path.

However this is not a trivial exercise. If you know VBA fairly wel
you could open the file directly, parse the information you want, an
load it into your combo box.

The next problem with this however is that a combo box is not a ver
good medium for asquiring and storing new information.

What you really want to do is to import this information into a table
create a form that displays the information to the user, allow the use
to look at and change the information in the table, and then write th
information back out to a .csv file

 

[onedaywhen]

There is a method of importing comma delimited text files available
through the File|Get External Data|Import menu path.

What you really want to do is to import this information into a table

What these well intentioned people are not telling you is that you
*can* query/update a .csv file direct and that it *is* a trivial task
e.g.

SELECT [department ID]
FROM [Text;Database=C:\MyFolder\;HDR=Yes;].MyFile#csv
;

INSERT INTO
[Text;Database=C:\MyFolder\;HDR=Yes;].MyFile#csv
([department ID]) VALUES ('101')
;

You can also open an ADODB connection to a text file (e.g. to use a
recordset) using the MS OLEBE provider for Jet e.g. connection string:

Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\MyFolder\;
Extended Properties="text;HDR=Yes"

Because the data source is the entire folder you can even create joins
between text files in the same folder.

--