附加多值列表 [英] Appending Multi-value lists

问题描述

我有一个Access 2007数据输入表单，该表单绑定到一个表，该表仅用作数据的存放站，直到对其进行检查并附加到永久表之前.在表(并因此在窗体)中，有多个字段具有多值选择(从查询表查询中得出).用户不在乎数据是否可搜索，因此将其存储在文本字段中作为长列表就可以了.问题在于追加.我无法将多值列表附加到表中.

I have an Access 2007 data entry form that is bound to a table that is used solely as a holding station for the data until it is reviewed and appended to the permanent tables. There are several fields in the table (and thus form) that have multi-value selections (derived from a look-up table query). The user does not care if the data is searchable so storing in the text field as a long list is fine. The problem is with appending. I cannot append multi-value lists to a table.

我注意到，如果我将表另存为excel电子表格，然后将其重新导入到具有与导出表相同的数据库结构的表中，但是对于那些需要多值的字段，则没有多值组合框(它们现在是只是文本框)，列表就在那里，并按照我想要的方式进行了格式化.

I noticed that if I save the table as an excel spreadsheet and then import it back into a table with the same database structure as the exporting table but without multi-value combo boxes for those fields requiring multi-values (they are now just text boxes) that the list is there and formatted the way I want.

所以我在想，Access使用什么代码将正确的值导出到Excel，我是否可以使用类似的代码将其附加到永久表中(不必将相应的文本字段格式化为多行格式，值列表)，而不必导出到Excel文件，然后再将其重新导入到新模板中?请注意，尽管有些列表只有10-15个选项，但一个列表有50多种可能的选择.

So I am thinking, what code is Access using to export the correct values to Excel and could I just use similar coding to append to the permanent tables (which don’t have to have the corresponding text fields formatted as a multi-value list) instead of having to export to an Excel file and then import this back into a new template? Please note that although some of the lists are only 10-15 options, one list has over 50 possible selections to choose from.

我只是想创建一个用户友好的数据输入界面，而没有数十个vba编码(我是新手).欢迎有其他想法.

I am just trying to make a user friendly data entry interface without scores of vba coding (which I am a newbie at). Alternate ideas are welcome.

推荐答案

在内部，Access所做的只是艾伦·布朗(Allen Browne)的 ConcatRelated 函数.幸运的是，艾伦的代码将处理多值字段，因此您可以在这样的查询中使用它

Internally, Access is doing just what Allen Browne's ConcatRelated function does. Fortunately, Allen's code will handle multi-valued fields so you can just use it in a query like this

INSERT INTO MainTable ( MealType, Meats )
SELECT MealType, ConcatRelated("Meats","TempTable","TempID=" & [TempID]) AS Expr1
FROM TempTable;

其中[MainTable].[Meats]是Text(255)和[TempTable].[Meats]是多值Lookup字段.

where [MainTable].[Meats] is Text(255) and [TempTable].[Meats] is a multi-valued Lookup field.

这篇关于附加多值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！