append multiselect to table

[Maarkr]

I've setup a many students to many classes db. My junction has JctID,
studentID, ClassID, and ClassDate fields. I can use a form to enter records
one by one, but I also want to multiselect students that take a class on
particular date (I may have 50 students take a particular class on a date,
and may have 10 different classes to update to the db). I made a form with a
multiselect list box showing all students (and IDs), a combobox showing all
classes (classIDs), and a class date. I've seen examples on this site using
both rs and SQL methods to get info, but I need help with the best method to
loop thru or get all selected studentIDs in the list box, the one selected
Class ID, and one Class date from the form to append to my junction table.
as always, thanks...

 

[Ken Snell \(MVP\)]

Something like this, perhaps -- looping through the students listbox:

Dim dbs As DAO.Database
Dim varItemSel As Variant
Dim strSQL As String
On Error GoTo Err_Part
Set dbs = CurrentDb
For Each varItemSel In Me.NameOfStudentListbox.ItemsSelected
strSQL = "INSERT INTO NameOfJunctionTable " & _
(studentID, ClassID, ClassDate) VALUES ( " & _
Me.NameOfStudentListbox.ItemData(varItemSel) & ", " & _
Me.NameOfClassIDComboBox.Value & ", " & _
Format(Me.NameOfClassDateTextbox, "\#mm\/dd\/yyyy\#") _
& ");"
dbs.Execute strSQL, dbFailOnError
Next varItemSel

Exit_Part:
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Part:
MsgBox "Error occurred with student " & _
Me.NameOfStudentListbox.ItemData(varItemSel) & " -- error #" & _
Err.Number & ": " & Err.Description
Resume Exit_Part:

 

[Maarkr]

thanks... I was going the route of:
If Me.List0.ItemsSelected.Count = 0 Then Exit Sub
For Each varItem In Me.List0.ItemsSelected
lngMbr = Me.List0.Column(0, varItem)
lngClassID = Me.List2.Value
strClassDt = Me.Text4.Value
Debug.Print lngMbr, lngClassID, strClassDt...

then using the DoCmd.RunSQL ... couldn't get it to work.

I tried your code and got the same error code 3134 as my trials...darn...
the debug was showing up looking good with the strings and SQL code, so I
finally pasted it in a new query... which wouldn't run... turned out that the
problem was using the original imported field name from Excel, which was
Mbr_ID#, and the query wouldn't execute with the # in the field name! Don't
you hate it! Thanks Ken, I did keep your neat error checking code for future
use and never would have included the Exit_Part: without your help, so my
code is good. On to the next challenge.

 

[Ken Snell \(MVP\)]

You can use a field name containing a # (or other nonroutine characters) if
you enclose the fieldname with [ ] characters:

SELECT [Mbr_ID#] FROM Tablename;

 

[Maarkr]

forgot that, but it's working anyway... if you're still around, my next
problem is that I can update this training, but how should I deal with
multiple training dates? A person requires a training event sometimes
annually, so instead of using an append query, I need to check if a person
and class already exists and delete that record ,ie run a delete query first,
or will it be easier to run a duplicates/delete query after the append? I'll
play with that for a while unless someone answers with a good idea.

You can use a field name containing a # (or other nonroutine characters) if
you enclose the fieldname with [ ] characters:

SELECT [Mbr_ID#] FROM Tablename;

--

Ken Snell
<MS ACCESS MVP>

thanks... I was going the route of:
If Me.List0.ItemsSelected.Count = 0 Then Exit Sub
For Each varItem In Me.List0.ItemsSelected
lngMbr = Me.List0.Column(0, varItem)
lngClassID = Me.List2.Value
strClassDt = Me.Text4.Value
Debug.Print lngMbr, lngClassID, strClassDt...

then using the DoCmd.RunSQL ... couldn't get it to work.

I tried your code and got the same error code 3134 as my trials...darn...
the debug was showing up looking good with the strings and SQL code, so I
finally pasted it in a new query... which wouldn't run... turned out that
the
problem was using the original imported field name from Excel, which was
Mbr_ID#, and the query wouldn't execute with the # in the field name!
Don't
you hate it! Thanks Ken, I did keep your neat error checking code for
future
use and never would have included the Exit_Part: without your help, so my
code is good. On to the next challenge.

 

[Ken Snell \(MVP\)]

Still around, but am out of town right now, and work doesn't allow me enough
time tonite to give a full reply. Will post as soon as I have time to give
you a good answer. Apologize in advance for the delay.

--

Ken Snell
<MS ACCESS MVP>

forgot that, but it's working anyway... if you're still around, my next
problem is that I can update this training, but how should I deal with
multiple training dates? A person requires a training event sometimes
annually, so instead of using an append query, I need to check if a person
and class already exists and delete that record ,ie run a delete query
first,
or will it be easier to run a duplicates/delete query after the append?
I'll
play with that for a while unless someone answers with a good idea.

You can use a field name containing a # (or other nonroutine characters)
if
you enclose the fieldname with [ ] characters:

SELECT [Mbr_ID#] FROM Tablename;

--

Ken Snell
<MS ACCESS MVP>

thanks... I was going the route of:
If Me.List0.ItemsSelected.Count = 0 Then Exit Sub
For Each varItem In Me.List0.ItemsSelected
lngMbr = Me.List0.Column(0, varItem)
lngClassID = Me.List2.Value
strClassDt = Me.Text4.Value
Debug.Print lngMbr, lngClassID, strClassDt...

then using the DoCmd.RunSQL ... couldn't get it to work.

I tried your code and got the same error code 3134 as my
trials...darn...
the debug was showing up looking good with the strings and SQL code, so
I
finally pasted it in a new query... which wouldn't run... turned out
that
the
problem was using the original imported field name from Excel, which
was
Mbr_ID#, and the query wouldn't execute with the # in the field name!
Don't
you hate it! Thanks Ken, I did keep your neat error checking code for
future
use and never would have included the Exit_Part: without your help, so
my
code is good. On to the next challenge.

:

Something like this, perhaps -- looping through the students listbox:

Dim dbs As DAO.Database
Dim varItemSel As Variant
Dim strSQL As String
On Error GoTo Err_Part
Set dbs = CurrentDb
For Each varItemSel In Me.NameOfStudentListbox.ItemsSelected
strSQL = "INSERT INTO NameOfJunctionTable " & _
(studentID, ClassID, ClassDate) VALUES ( " & _
Me.NameOfStudentListbox.ItemData(varItemSel) & ", " & _
Me.NameOfClassIDComboBox.Value & ", " & _
Format(Me.NameOfClassDateTextbox, "\#mm\/dd\/yyyy\#") _
& ");"
dbs.Execute strSQL, dbFailOnError
Next varItemSel

Exit_Part:
dbs.Close
Set dbs = Nothing
Exit Sub

Err_Part:
MsgBox "Error occurred with student " & _
Me.NameOfStudentListbox.ItemData(varItemSel) & " -- error #" & _
Err.Number & ": " & Err.Description
Resume Exit_Part:


--

Ken Snell
<MS ACCESS MVP>




I've setup a many students to many classes db. My junction has
JctID,
studentID, ClassID, and ClassDate fields. I can use a form to enter
records
one by one, but I also want to multiselect students that take a
class
on
particular date (I may have 50 students take a particular class on a
date,
and may have 10 different classes to update to the db). I made a
form
with a
multiselect list box showing all students (and IDs), a combobox
showing
all
classes (classIDs), and a class date. I've seen examples on this
site
using
both rs and SQL methods to get info, but I need help with the best
method
to
loop thru or get all selected studentIDs in the list box, the one
selected
Class ID, and one Class date from the form to append to my junction
table.
as always, thanks...

 

[Ken Snell \(MVP\)]

I'm not understanding why you need to delete data? Is there a limitation for
your database that only one training date can be stored for each person?
Your database cannot store multiple dates for each person?

Please provide more details so that we can better understand the situation
and requirements.

 

[Maarkr]

I guess I really never thought of it til you asked the question... I went
ahead and used John V's code to delete the first duplicate record in the db
when the name and training event match. Querying the junction table for the
most recent event when dup's exist would be a good alternative. I'm building
several db's based on the same or similar training concepts so I'll do that
next time. There's only about 4000 records in the junction table so there's
room for more, but if I expand the project, it may have up to 20000 records,
so dups may not be desireable. Thanks for your advice. Since you mentioned
it... I haven't searched the site yet for some query language to get me going

 

[Ken Snell \(MVP\)]

Here's some generic SQL statement that you can use as a starting point:

SELECT * FROM TableName
WHERE TableName.DateField =
(SELECT Max(T.DateField) AS MaxDF
FROM TableName AS T
WHERE T.PersonName = TableName.PersonName
AND T.TrainingClass = TableName.TrainingClass
GROUP BY T.PersonName, T.TrainingClass);
--

Ken Snell
<MS ACCESS MVP>