Using Access to update a Sharepoint "Choice" field

[CedarTree]

Hello - I have a choice field in SP, that has as one option this "---". In fact, that is the default value in SP when adding a new record, although (perhaps b/c of the format), it doesn't work so I have to go in and choose "---" as the choice for new records. I figured I would try to automate/update this in Access by linking to the SP table. Note that I can update all other fields, and that manually updating to "---" works fine. But when I go to use DAO recordset VBA to update, I get an error whether I use [ChoiceField] or ChoiceField.Value in my VBA. Any suggestions? Thanks!

EDIT - to avoid the weird "---" option, I added an "N/A" option, and getting error 3265 in VBA.

 

[theDBguy]

Can you post the relevant code for review?

 

[CedarTree]

        sql = "SELECT * FROM Table"
        sql = sql + " WHERE ChoiceField.Value = 'N/A' OR ChoiceField Is Null"
        Set rst = CurrentDb.OpenRecordset(sql)
        If Not rst.EOF Then
            rst.MoveFirst
            While Not rst.EOF
                rst.Edit
                rst("ChoiceField.Value") = "N/A"
                rst.Update
                rst.MoveNext
            Wend
        End If

 

[theDBguy]

        sql = "SELECT * FROM Table"
        sql = sql + " WHERE ChoiceField.Value = 'N/A' OR ChoiceField Is Null"
        Set rst = CurrentDb.OpenRecordset(sql)
        If Not rst.EOF Then
            rst.MoveFirst
            While Not rst.EOF
                rst.Edit
                rst("ChoiceField.Value") = "N/A"
                rst.Update
                rst.MoveNext
            Wend
        End If

Not sure if that's a typo, but maybe try?

rst("ChoiceField").Value = "N/A"

 

[Gasman]

And 3265 is?

 

[MajP]

Not sure why you need to do code to do this. Simply build an update query. Obviously build a select query first and see if you return the null and NA fields. Then modify to an update query. I do all my bulk updates in the query builder.

 

[CedarTree]

Right - I started with an update query and could not get it to work. So I thought maybe coding would give me more control/debugging capability.

 

[CedarTree]

Not sure if that's a typo, but maybe try?

rst("ChoiceField").Value = "N/A"

I'll give it a shot...

 

[CedarTree]

I'll give it a shot...

Nope - I get a Method value of field error.

 

[theDBguy]

Nope - I get a Method value of field error.

Try removing Value then and see what happens.

rst("ChoiceField") = "N/A"

 

[MajP]

Error 3265 is Item not found in collection. So your field is not named what you think.

 

[MajP]

Also is this really a simple Choice field or is this really a Lookup field?

 

[CedarTree]

In SP, it's a choice field. And I just double checked the field names (in SP). Still no luck so far.

 

[MajP]

Is the field based on a default field like "Title" or was it built from scratch? Can you screen shot the properties page on that field (Settings -> Edit Column)?

 

[CedarTree]

Built from scratch. Definitely a choice field. A little hard to share due to proprietary data, etc.

 

[MajP]

If it was me, I think I would create a new column. Do an update query to copy existing values into the new column. Delete the old column. Rename the new column to the old column.

 

[CedarTree]

Thanks - will try when I have some more time later this week. Thanks!

 

[MajP]

If you cannot populate the new column based on the old one, then that would prove there is more going on. If after you create the new column if you cannot update that column that too will show that there is more going on.