What you are setting up is called a many-to-many (one record could have many
files and any single file may be attached to more than one record)
relationship. If a file can't be attached to more than one record, this
would then just be a one-to-many relationship. That would be a little
easier, but not as flexible.
I suspect you are adding the file to the listbox by adding it to the
listbox's Row Source. Instead, the Row Source should be a query based on a
new table (actually, 2 new tables for a many-to-many relationship). As you
add files, you would actually add them to this table and then requery the
listbox to get them to show. You may find a subform easier to use if you go
with the one-to-many relationship setup.
The basic setup of a many-to-many relationship is as follows:
Table1
Primary Key Field
Descriptive Fields as needed
LinkingTable
Primary Key from Table1
Primary Key from FileTable
(make both of these fields the primary key for this table,
individually, they are foreign keys for linking purposes)
FileTable
PrimaryKey field
File description data fields as needed (i.e. path to file)
The query for the Row Source of the listbox would be as follows:
SELECT FileTable.PrimaryKeyField, FileTable.FileNameField,
FileTable.FilePathField
FROM FileTable INNER JOIN LinkingTable
ON FileTable.PrimaryKeyField = LinkingTable.[PrimaryKey from FileTable]
WHERE LinkingTable.[Primary key from Table1] = Forms!MyForm!PrimaryKeyField;
The form would be based on Table1, so the PrimaryKeyField of the form would
be the primary key of Table1. This will cause the listbox to display the
files listed in the FileTable that are associated with the current form
record via the LinkingTable.
When you add a file to a record, you would need to write the data for the
file to the FileTable, if the file doesn't already exist in that table. The
PrimaryKey of the file table will probably be an AutoNumber field. You would
then need to write a record to the linking table with the PrimaryKey value
you have from the record you just added to the FileTable (or found if the
file was already in the table) and the form's PrimaryKey field's value.
Next, requery the listbox (Me.lstMyListbox.Requery).
To determine if a file already exists in the table, check the FilePath field
since only one file can occupy any single path. However, be aware that a
file can have more than one path. For example, if I share a folder on my C:
drive called SharedFiles, I can have two paths to files in that folder.
1) C:\SharedFiles\FileName.txt
2) \\ComputerName\SharedFilesFolder'sShareName\FileName.txt
The first one is only usable only from the local computer. The second one is
usable from any computer on the network, including the local computer.
