Move items from listbox to a table in a database ?

[adamtate94]

Hi Guys, I have a listbox on my form in access which holds strings,
how do I insert these items into an SQL Database (Table)?
Thanks

 

[pbaldy]

One way:

http://www.baldyweb.com/MultiselectAppend.htm

 

[adamtate94]

Hi Paul thanks again for the reply

I have tried implementing the code here it is :

Private Sub Command185_Click()
Dim strSQL As String
strSQL = "INSERT INTO dbo.CourseElementsDefault (CourseID) VALUES (Me.CourseID)"

Set ctl = Me.lstElements
For Each varItem In ctl.ItemSelected
DoCmd.RunSQL strSQL
rs.AddNew
rs!ElementID = ctl.ItemData(varItem)
rs.Update
Next varItem
End Sub


I get an error "object doesn't support this property or method".

Thanks in advance,
Adam

 

[pbaldy]

Do you really want to do 2 separate inserts? The variable needs to be concatenated into the SQL if so, and I doubt you want to repeat the same insert. You probably don't want the "dbo." either. That would be within SQL Server.

 

[adamtate94]

My table that I am trying to insert data into is like this:

CourseElementID(Auto) | CourseID | Element ID
1. 1. 4
2. 1. 2
... ... ...

The company I work for still uses Access 2003, there is an object error being thrown up when declaring rs , as ADO also.

 

[pbaldy]

Why have you added the bit executing SQL? My sample does what you need. Plus you never declare or set the recordset.

 

[adamtate94]

Each time I put a new module ID in from the list it needs to add another courseID to each row too, do I put the INSERT statement in the for each?
Thanks for the help

 

[pbaldy]

Did you look at the sample db? This line would be for your courseID:

rs!OtherValue = Me.txtOtherValue

 

[adamtate94]

I will give it a try now, I understand what you mean now

Thanks a lot for the help

 

[pbaldy]

No problem.

 

[adamtate94]

EDIT :
I have this error now:

 

[pbaldy]

If you hit debug, what line is highlighted? Can you attach the db here?