Re: Problem with multiselect list box code

Set rst = db.OpenRecordset("Sessions")

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Phil" <Phil@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A650AE26-EBD3-4B96-9A9D-51DB6DF5D49E@xxxxxxxxxxxxxxxx
Hi Douglas

Thank you for your speedy reply, I pasted your code (2nd example) into my
button it still does not work I get the same error and the following line
is
highlighted. It did not trap an error (I dont think).

Set rst = db.OpenRecordset(Sessions)

thanks also for trying to answer my second question, the fields are not
part
of the listbox but seperate fields on the form EventID (number) &
CourseDate
(comboBox with bound value being the value I want to trap).

My understanding of access has grown over the last two years and I can do
rudementary coding but lack the knowledge for real understanding of what
is
happening.

I have posted the code again below just in case I have made an error in
pasting

thanks

Phil

Dim frm As Form, ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant
Dim strTemp As String
If Me.TimetableList.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one Session.")
Exit Sub
End If
Set db = CurrentDb
Set frm = Me 'sets form to active form
Set ctl = frm!TimetableList 'Name of your listbox
Set rst = db.OpenRecordset(Sessions)
For Each varItem In ctl.ItemsSelected
db.Execute "INSERT INTO Sessions ([SessionID]) " & "VALUES(""" &
ctl.ItemData(varItem) & """)", dbFailOnError
Next varItem




"Douglas J. Steele" wrote:

Your SQL statement is incorrect.

Try:

db.Execute "INSERT INTO Sessions ([SessionsID]) " & _
"VALUES(""" & ctl.ItemData(varItem) & """)"

I'd also recommend using dbFailOnError as the optional second parameter
so
that you'll get a trappable error if something goes wrong:

db.Execute "INSERT INTO Sessions ([SessionsID]) " & _
"VALUES(""" & ctl.ItemData(varItem) & """)", dbFailOnError

You'll definitely want to add the additional fields to that INSERT INTO
statement, though: if you don't, you'll have no way of uniquely referring
to
the rows you just inserted! That means something like:

db.Execute "INSERT INTO Sessions " & _
"(SessionsID, EventID, CourseDate) " & _
"VALUES (" & _
""" & ctl.ItemData(varItem) & """, " & _
ctl.Column(2, varItem) & ", " & _
Format(ctl.Column(3, varItem), "\#mm\/dd\/yyyy\#") & ")", _
dbFailOnError

In what I've suggested above, I'm assuming that the EventID and
CourseDate
are available as the 3rd and 4th column respectively in the listbox, and
that EventID is numeric. If that's not the case, and you can't figure it
out
on your own, post back with more details.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Phil" <Phil@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DD5C01B5-43A1-4782-9310-595EE0502E10@xxxxxxxxxxxxxxxx
I hope someone can help

I am trying to populate a table with fields from multiselect listbox:

Control is called TimetableList I would like it to update the field
SessionsID in the sessions table

I get a runtime error (3078) the jet datbase engine cannot find table
or
query (as far as I can tell they are correct.)

Also on the form I have two other field EventID, & CourseDate I would
like
these to be completed in the Session table as well but was intent on
getting
the listbox update working first

the code I have:

Dim frm As Form, ctl As Control
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varItem As Variant
Dim strTemp As String
If Me.TimetableList.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one Session.")
Exit Sub
End If Definitely works up to here
Set db = CurrentDb
Set frm = Me 'sets form to active form
Set ctl = frm!TimetableList 'Name of your listbox
Set rst = db.OpenRecordset(Sessions)
For Each varItem In ctl.ItemsSelected
db.Execute "INSERT INTO me.Sessions.[SessionsID] VALUES(""" &
ctl.ItemData(varItem) & """)"
Next varItem

I hope you can help

thanks

Phil





.

Quantcast