Combining Names in A Textbox on a ms access report or form

[ckeller06]

i have 2 tables which are linked together

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

table 2:
[Customer] { CustomerID, CustomerFirstNa me, CustomerLastNam e, 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

[Scott Price]

i have 2 tables which are linked together

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

table 2:
[Customer] { CustomerID, CustomerFirstNa me, CustomerLastNam e, 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:

[CODE=vb]label1.caption = Forms![YourFormName]![YourTextBoxName] & ": " & Forms![YourFormName]![YourTextBoxName 1] & ", " & Forms![YourFormName]![YourTextBoxName 2] [/CODE]

etc etc etc.

This vb code would concatenate three text boxes on one form with a : and space between the first two and a , and space between the second two, then assign the value to a label creatively named label1 on your report.

Might be quite a bit easier to just resign yourself to seeing the thing in column view and adjust your report accordingly!

I'll be happy to be corrected by anyone that has any better ideas on how to do this!

Regards,
Scott

[FishVal]

i have 2 tables which are linked together

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

table 2:
[Customer] { CustomerID, CustomerFirstNa me, CustomerLastNam e, 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 ([CustomerFirstNa me], [CustomerLastNam e]) into a string
• returns the string

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

[ckeller06]

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

[Scott Price]

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

Afraid not! I've just worked out a code sample in my test database that will do what you want. To explain:

The process of making this work in your database will be as follows:

Create a query that pulls the values you want to see. I called mine qryEventCustome r, and it had this SQL: [CODE=sql]SELECT tblCustomer.Cus tomerID, tblEvent.EventN ame, tblCustomer.Cus tomerFirstname & " " & tblCustomer.Cus tomerLastName AS Customer
FROM tblCustomer INNER JOIN tblEvent ON tblCustomer.Cus tomerID = tblEvent.Custom erID
WHERE (((tblEvent.Eve ntName)="Saturd ay Morning"));[/CODE]

Next, create an unbound form (I named it frmEventCustome r) in design view. On this form place a listbox named lstEventCustome r. When the wizard comes up, have the listbox look up the values in your qryEventCustome r. Save and close the form.

Next open your vba editor window and on the left-hand side click on the + sign next to the Modules folder. If there are no modules, insert one.

Place this code in the module:

[CODE=vb]Public Function EventCustomer() As String

Dim MyArray() As Variant
Dim evcust As String
Dim cust As String
Dim ev As String
Dim rs As DAO.Recordset
Dim ls As Integer
Dim intCounter As Integer
Dim list As Integer
DoCmd.OpenForm "frmEventCustom er", , , , , acHidden

Set rs = Forms!frmEventC ustomer!lstEven tCustomer.Recor dset
ls = Forms!frmEventC ustomer!lstEven tCustomer.ListC ount
With rs
.MoveFirst
MyArray() = .GetRows(ls)
End With

ev = MyArray(1, 0)
cust = MyArray(2, 0)
list = 0
evcust = ev & ": " & cust
For intCounter = 1 To (ls - 1)
list = list + 1
cust = MyArray(2, list)
evcust = evcust & ", " & cust
Next

EventCustomer = evcust
DoCmd.Close acForm, "frmEventCustom er", acSaveNo
End Function[/CODE]

Next, on your report, create a label named lblEventCustome r. In the OnOpen event of your report, place this code:
[CODE=vb]
Call EventCustomer
Me!lblEventCust omer.Caption = EventCustomer[/CODE]

Save everything, test... Let us know if this works for you, or if you have any further trouble implementing it!

Regards,
Scott

[ckeller06]

well i have followed what you have done, and you have done a good job, I see the logic behind this process a little bit better, however, I am stuck on an error that needs debugging: it says user function not defined... when i enter in debug mode it has rs As DAO.Recordset

I fiddled around with this making it a string/integer/recordset but i still haven't been able to correct this error

ps: when i do chance DAO.Recordset to recordset it says that rs is a miss match.

[JKing]

Check to make sure you have a reference to the Microsoft DAO Object Library. You do this from Tools > References in the VBA editor.

[ckeller06]

ok that was a simple fix, however now it's saying Subscripts are out of range with MyArrays() or ev and cust

[Scott Price]

ok that was a simple fix, however now it's saying Subscripts are out of range with MyArrays() or ev and cust

Had to do a spot of refiguring... for some reason, the ls variable was counting the wrong number of records in the recordset... Anyway, try this instead:
[CODE=vb]
Public Function EventCustomer() As String

Dim MyArray() As Variant
Dim evcust As String
Dim cust As String
Dim ev As String
Dim rs As DAO.Recordset
Dim ls As Integer
Dim intCounter As Integer
Dim list As Integer
DoCmd.OpenForm "frmEventCustom er", , , , , acHidden

Set rs = Forms!frmEventC ustomer!lstEven tCustomer.Recor dset
Erase MyArray
With rs
.MoveFirst
.MoveLast
ls = .RecordCount
.MoveFirst
MyArray() = .GetRows(ls)
End With

ev = MyArray(1, 0)
cust = MyArray(2, 0)
list = 0
evcust = ev & ": " & cust
For intCounter = 1 To (ls - 1)
list = list + 1
cust = MyArray(2, list)
evcust = evcust & ", " & cust
Next
EventCustomer = evcust
DoCmd.Close acForm, "frmEventCustom er", acSaveNo
Erase MyArray
rs.Close

End Function[/CODE]

Regards,
Scott

[ckeller06]

ok totally works perfectly now... the problem was that for the array I only had it called for the customername in the form listbox, once i added the event and then modified the array dimension to:

ev = MyArray(0, 0)
cust = MyArray(1, 0)

it fixed my subsript out of range problems


THANKS TO ALL!!!

Now i am just going to play with if for a bit so that i dont need to do a form for each event. and maybe even using this tech of storing information I might try running a query store it and place the recordset inside an array and gather that array into a list string

again thank you!

[Scott Price]

ok totally works perfectly now... the problem was that for the array I only had it called for the customername in the form listbox, once i added the event and then modified the array dimension to:

ev = MyArray(0, 0)
cust = MyArray(1, 0)

it fixed my subsript out of range problems


THANKS TO ALL!!!

Now i am just going to play with if for a bit so that i dont need to do a form for each event. and maybe even using this tech of storing information I might try running a query store it and place the recordset inside an array and gather that array into a list string

again thank you!

Glad it's working for you! and you're welcome.

Regards,
Scott