Adding selected listbox values to a multivalue field via SQL statement

Question

*Edit in: The problem was that the SQL statement had to add the Areas to the MVF one at a time. The second problem was me forgetting to execute the SQL within the loop.

I have a problem in Access 2007 with some SQL statements involving a multivalue field called "area". The idea is that any one User has several relevant areas they belong to. The different areas are displayed in a multiselection listbox and inserted with the following code. After a bit of research it seemed that passing multivalue fields requires an insert. [Though I'm unsure of this]

My first attempt looked like this

For i = 0 To Me.boxArea.ListCount - 1
        If Me.boxArea.Selected(i) Then
            sqlString = sqlString & Me.boxArea.Column(boundColumnZeroBasedIndex, i) & ","
        End If
Next i

Which resulted in an error that states: "The Number of values and destination fields are not the same." The SQL string ends up as the following which made me realize it was behaving like a regular insert and the multiple values were being treated like different fields.

"INSERT INTO Users ([area].[Value]) VALUES(G1 ,G2,G3,) WHERE badgeNumber = '404';"

For my next attempt, I thought it might just require passing the values through one at a time.

If Me.boxArea.Selected(i) Then
            sqlString = "INSERT INTO Users ([area].[Value]) VALUES('" & Me.boxArea.Column(boundColumnZeroBasedIndex, i) & "') WHERE badgeNumber = '" + txtBadgeNumber + "';"
        End If
Next i

This goes through without errors, but it only includes the very last value rather than the entire string. So G3 is all that appears.

Only One Value

So now I'm confused as to how insert multiple values into a MVF through SQL within Access. Everything I've tried so far creates an error or unintended results. Any help you can provide would be greatly appreciated. Thanks!

-EDITED IN INFO FOR COMMENTS

Here's a debug screen showing the value of boxArea

The only other SQL Statement running is the SQL statement to create the User. I believe there's nothing wrong with it, but included as a reference.

sqlStr = "INSERT INTO Users ( badgeNumber, firstName, lastName, accessLevelID, department, email, phone, mobile, fax, pager, title, displayName, company )VALUES ('" + txtBadgeNumber + "', '" + txtFirstName + "', '" + txtLastName + "', 1, 'BE23515', '" + txtEmail + "', '" + txtPhone + "', '" + txtMobile + "', '" + txtFax + "', '" + txtPager + "', '" + txtTitle + "', '" + txtDisplay + "', 'XXXX');"
CurrentDb.Execute sqlStr, dbFailOnError

Jay Taplin · Accepted Answer · 2013-09-09 17:20:11Z

2

Your second attempt looks like it was close. Instead of using an update statement, use an insert. For instance:

sqlString = "INSERT INTO Users ([area].[Value]) VALUES ('" & Me.boxArea.Column(boundColumnZeroBasedIndex, i) & "') WHERE badgeNumber = '" + txtBadgeNumber + "';"

For reference, see this page on Microsoft's site:

http://office.microsoft.com/en-us/access-help/using-multivalued-fields-in-queries-HA010149297.aspx

answered Sep 9, 2013 at 17:20

Jay Taplin

5705 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Duffie Over a year ago

Wow didn't notice that I had an "UPDATE INTO" there. Corrected that, but I'm still having the same problem. Only G3, the last value passed through, is showing up in the table. Rather than values G1, G2, G3. Added in an additional Image

Jay Taplin Over a year ago

What value is being returned from "Me.boxArea.Column(boundColumnZeroBasedIndex, i)", or better yet, can you provide the multiple SQL statements that you are issuing against the database?

Jay Taplin Over a year ago

I just noticed that you said "G3" is the only value being stored; you'll need to insert the values one at a time, i.e. INSERT...G1, then INSERT...G2, then INSERT...G3.

Duffie Over a year ago

That's what I believe as well. However, in my second attempt the loop should be inserting them one at a time. Watching the debugger, I can confirm that the SQL is inserting G1, G2, and G3 on different passes through the loop. I'm unsure why only G3 is appearing in the table.

Duffie Over a year ago

I am new to MVF, should the ([area].[Value]) have an [i] in there somewhere? I am at a loss why it is overwriting the values rather than adding to it.

|

Collectives™ on Stack Overflow

Adding selected listbox values to a multivalue field via SQL statement

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related