Forcing table to save using vba

0
votes

So I have a bit weird problem. I need to insert values into freshly created multivalue lookup field. The only problem is that such empty column doesn't have its .Value parameter. I've noticed that opening the table, changing anything which then will force the pop-up "You need to save this table, blah blah...". And after saving it, even if it's still empty (sic!) the property .Value in a field appears.

I need to do all this using VBA, I tried doing

DoCmd.Open("tablename") DoCmd.Save("tablename")

to force saving it after creation, but VBA complains about syntax, even though every example that I found was using exactly those lines of code.

Maybe there's another way to force appearing .Value rather than trying to force saving table? I'm clueless ATM.

----

EDIT:
I managed to solve it. Instead of DAO VBA instructions, I just opened the table in the design view, saved it, closed the table.

The (in this case) huge difference was opening it in the design view instead of simple datasheet view.

Saving the table in those two views produces two different results (with or w/o the .Value property).

1
ms-accessvbams-access-2007
it's generally good practice to avoid using multivalue fields. use a JOIN table instead. – serakfalcon
The thing is that I cannot avoid it. Those fields are in a linked list in SharePoint so I am unable to change the structure – pkrysiak

1 Answers

0
votes

I am not sure if your code is currently using DAO; however, it should be. I found a user that had a similar problem to you and another user was able to assist them: Creating a Multi Value Field through DAO works, but Access has problem saving it.

The difference that I would image for you is that you would want to modify:

.Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
.Properties.Append .CreateProperty("RowSource", dbText, _
                                       "SELECT [Last Name] FROM Employees")

To

.Properties.Append .CreateProperty("RowSourceType", dbText, "Value List")
.Properties.Append .CreateProperty("RowSource", dbText, _
                                       "Your;Values;Here")