在ms访问报告或表单上组合文本框中的名称 [英] Combining Names in A Textbox on a ms access report or form

问题描述

我有2张桌子连在一起


表1：

[活动] {EventID，EventName，CustomerID}


表2：

[客户] {CustomerID，CustomerFirstName，CustomerLastName，AGE}


他们很快就加入了CustomerID，但是我想要一个报告，在一个文本框中显示一个事件的多个客户。


IE报告如下：


活动：周六客户：Charlie Brown，Alex Maine，Roger Wells

活动：周一客户：Watson博士，Green博士


i希望它们也被逗号加入，我还没有能够提出任何将链接ID组合成几行的函数


任何想法

解决方案

i有2个表链接在一起


表1：

[事件] {EventID，EventName，CustomerID}


表2：

[客户] {CustomerID，CustomerFirstName，CustomerLastName，AGE}


他们显然是由CustomerID加入的，但我想要一个报告，在一个文本框中显示一个事件的多个客户。 />

IE报告如下：


活动：周六客户：Charlie Brown，Alex Maine，Roger Wells

活动：周一客户：Watson博士，Green博士


i希望它们也被逗号加入，我还没有能够提出任何将链接ID组合成几行的函数


任何想法

从任何返回所需记录的查询中获取的默认视图将是一列。您希望从列视图转到行视图，这不是很容易也不是直观的完成！


一种方法是填充隐藏的表单（或者可能需要多个隐藏的表单，不知道，因为我没有尝试过这个）你希望连接的值，然后从这个隐藏的表单中抽取来填充你的报告上的标签或文本框的值。


连接的实际代码（这不包括打开隐藏表单，验证信息，处理错误等的代码）看起来像这样： />

在报告的开启事件中：

展开 | 选择 | Wrap | 行号

i有2个表链接在一起


表1：

[Event] {EventID，EventName，CustomerID}


表2：

[客户] {CustomerID，CustomerFirstName，CustomerLastName，AGE} <很明显，他们是CustomerID加入的，但是我想要一个报告，在一个文本框中显示一个事件的多个客户。


IE报告如下：


活动：周六客户：Charlie Brown，Alex Maine，Roger Wells

活动：周一客户：Watson博士，Green博士


i希望它们也被逗号加入，我还没有能够提出任何将链接ID组合成几行的函数


任何想法

其中一种方法是编写VBA函数获取作为参数CustomerID并返回你提到的连接字符串。函数代码

• 打开一个Recordset，从[Customer]表中检索所有记录，其中[CistomerID] =<参数传递给函数>
• 迭代Recordset连接必要字段中的所有记录（ [CustomerFirstName]，[CustomerLastName]）到一个字符串
• 返回字符串

这个函数可以在查询中用作计算字段或表单/报表控件作为ControlSource。

是否可以使用select语句将这些行连接起来或将它们组合在一个字符串值中？

i have 2 tables which are linked together

table 1:
[Event] { EventID, EventName, CustomerID }

table 2:
[Customer] { CustomerID, CustomerFirstName, CustomerLastName, AGE }

they are join by CustomerID obvouisly, but i want a report that shows multiple customers in one textbox for one Event.

I.E. Report would look like:

Event: Saturday Customers: Charlie Brown, Alex Maine, Roger Wells
Event: Monday Customers: Dr. Watson, Dr. Green

i would like them to be joined by commas as well, i haven''t been able to come up with any function that will combine several rows together of linked ids

any thoughts

解决方案

i have 2 tables which are linked together

table 1:
[Event] { EventID, EventName, CustomerID }

table 2:
[Customer] { CustomerID, CustomerFirstName, CustomerLastName, AGE }

they are join by CustomerID obvouisly, but i want a report that shows multiple customers in one textbox for one Event.

I.E. Report would look like:

Event: Saturday Customers: Charlie Brown, Alex Maine, Roger Wells
Event: Monday Customers: Dr. Watson, Dr. Green

i would like them to be joined by commas as well, i haven''t been able to come up with any function that will combine several rows together of linked ids

any thoughts

The default view that you are going to get from any query that returns the records you want is going to be a column. You are wanting to go from column view to a row view, which is not going to be very easy nor intuitive to accomplish!

One approach would be to populate a hidden form (or possibly would require mulitiple hidden forms, don''t know as I''ve not tried this) with the values you wish to concatenate, then draw from this hidden form(s) to populate the values of a label or text box on your report.

The actual code to concatenate (this doesn''t include the code to open the hidden form, validate the info, handle errors, etc) would look something like this:

In the on open event of your report:

Expand|Select|Wrap|Line Numbers

i have 2 tables which are linked together

table 1:
[Event] { EventID, EventName, CustomerID }

table 2:
[Customer] { CustomerID, CustomerFirstName, CustomerLastName, AGE }

they are join by CustomerID obvouisly, but i want a report that shows multiple customers in one textbox for one Event.

I.E. Report would look like:

Event: Saturday Customers: Charlie Brown, Alex Maine, Roger Wells
Event: Monday Customers: Dr. Watson, Dr. Green

i would like them to be joined by commas as well, i haven''t been able to come up with any function that will combine several rows together of linked ids

any thoughts

One of the way to do it is to write VBA function getting as argument CustomerID and returning concatenated string you are mentioning. The function code

• opens a Recordset retrieving all records from [Customer] table where [CistomerID]=<argument passed to the function>
• iterates all records in the Recordset concatenating necessary fields ([CustomerFirstName], [CustomerLastName]) into a string
• returns the string

This function may be used in query as calculated field or form/report control as ControlSource.

Is it possible to join these rows with a select statement or grouping them together in a string value?

这篇关于在ms访问报告或表单上组合文本框中的名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！