Creating records based on multiple items selected in a list box

[ncalcaterra]

hi! would love some help on (what i'm sure to most of you is simple) this list box problem... i have a form with the following fields (form name is fCorrespondence and the table name is tCorrespondence):

CorresID
EnteredOn
EnteredBy
CorresDate
TransmittedVia
ReferenceNo
Task
From
To
Description
ProjectID (List box)

the ProjectID field is my list box, and i'd like to be able to select multiple values from the list box and have one record created for each value selected in the list box in the underlying table (tCorrespondence). all the fields listed above will have all the same information, except the ProjectID field (which is why i want to have a separate record created for each selection).

To summarize the above in plain english, i'd like to make one entry for each correspondence, but each piece of correspondence could relate to several projects, and i'd just like to create one entry and highlight (or check) which projects the correspondence applies to. Later on, i'll have to report on the correspondece for each project (fyi).

i would be forever indebted to the individual who could help me solve my problem!!!! thanks for reading, and thanks for any help you can provide.

 

[Eupher]

How is the listbox populated?

Ken S.

 

[ncalcaterra]

from tProject (which has the following fields):

ProjectID
ProjectName

thanks Ken!

 

[Eupher]

Hi, ncalcaterra,

So your form is intended strictly for entering new data, never for viewing existing data?

Ken S.

 

[ncalcaterra]

Mostly for just entering data, but it would be nice to use if we ever had to go back and look something up. thanks ken

 

[Eupher]

Is the form bound to tCorrespondence?

Ken S.

 

[ncalcaterra]

hey ken, yes, it is bound to tCorrespondence.

 

[Eupher]

Your original post is correct, it is not difficult to loop through the items selected in a listbox to write data, set criteria for a report, whatever. But your stated form design concerns me, particularly the use of the listbox, and I think you are asking for headaches. On the one hand, using the listbox for multiple selections is easy. On the other hand, to use a bound multi-select listbox to display data is problematic because you can't bind multiple selections. I'm also concerned by your intended use of redundant data:

all the fields listed above will have all the same information, except the ProjectID field

One of the purposes (and benefits) of a relational database is the reduction/elimination of the need for redundant data.

Here's a bit of code which shows you how to loop through the selected items of a multi-select listbox:

Dim Entry As Variant
Dim rs As DAO.Recordset

Set rs = Me.Recordset

For Each Entry in Me!ProjectID
    rs.AddNew
    rs!CorresID = Me!CorresID
    rs!EnteredOn = Me!EnteredOn
    'etc., continue for each field
    rs!ProjectID = Me!ProjectID.ItemData(Entry)
    rs.Update
Next Entry

rs.Close

Of course you may have perfectly valid reasons for organizing your data in this manner - sometimes it's hard to tell in a forum like this. I suggest, however, you use an unbound listbox for your ProjectID selections and a simple textbox to actually display the ProjectID for each record. Also suggest you examine your data structure and see whether you really want all that redundant data; take a look at one-to-many relationships and normalized data. Just a thought...

HTH,

Ken S.