Storing multiple selections from listbox to SQL-DB (ADP) ... help

[Sonja via AccessMonster.com]

I am setting up a ADP system to store information about monthly society
meetings.
To store which persons were present I would like to be able selecting
multiple persons from a listbox (multi selections: extended).

I'have found several items about this but somehow it don't get it working.

I know it should be done with a many to many relationship between the tables.
In my sql-db I have created 3 tables for this. (below)

In my Form (which is bound to tblMeeting) I have a sub form with the listbox
on it;
* Link Child fields property of the sub form is set to tblMeeting.meeting_ID
* Link Master fields is set to tblMeetingDetails.meeting_ID
* Row source is tblMember.member_ID
* Control source is set to tblMeetingDetails.member_ID

When I select mutiple persons in the listbox and then store the record, no
member _ID's
were stored in the tblMeetingDetails.

tblMeeting
- meeting_ID (Pk)
- meeting_date
- meeting_location
- etc.

 

[Michel Walsh]

Hi,



Something like:

-----------------
Dim db As Database : Set db=CurrentDb
Dim varItem As variant

For each varItem in Me.ListBoxName.ItemsSelected

db.Execute "INSERT INTO MeetingsDetails(meeting_ID, member_ID) VALUES("
& _
actualMeetingID & ", " &
Me.ListBoxName.ItemData(varItem) & ")" , _
dbFailOnError

Next varItem
--------------------



Hoping it may help,
Vanderghast, Access MVP

 

[Sonja via AccessMonster.com]

Thanks for your quick response Michel.

Question,
For which event and where should I put the code.
(I'm not that familiar with VB code)

The result should be that every selected persons will create a record in the
MeetingDetails Table
which also contains the meeting_ID of the current record in the main form.

Sonja

Hi,

Something like:

-----------------
Dim db As Database : Set db=CurrentDb
Dim varItem As variant

For each varItem in Me.ListBoxName.ItemsSelected

db.Execute "INSERT INTO MeetingsDetails(meeting_ID, member_ID) VALUES("
& _
actualMeetingID & ", " &
Me.ListBoxName.ItemData(varItem) & ")" , _
dbFailOnError

Next varItem
--------------------

Hoping it may help,
Vanderghast, Access MVP

I am setting up a ADP system to store information about monthly society
meetings.

[quoted text clipped - 39 lines]

Sonja

 

[Michel Walsh]

Hi,


Since the list box has no way to know WHEN you are finish with your MULTIPLE
selection (by opposition to a SINGLE selection is known to be done as soon
as one is clicked), I would provide a button to explicitly signify you are
done with the selection and it is under its onClick event procedure that I
would INSERT multiple records.

Note that the newsreader has cut my VBA lines of code where there should be
no such cut. I hope you are able to identify and figure out what "should be"
on the same line and what is not... If not, I can try to rewrite the code.



Hoping it may help,
Vanderghast, Access MVP

Thanks for your quick response Michel.

Question,
For which event and where should I put the code.
(I'm not that familiar with VB code)

The result should be that every selected persons will create a record in
the
MeetingDetails Table
which also contains the meeting_ID of the current record in the main form.

Sonja

Hi,

Something like:

-----------------
Dim db As Database : Set db=CurrentDb
Dim varItem As variant

For each varItem in Me.ListBoxName.ItemsSelected

db.Execute "INSERT INTO MeetingsDetails(meeting_ID, member_ID)
VALUES("
& _
actualMeetingID & ", " &
Me.ListBoxName.ItemData(varItem) & ")" , _
dbFailOnError

Next varItem
--------------------

Hoping it may help,
Vanderghast, Access MVP

I am setting up a ADP system to store information about monthly society
meetings.

[quoted text clipped - 39 lines]

Sonja