MS Access SharePoint multi value list modification

[maxamis4]

So I am creating a UI for SharePoint via MS Access. Typically I advise my clients not to use multi value lists for junctions and it typically stays that way. However in this case the SharePoint UI will be used on some limited basis which requires the junction to be present and visible. Enter the multivalue list option.

I am have trouble with my multi-value delete query. With the multi value list I see that you have to treat the value list almost like an independent query. The problem is I have two criteria to filter by. The parent record and then the multi value ID. When executing the query i get an error that indicates I can not use the parent ID in a multi-value query. I tried to use a nexted select/delete statement with no luck. Went back to the basics and have been stock since.

DELETE [Regional Application Mapping].[Production Servers].Value, [Regional Application Mapping].ID
FROM [Regional Application Mapping]
WHERE ((([Regional Application Mapping].[Production Servers].Value)=707) AND (([Regional Application Mapping].ID)=241));

Regional Application Mapping].ID)=241 'This is the parent record filter. I want to identify the App ID first to narrow my search on the value that I plan on deleting. mind you the servers value can be used with other parent records as an option. its technically a many to many relationship but 1 to 1 on the record level. This filter is the one that fails

[maxamis4]

So I found a very ugly work around and I am building the function to do it but it is killing me to think that I have to write an entire module to perform this action. Anyone have any thoughts:

Work aournd

Function Delete_Multi_value_List(AppID, SrvID)
'MULTIVALUE LISTS http://stackoverflow.com/questions/6018172/update-a-multi-valued-field-in-access

'READS ALL SHAREPOINT MULTIVALUE LISTS

   Dim db As Database
   Dim rs As Recordset
   Dim childRS As Recordset
   Dim myInt As Integer
   Dim myApp As String
   
   
   Set db = CurrentDb()
   
   mySql = "SELECT [Regional Application Mapping].ID, [Regional Application Mapping].*, [Regional Application Mapping].ID FROM [Regional Application Mapping]" _
                            & " WHERE ((([Regional Application Mapping].ID)=" & AppID & "));"
    
   ' Open a Recordset for the Tasks table.
   Set rs = db.OpenRecordset(mySql)
   rs.MoveFirst
    
   Do Until rs.EOF
         
      ' Open a Recordset for the multivalued field.
      Set childRS = rs![Production Servers].Value 'Production Systems
      'Set childRS = rs![Non-Production Servers].Value 'Non Production Systems
      
      myApp = rs![Application Name]
      myInt = DLookup("[ID]", "Regional Application Mapping", "[Application Name] = '" & myApp & "'")
      
      If myInt = AppID Then
         ' Exit the loop if the multivalued field contains no records.
         Do Until childRS.EOF
             childRS.MoveFirst
                    
             ' Loop through the records in the child recordset.
             Do Until childRS.EOF
              
                  If childRS!Value.Value = SrvID Then
                  
                    childRS.Delete
                  
                  End If
                  
                               
                 childRS.MoveNext
             Loop
         Loop
         
      End If
      
      rs.MoveNext
   Loop
End Function

Still very sloppy but I will post the cleaned up version.  If anyone knows any other way let me know.

[Rabbit]

I don't know much about Sharepoint but in the first post, if you're trying to delete the multi-value, then why do you have the parent id in the delete clause as well? I understand that it's in the where clause to filter it, but I don't understand why it would also be in the delete clause unless you meant to delete the parent record, in which case you wouldn't have the multi-value in there.

[zmbd]

You might try replacing your multi-value field with a lookup control or base it on a table/query. This way you need only edit the underlying data table to the control.
MicroSoft - Guide to multivalued fields ACC2007
-- about midway (maybe less) down into this document may be helpful there, and then there's a link to modifying etc... I'm sure this also applies to/works with ACC2010.

[maxamis4]

Rabbit,

You need the parent ID to find the primary recordset. From there loop through the multivalue list and delete the records needed.

Example:
Honda, Accord, 2011 would be the parent recordset. The multivalue list would incorporate features like leather interior, GPS, turn sensors, backup camera, etc...

Now the real way to do it is as Zmbd suggested. The problem is my client still wants to keep the front end options in Sharepoint and without the multivalue option he can't select options that relate to another list.

[Rabbit]

@maxamis, I understand you need the parent id to find the correct record, that's why you have the where clause. I don't understand why you would tell it to delete the parent record though in the delete clause.

[zmbd]

maxamis4,
I know that this may sound stupid; however, I do have a good reason for asking: how was the multi-value field created to begin with?
Basically, what is the record source for the field?

[edit] How to: Manipulate Multivalued Fields With DAO - Office 2010 If you can open the multivalue as a record set, then you should be able to find, edit, or delete the value from the list like any other record set... I am grapsing at the straws here for you :)[/edit]