Multiple Select Listboxes saved to Table

[jpkent]

Hello - and I may as well get this out of the way, I'm painfully new to Access, sorry - but I'm taking on what I believe to be a simple project, and one of the problems I've run into is that I can't get any of the items in a multi selection listbox into a table.

Now, I want to back up a step because I assume that I'm doing everything as simply as possible right now: I have two tables which I am crossing using a query. The first table is actual data, whereas the second has data to be looked up. I wanted the user to be able to show all the data available for more than one row, so as far as I can understand all the rows I want shown need to be defined in that second table. I have a listbox that looks up values in the first table, and the user should be able to select values in that list to be shown (stored into the second table).

Assuming this is the simplest thing to do so far, I found a post before (thread702-541606 that covers this, but I couldn't get the code to work (the error I received was "Syntax error in INSERT INTO statement.&quot Aside from tweaking the format of the code here and there as the thread discussed, I tried looking up a general format for the insert into statement to no avail. What's the problem - is this the best way to do this - is there another way?

 

[Rolliee]

VB is easier to write, in my opinion, and certainly easier to debug that INSERT statements.

rollie@bwsys.net

 

[nat1967]

Hi blackfire,

Another way besides Insert SQL is to use VB to open the table and insert the data from the list box.


'****example*****
Private Sub cmdSave_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

Set rs = db.OpenRecordset("listexample2&quot 'set reference to table where data is going

With rs

.AddNew 'adds new line in table
.Fields("listexample&quot = Me!List2.RowSource 'transfer data from listbox to new row in table
.Update 'updates the table

End With

set rs = nothing
set db = nothing

End Sub
'****end example*****


HTH


Have A Great Day!!!,

Nathan
Senior Test Lead

 

[nat1967]

I left out the Insert SQL example:
'****example*****
Private Sub cmdSave_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb

Call db.Execute("Insert into listexample2(listexample) values ('" & Me!List2.RowSource & "');", dbFailOnError)


End Sub
'****end example*****


Have A Great Day!!!,

Nathan
Senior Test Lead

 

[kire1971]

Sorry to intrude on this thread but i'm also having a problem with a multiselect listbox. Instead of using the listbox to add a new record to a table, is there a way to have it update the current record being edited in the form? I've only been able to get it to add a new record or update the first record, never to update the current record.

Any help would be appreciated. Thanks.