concatenate Records into an unbound bux on a continuos subform

[Costefran]

Can you please help

Is there any way to concatenate values from a control on a continuous subform and set an unbound box on to the same form
For example
If I have a subform with a combobox 'A' set to limit to list and a default value of 'None'

The user selects as follows

Record 1 - 'Tea' selected
Record 2 - 'Milk' selected
Record 3 - 'Sugar' selected
Record 4 - Nothing selected so default Value is 'None'

I would like the unbound box value to be 'Tea, Milk, Sugar'

Any help you can give would be appreciated

 

[MajP]

This tells me that the loop never executes. So have to find out why.

Do While Not rs.EOF
For some reason either you have no records returned or you are at the end of file.

add this code before the loop
rs.movelast
rs.movefirst
debug.print rs.recordcount

Do While Not rs.EOF
....
my guess it will fail.

 

[Costefran]

Both of you thanks

MajP's code works to some degree

I now have some text population the control in my subforms footer although its confusing as

The first selection returns an error saying no current record
The second selection adds the selected string
If I go back to the first selection and reselect I the original first selection is then dropped into the box

When this is working I assume that if a user selects "None" on a previously selected string then it will be removed from the control at the footer?

Anyway it seems that it is going in the right direction

Thanks again and nearly there!

 

[Costefran]

Okay I think I have worked out what the code is now doing

1st record selection I get an error saying no current record
2nd record selection I get the 1st Record string in my footer control
3rd record selection I get the 2nd Record string in my footer control

and so on...............

 

[Costefran]

I thought this would help from the immpedite window. I have added a coupkle of comments which I hope makes sense

' 1st Record Seletion
Function Called
'Error message no current record

' 2nd Record Selection
Function Called
1
In Loop
Done loop Disable 1 Complete Chiller System,
cleaned up Disable 1 Complete Chiller System
' End of Function

 

[MajP]

Try this before calling the function

me.dirty = false
Me.Cooling_Central_Plant_Aggregate_Text = getSelections

On the first selection the record is likely not saved to the underlying table.

 

[Costefran]

That works!!!!!!

Thanks so much for helping me out on this one

It really is appreciated

 

[Costefran]

I now have another problem as follows

The concetenate works fine. I then concatenate the subform footer values and set the all of these into a bound text box (set to Memo) on my main form (called Alltexts) which is linked to the main forms underlying table which enables me to save it in the table record

The general approach is as follows

Main Form. Alltexts = Subform1.TextAll & Subform2.TextAll

This works fine

The problem is as follows

When I open the form in add mode for a new record everything works fine

However when I navigate to a new record using the forms navigation buttons the subform1.TextAll and Subform2.TextAll still have values from the previous record and the first time I select a new record on subform1 or subform2 it sets my main form Alltexts with values from the previous record

So I guess I need to clear TextAll on each of the subforms footers when it is a new record on the main form but keep them when it is an existing record on the main form

I'm not sure of the best way to do this and have tried a couple of methods but cannot seem to get it right

so once again any help would be appreciated

 

[MajP]

You may consider never saving the value, but always calculating the value. This ensures that whatever is being shown is always up to date. If you have thousands of records you would be better off saving then calculating dynamically. See dhookum's link to a generic function or use something like:

Public Function concatWorkType(parentID as variant) As String
  Dim rs As DAO.Recordset
  Dim strList As String
  dim strSql as string
  strSql = "Select [Cooling Central Plant Work Type 1] from someTable where = " & parentID
  Set rs = currentDb.openrecordset(strSql)
  
  Do While Not rs.EOF
   If rs![Cooling Central Plant Work Type 1] <> "None" Then
      strList = strList & rs![Cooling Central Plant Work Type 1] & ", "
    End If
    rs.MoveNext
  Loop
  If Not strList = "" Then
    strList = Left(strList, Len(strList) - 2)
  End If
  concatWorkType = strList
End Function

Basically the same function except we are passing in a primary key to determine which records to concatenate.
Now this can be used in a form/report calculated control or in a query. On a form it would look something like:

controlsource:=concatWorkType([txtBoxParentID])

in a query
Select ...,concatWorkType([parentIDfieldName]) as WorkTypes,....from someTable ..

 

[MajP]

before trying to use this in a form or query you can test/debug in the immediate window. Pick a primarykey. Lets say 123 has three choices. Then to test

?concatWorkType(123)

shoud print your concatenated worktypes. To test a string pk
?concatWorkType("abc")
Also you would have to modify the strSql from
...where parentIDfield = " & parentID
to be
...where parentIDfield = '" & parentID & "'