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, 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

[Scott Price]

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:

label1.caption = Forms![YourFormName]![YourTextBoxName] & ": " & Forms![YourFormName]![YourTextBoxName1] & ", " & Forms![YourFormName]![YourTextBoxName2]  

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, 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.

[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 qryEventCustomer, and it had this SQL:

SELECT tblCustomer.CustomerID, tblEvent.EventName, tblCustomer.CustomerFirstname & " " & tblCustomer.CustomerLastName AS Customer
FROM tblCustomer INNER JOIN tblEvent ON tblCustomer.CustomerID = tblEvent.CustomerID
WHERE (((tblEvent.EventName)="Saturday Morning"));

Next, create an unbound form (I named it frmEventCustomer) in design view. On this form place a listbox named lstEventCustomer. When the wizard comes up, have the listbox look up the values in your qryEventCustomer. 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:

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 "frmEventCustomer", , , , , acHidden
 
Set rs = Forms!frmEventCustomer!lstEventCustomer.Recordset
ls = Forms!frmEventCustomer!lstEventCustomer.ListCount
            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, "frmEventCustomer", acSaveNo
End Function

Next, on your report, create a label named lblEventCustomer. In the OnOpen event of your report, place this code:

Call EventCustomer
Me!lblEventCustomer.Caption = EventCustomer

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:

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 "frmEventCustomer", , , , , acHidden
 
Set rs = Forms!frmEventCustomer!lstEventCustomer.Recordset
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, "frmEventCustomer", acSaveNo
Erase MyArray
rs.Close
 
End Function

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