Create new records from MultiSelect

[Gary]

Hello, and thank you in advance to anyone that can assist
with this issue.

I have created a database using Access 2002 to track
employee training data in an organization. I have a table
that tracks employee data (EeTbl) and a table that tracks
Course Data (CourseTbl). I would like to be able to enter
the course data in CourseFrm (linked to CourseTbl) and use
a multi-select to choose multiple attendees. All this data
would ideally dump into a third table with relationships
to the first two (AttendTbl). [ Tracking only CourseID and
EeID].

That said, can anyone assist with a way to use the multi-
select to copy the course data into records for each
selected employee- thus "adding" the course to each
employee for later sorting and reporting?

Thank you again!
Gary in Chicago

 

[Rod Scoullar]

Gary,

You enumerate the selected items of a multiselect list box like this

Dim varPtr As Variant
Dim lngEeID As Long

For Each varPtr In ListBox.ItemsSelected
lngEeID = ListBox.Columns(N, varPtr) ' Where N is the column number
corresponding to the EeID
' Save the CourseID, EeID pair in AttendTbl using a SQL INSERT INTO
statement or a recordset AddNew
Next

Rod Scoullar

 

[Gary]

Thank you very much for your reply.

Forgive me for asking, but I'm new to VB.

How would you use the AddNew method to link records to a
table?

I need to add the records [ClsID] and [EeID] from the form
[AttendFrm] to matching fields in the table [AttendTbl].

Thank you again
Gary

 

[Rod Scoullar]

Gary,

Which version of Access are you using? The syntax is slightly different for
Access97 and Access2000/XP.

I'll assume the later versions.

Put a Command Button on your form. Name it cmdLinkAttendees.

Select the button, open the Properties dialog and select the Event tab.

Click in the box opposite On Click, then click on the elipsis (...) at the
end of the line.

Select Code Builder from the dialog and click OK

You should now be in the VB edit screen and see

Private Sub cmdLinkAttendees_Click()

End Sub

The code goes between those lines.

All the code should be indented except for the first and last line defining
the Sub. If anything else is not indented it has been wrapped by the
newsgroup reader and should be added to the end of the previous line.

Sorry if I have been too verbose.

Rod Scoullar


Private Sub cmdLinkAttendees_Click()
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim varPtr As Variant
Dim lngClsID As Long
Dim lngEeID As Long

' open a recordset which contains records from the AttendTbl table

strSQL = "Select * From AttendTbl"
rs.Open strSQL, CurrentProject.Connection

' you have to set the value to the Class ID you want to add Attendees to
' I presume this value can be found on the form somewhere
' I am assuming here that it is in a control call ClsID

lngClsID = ClsID

' enumerate each item selected from the list

For Each varPtr In ListBox.ItemsSelected
rs.AddNew ' add a new record to AttendTbl
lngEeID = ListBox.Columns(N, varPtr) ' where N is the column
number corresponding to the EeID
rs!ClsID = lngClsID ' set the values for the new record
rs!EeID = lngEeID
rs.Update ' save the new record
Next

rs.Close
End Sub

 

[Rod Scoullar]

Gary,

Which version of Access are you using? The syntax is slightly different for
Access97 and Access2000/XP.

I'll assume the later versions.

Put a Command Button on your form. Name it cmdLinkAttendees.

Select the button, open the Properties dialog and select the Event tab.

Click in the box opposite On Click, then click on the elipsis (...) at the
end of the line.

Select Code Builder from the dialog and click OK

You should now be in the VB edit screen and see

Private Sub cmdLinkAttendees_Click()

End Sub

The code goes between those lines.

All the code should be indented except for the first and last line defining
the Sub. If anything else is not indented it has been wrapped by the
newsgroup reader and should be added to the end of the previous line.

Sorry if I have been too verbose.

Rod Scoullar


Private Sub cmdLinkAttendees_Click()
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim varPtr As Variant
Dim lngClsID As Long
Dim lngEeID As Long

' open a recordset which contains records from the AttendTbl table

strSQL = "Select * From AttendTbl"
rs.Open strSQL, CurrentProject.Connection

' you have to set the value to the Class ID you want to add Attendees to
' I presume this value can be found on the form somewhere
' I am assuming here that it is in a control call ClsID

lngClsID = ClsID

' enumerate each item selected from the list

For Each varPtr In ListBox.ItemsSelected
rs.AddNew ' add a new record to AttendTbl
lngEeID = ListBox.Columns(N, varPtr) ' where N is the column
number corresponding to the EeID
rs!ClsID = lngClsID ' set the values for the new record
rs!EeID = lngEeID
rs.Update ' save the new record
Next

rs.Close
End Sub

 

[Gary]

Sorry to continue this thread, but I'm getting an error
message with the code in question: "Object or provider is
not capable of performing the requested operation." This
is using Access2000, although Access2002 has a similar
error (worded slightly differently).

Best I can tell the error is being generated by the
line "rs.Open strSQL, CurrentProject.Connection"

I will post code in question below. Thank you again for
your continued support.

- - - - START CODE - - - -
Private Sub CmdDone_Click()
On Error GoTo Err_CloseFrm_Click
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim varPtr As Variant
Dim lngClsID As Long
Dim lngEeID As Long

' open a recordset which contains records from the
AttenTbl table

strSQL = "Select * From AttenTbl"
rs.Open strSQL, CurrentProject.Connection

' set the value to the Class ID to add Attendees to

lngClsID = ClsID

' enumerate each item selected from the list

For Each varPtr In SelEeID.ItemsSelected
rs.AddNew ' add a new record to AttenTbl
lngEeID = SelEeID.Column(0, varPtr) ' where 0
is the column number corresponding to the EeID
rs!AttenClsID = lngClsID ' set the values for
the new record
rs!AttenEeID = lngEeID
rs.Update ' save the new record
Next

rs.Close

'DoCmd.Close

Exit_CmdDone_Click:
Exit Sub

Err_CloseFrm_Click:
MsgBox Err.Description
Resume Exit_CmdDone_Click

End Sub