Creating a Multi Value Field through DAO works, but Access has problem saving it

[Michael R]

Hi.
I'm using Access 2010 x86
I'm trying to create a Multi Value Field in a table using DAO, with the following code:

Sub Test1()
Dim db As dao.Database, tdf As dao.TableDef, fld As Field
Set db = CurrentDb
  Set tdf = db.TableDefs("Contacts")
  With tdf
 
    Set fld = .CreateField("New", dbText, 255)
 
    .Fields.Append fld
 
 
    fld.Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
    fld.Properties.Append ..CreateProperty("AllowMultipleValues", dbBoolean, True)
    Set fld = Nothing
 
  End With
  Set tdf = Nothing
Set db = Nothing
 
End Sub

It works well, but the only thing is that when later I open the table, I get the message:

"You have changed the "New: lookup column to store multiple values. You will not be able to undo this change once you save the table", each time I open the table, no matter if I save it or not.

Do you know how can I bypass/avoid this message?

[mcupito]

FYI, did you notice the 2 "." in your code? Is that a mistake?

fld.Properties.Append ..CreateProperty

On the same line, check this out:

expression .CreateProperty(Name, Type, Value, DDL)

If DDL is True, users can't change or delete this Property object unless they have dbSecWriteDef permission.

This option is now ruled out.

[Michael R]

It's a typo here, in the code I got it right.

[mcupito]

Michael R, see my edit.

[Michael R]

I made the DDL false, and still get the same message after entering the altered table.

[Michael R]

this is my current code, after your suggestion:

Sub Test1()
Dim db As dao.Database, tdf As dao.TableDef, fld As Field
Set db = CurrentDb
  Set tdf = db.TableDefs("Contacts")
  With tdf
 
    Set fld = .CreateField("New3", dbText, 255)
 
    .Fields.Append fld
 
 
    fld.Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
    fld.Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
    fld.Properties.Append .CreateProperty("RowSource", dbText, "SELECT Slownik.Sprzedawca FROM Slownik WHERE (((Slownik.Sprzedawca) Is Not Null));")
    fld.Properties.Append .CreateProperty("BoundColumn", dbInteger, 1)
    fld.Properties.Append .CreateProperty("ColumnCount", dbInteger, 1)
    fld.Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
    fld.Properties.Append .CreateProperty("ListRows", dbInteger, 16)
    fld.Properties.Append .CreateProperty("LimitToList", dbBoolean, True)
    fld.Properties.Append .CreateProperty("AllowValueListEdits", dbBoolean, False)
    fld.Properties.Append .CreateProperty("ShowOnlyRowSourceValues", dbBoolean, False)
    fld.Properties.Append .CreateProperty("AllowMultipleValues", dbBoolean, True, False)
    Set fld = Nothing
 
  End With
  Set tdf = Nothing
Set db = Nothing
 
End Sub

you can try to run it, and see (just need to create "Contacts" table

[Michael R]

notice that in the AllowMultipleValues property I choose DDL as false.

[mcupito]

When you say you "open the table" does that refer to viewing the recordset from double clicking the Table object on the Access Objects list, or does it occur when a form opens using the table from data?

Also, I'm not sure if this matters but I would move the

Set fld = Nothing

outside of the

With

[Michael R]

It occurs only when i view the recordset from double clicking the Table object on the Access Objects lists.

Also, even though viewing the table's fields properties through Access Objects list shows Allow Multiple Values property as True, it only presents a regular Combo Box in it's display control (both from a form or from table view) and not the Multi Combo Box that it suppose to present.

[Seth Schrock]

While MS Access does support multi-value fields, it is not considered a best practice for proper database design. It would be better to create a new table and then link them using a Primary Key - Foreign Key pair. Not only would you totally get around your current troubles, but you would also save yourself from headaches further down the line. See Database Normalization and Table Structures for more information on proper database design.

And it is very easy to do it using the separate table method.

[Michael R]

Well, thanks so much for your suggestion, Seth.
I've used the multi-value fields, and although it is not considered as a best practice, they served me well.
Also, the give my users the ability to multi-select values from combo-boxes, which is very UI-convinient, and is possible only through Multi Value Fields.

[Michael R]

mcupito, I moved it outside With, you are right,
but the problem still remains...

[Michael R]

There is probably some table property that needs to be applied in order for this to work, I just don't know which one.

[mcupito]

At this point, it might be useful to see what methods you tried when you say "whether I save it..."

From the message it sounds like it is not being saved correctly. I could be wrong though.

[Michael R]

When Access asks me: "You have changed the "New: lookup column to store multiple values. You will not be able to undo this change once you save the table"
"Do you want to change "New" lookup column to store multiple values?"
Either I choose "yes" or "no", the result is the same, that column also appears to be multivalued doesn't allow choosing multiple values.

[ADezii]

I apologize for taking such a late look at Thread, but when I finally did, I took a keen interest in it. I do believe that I got it working exactly as it should, but I will let you be the judge of that. For the Row Source of the Multi-Valued Field I used the [Last Name] Field of the Employees Table only for the sake of brevity and simplicity, modify as needed. Let us know how you make out.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As Field
 
Set db = CurrentDb
Set tdf = db.TableDefs("Contacts")
 
With tdf
  Set fld = .CreateField("New", dbComplexText, 255)
    .Fields.Append fld
      With fld
        .Properties.Append .CreateProperty("AllowMultipleValues", dbBoolean, True)
        .Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
        .Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
        .Properties.Append .CreateProperty("RowSource", dbText, _
                                           "SELECT [Last Name] FROM Employees")
        .Properties.Append .CreateProperty("BoundColumn", dbInteger, 1)
        .Properties.Append .CreateProperty("ColumnCount", dbInteger, 1)
        .Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
        .Properties.Append .CreateProperty("ListRows", dbInteger, 16)
        .Properties.Append .CreateProperty("LimitToList", dbBoolean, True)
      End With
        Set fld = Nothing
End With
 
Set tdf = Nothing
Set db = Nothing

[Michael R]

ADezii, I appreciate your input and help.
Also I appreciate mcupito and Seth Shrock's time.

:)) !!
It works,
with dbComplexText / dbComplexLong / etc..
How wonderful,
thanks.

p.s.
What is dbComplex?

[ADezii]

@MichaelR:
Glad we got it working for you. In the case of dbComplexText, it is a Multi-value, variable width, Complex DAO Data Type used specifically in Multi-Value Fields. As for the other Complex Data Type variations, the same concept would apply.