I totally agree to register data seperately instead of sharing them in one field, but I will explain why there is sometimes more than one value in my field.
I have created a database for Fire Insurance. When the insured didn’t pay his premium, I need to send a letter to the insured, his broker and a copy to all the companies who are coassured in the policy.
The problem is that the companies who are coassured in the policy, were never registered in the database. So I created a multivalue listbox where I need to select manually those companies.
After this multiselection I wrote code in VBA to fill in a table (tblLetters) where I register the letters that need to be send.
Here is the code I used to get the multiselection of the companies in one string to fill in the field in the table
'Declare Variables
Dim varCompanies As Variant
Dim Choice As Variant
varCompanies = ""
For Each Choice In Me.lstCompanies.ItemsSelected
varCompanies = varCompanies & Me.lstCompanies.ItemData(Choice) & ","
Next Choice
varCompanies = Left(varCompanies, Len(varCompanies) - 1)
I now created another listbox with the data from tblLetter. From this listbox I want to create a query to find the related addresses of the companies that were registered in the table.
The reason I work this way is because the letters are made in Ms Word, and I need a visible query for it, am I right?
Or is there a way to get the variable I used for the multiselection to fill in the table to use also as the query criteria?