Refining multi select list box code

[Guest]

I posted yesterday with a query on my code which Douglas Steel (Thanks)
sorted for me. the code is linked to a button on a form (Events1) will
insert multiselect items from a listbox (ItemsSelected) into a table (this
part works fine). the form that the listbox is on has two other fields that
I would also like to insert into the table. the fields are EventID (number)
and CourseDate (ComboBox bound column is the one I want to insert into table)
I dont know how to refer to the fields in the code.

I hope someone can help, thanks in advance

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

 

[Van T. Dinh]

I think you are actually talking about Controls on the Form. A Form doesn't
have Fields. A Form has a DataSource (a Table or a Query / SQL String) and
the DataSource has Fields (from base Tables).

Those visible / graphical elements on the Forms such as TextBoxes,
ComboBoxes, ListBoxes are Control and if you want to get the value in the
Control, you simply use the ControlName. The multi-select ListBox is the
odd one out that always has Null Value and that's why you need the special
code posted.

For example, you could modify your code to:

db.Execute "INSERT INTO Sessions ([SessionID], [EventID]) " &
" VALUES(""" & ctl.ItemData(varItem) & "", " & _
Me.txtEventID & ")", dbFailOnError

I use the normal naming convention using the prefix "txt" to indicate a
TextBox Control.

 

[Van T. Dinh]

Just copy:

db.Execute "INSERT INTO Sessions ([SessionID], [EventID]) " & _
" VALUES(""" & ctl.ItemData(varItem) & "", " & _
Me.txtEventID & ")", dbFailOnError

from the post and paste it in your code.

You do need the closing parenthesis and the double-quote after dfFailOnError
is wrong in what you posted.

 

[Douglas J. Steele]

There appears to be a slight typo there. (too few double quotes near the end
of the 2nd line:

db.Execute "INSERT INTO Sessions ([SessionID], [EventID]) " & _
" VALUES(""" & ctl.ItemData(varItem) & """, " & _
Me.txtEventID & ")", dbFailOnError



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

thanks again for your reply, I realy appreciate this help. I have been
muddling through this since your post.

I have pasted it directly into my code (see below) but I get the error
Compile Error: Expected end of statement it *highlights* the following:

*Me*.txtEventID & ")", dbFailOnError

I am stuck, hope you help

thanks Phil

Just copy:

db.Execute "INSERT INTO Sessions ([SessionID], [EventID]) " & _
" VALUES(""" & ctl.ItemData(varItem) & "", " & _
Me.txtEventID & ")", dbFailOnError

from the post and paste it in your code.

You do need the closing parenthesis and the double-quote after
dfFailOnError
is wrong in what you posted.

--
HTH
Van T. Dinh
MVP (Access)

Hi Van T. Dinh


Thank you for your reply I did mean controls, when it comes to code I
see
it
as a necessary evil, sometimes I understand what I am doing other times
i
feel I am out of my depth.

I pasted your code, and came up with a compile error expected end of
statement, I deleted the ) that was highlighted which gave me:

db.Execute "INSERT INTO Sessions ([SessionID], [EventID]) " & "
VALUES("""
&
ctl.ItemData(varItem) & "", " & _ Me.txtEventID & "", dbFailOnError"

the error I got says:

" Runtime error 3421 data type conversion error"

the control EventID refers to a field which is an autonumber and PK
does
this make a difference, or is it something else completely.

thanks again for your help

Phil

 

[Van T. Dinh]

Thanks, Doug.

I looked at it quite a few times and still missed one doubel-quote.