468,539 Members | 1,906 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,539 developers. It's quick & easy.

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

176 100+
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:

Expand|Select|Wrap|Line Numbers
  1. Sub Test1()
  2. Dim db As dao.Database, tdf As dao.TableDef, fld As Field
  3. Set db = CurrentDb
  4.   Set tdf = db.TableDefs("Contacts")
  5.   With tdf
  6.  
  7.     Set fld = .CreateField("New", dbText, 255)
  8.  
  9.     .Fields.Append fld
  10.  
  11.  
  12.     fld.Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
  13.     fld.Properties.Append ..CreateProperty("AllowMultipleValues", dbBoolean, True)
  14.     Set fld = Nothing
  15.  
  16.   End With
  17.   Set tdf = Nothing
  18. Set db = Nothing
  19.  
  20. 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?
Feb 8 '14 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim tdf As DAO.TableDef
  3. Dim fld As Field
  4.  
  5. Set db = CurrentDb
  6. Set tdf = db.TableDefs("Contacts")
  7.  
  8. With tdf
  9.   Set fld = .CreateField("New", dbComplexText, 255)
  10.     .Fields.Append fld
  11.       With fld
  12.         .Properties.Append .CreateProperty("AllowMultipleValues", dbBoolean, True)
  13.         .Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
  14.         .Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
  15.         .Properties.Append .CreateProperty("RowSource", dbText, _
  16.                                            "SELECT [Last Name] FROM Employees")
  17.         .Properties.Append .CreateProperty("BoundColumn", dbInteger, 1)
  18.         .Properties.Append .CreateProperty("ColumnCount", dbInteger, 1)
  19.         .Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
  20.         .Properties.Append .CreateProperty("ListRows", dbInteger, 16)
  21.         .Properties.Append .CreateProperty("LimitToList", dbBoolean, True)
  22.       End With
  23.         Set fld = Nothing
  24. End With
  25.  
  26. Set tdf = Nothing
  27. Set db = Nothing

17 3322
mcupito
294 256MB
FYI, did you notice the 2 "." in your code? Is that a mistake?

Expand|Select|Wrap|Line Numbers
  1. fld.Properties.Append ..CreateProperty
On the same line, check this out:

Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 8 '14 #2
Michael R
176 100+
It's a typo here, in the code I got it right.
Feb 8 '14 #3
mcupito
294 256MB
Michael R, see my edit.
Feb 8 '14 #4
Michael R
176 100+
I made the DDL false, and still get the same message after entering the altered table.
Feb 8 '14 #5
Michael R
176 100+
this is my current code, after your suggestion:

Expand|Select|Wrap|Line Numbers
  1. Sub Test1()
  2. Dim db As dao.Database, tdf As dao.TableDef, fld As Field
  3. Set db = CurrentDb
  4.   Set tdf = db.TableDefs("Contacts")
  5.   With tdf
  6.  
  7.     Set fld = .CreateField("New3", dbText, 255)
  8.  
  9.     .Fields.Append fld
  10.  
  11.  
  12.     fld.Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
  13.     fld.Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
  14.     fld.Properties.Append .CreateProperty("RowSource", dbText, "SELECT Slownik.Sprzedawca FROM Slownik WHERE (((Slownik.Sprzedawca) Is Not Null));")
  15.     fld.Properties.Append .CreateProperty("BoundColumn", dbInteger, 1)
  16.     fld.Properties.Append .CreateProperty("ColumnCount", dbInteger, 1)
  17.     fld.Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
  18.     fld.Properties.Append .CreateProperty("ListRows", dbInteger, 16)
  19.     fld.Properties.Append .CreateProperty("LimitToList", dbBoolean, True)
  20.     fld.Properties.Append .CreateProperty("AllowValueListEdits", dbBoolean, False)
  21.     fld.Properties.Append .CreateProperty("ShowOnlyRowSourceValues", dbBoolean, False)
  22.     fld.Properties.Append .CreateProperty("AllowMultipleValues", dbBoolean, True, False)
  23.     Set fld = Nothing
  24.  
  25.   End With
  26.   Set tdf = Nothing
  27. Set db = Nothing
  28.  
  29. End Sub
you can try to run it, and see (just need to create "Contacts" table
Feb 8 '14 #6
Michael R
176 100+
notice that in the AllowMultipleValues property I choose DDL as false.
Feb 8 '14 #7
mcupito
294 256MB
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
Expand|Select|Wrap|Line Numbers
  1. Set fld = Nothing
outside of the
Expand|Select|Wrap|Line Numbers
  1. With
Feb 8 '14 #8
Michael R
176 100+
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.
Feb 8 '14 #9
Seth Schrock
2,951 Expert 2GB
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.
Feb 8 '14 #10
Michael R
176 100+
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.
Feb 8 '14 #11
Michael R
176 100+
mcupito, I moved it outside With, you are right,
but the problem still remains...
Feb 8 '14 #12
Michael R
176 100+
There is probably some table property that needs to be applied in order for this to work, I just don't know which one.
Feb 8 '14 #13
mcupito
294 256MB
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.
Feb 8 '14 #14
Michael R
176 100+
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.
Feb 9 '14 #15
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Dim db As DAO.Database
  2. Dim tdf As DAO.TableDef
  3. Dim fld As Field
  4.  
  5. Set db = CurrentDb
  6. Set tdf = db.TableDefs("Contacts")
  7.  
  8. With tdf
  9.   Set fld = .CreateField("New", dbComplexText, 255)
  10.     .Fields.Append fld
  11.       With fld
  12.         .Properties.Append .CreateProperty("AllowMultipleValues", dbBoolean, True)
  13.         .Properties.Append .CreateProperty("DisplayControl", dbInteger, acComboBox)
  14.         .Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
  15.         .Properties.Append .CreateProperty("RowSource", dbText, _
  16.                                            "SELECT [Last Name] FROM Employees")
  17.         .Properties.Append .CreateProperty("BoundColumn", dbInteger, 1)
  18.         .Properties.Append .CreateProperty("ColumnCount", dbInteger, 1)
  19.         .Properties.Append .CreateProperty("ColumnHeads", dbBoolean, False)
  20.         .Properties.Append .CreateProperty("ListRows", dbInteger, 16)
  21.         .Properties.Append .CreateProperty("LimitToList", dbBoolean, True)
  22.       End With
  23.         Set fld = Nothing
  24. End With
  25.  
  26. Set tdf = Nothing
  27. Set db = Nothing
Feb 9 '14 #16
Michael R
176 100+
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?
Feb 9 '14 #17
ADezii
8,800 Expert 8TB
@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.
Feb 9 '14 #18

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Lars Netzel | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.