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]

If I understand the combobox is bound to a field. So really all you want to do is concatenate all the field values. I would do it this way

build a function

Private Sub Form_Current()
  Me.txtAll = getSelections
End Sub

Public Function getSelections() As String
  Dim rs As DAO.Recordset
  Dim strList As String
  
  Set rs = Me.RecordsetClone
  
  Do While Not rs.EOF
    If rs!yourFieldName <> "None" Then
      strList = strList & rs!yourfieldName & ", "
    End If
    rs.MoveNext
  Loop
  If Not strList = "" Then
    strList = Left(strList, Len(strList) - 2)
  End If
  getSelections = strList
End Function

now you can call this to populate the text box in the subform footer or header. You could put it in the form load event and the combo after update.

  Me.txtAll = getSelections

 

[dhookom]

I like MajP's suggestion because it will honor any filter on the subform. There is a generic function faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[Costefran]

MajP

Many thanks for your'e help I have added your code with my own fieldnames as below. I now get a compile error End if withou Block if. I have deleted both End ifs and run the code which removes the compile error but the aggregate text box shows no value. Please note I am a learner regards coding so have probably done something wrong

Private Sub Cooling_Central_Plant_Work_Type_1_AfterUpdate()
Dim rs As DAO.Recordset
Dim strList As String
Set rs = Me.RecordsetClone

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
getSelections = strList
End Sub


Private Sub Form_Current()
Me.Cooling_Central_Plant_Aggregate_Text = getSelections
End Sub

 

[dhookom]

I don't remember placing anything other than comments after the "Then" of an "If Then". If you do, you should remove the "End If".
Try:

Private Sub Cooling_Central_Plant_Work_Type_1_AfterUpdate()
  Dim rs As DAO.Recordset
  Dim strList As String
  Set rs = Me.RecordsetClone
  
  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
  getSelections = strList
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Costefran]

Thanks again and thanks for your patience. The compile error has gone away now I have pasted the code as below. However it still does not seem to work so I still haven't got it right. When I step through the code it jumps past the Dim statements which maybe it should (I said I was a novice) and it also jumps past the two If statements

Private Sub Cooling_Central_Plant_Work_Type_1_AfterUpdate()
Dim rs As DAO.Recordset
Dim strList As String
Set rs = Me.RecordsetClone
If Me.Cooling_Central_Plant_Work_Type_1 = "None" Then Me.SA_Cooling_Central_Plant_1 = Null Else Forms![Work Input Form]![Cooling Central Plant Table Subform].Form![SA Cooling Central Plant 1] = DLookup("[Chiller System Arrangement]", "Building Name Table", "[Buildng Name] = Forms![Work Input Form]![Building]")

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
getSelections = strList

Me.Cooling_Central_Plant_Aggregate_Text = getSelections
End Sub

 

[dhookom]

Take some advice and don't put your "If Then ... " all on one line. It's too hard to see what you are doing.
Do you really have two spaces in [Chiller System Arrangement]?
Where is this code running? Is it in Forms![Work Input Form]![Cooling Central Plant Table Subform]?

Private Sub Cooling_Central_Plant_Work_Type_1_AfterUpdate()
  Dim rs As DAO.Recordset
  Dim strList As String
  Set rs = Me.RecordsetClone
  If Me.Cooling_Central_Plant_Work_Type_1 = "None" Then 
    Me.SA_Cooling_Central_Plant_1 = Null
   Else
    Forms![Work Input Form]![Cooling Central Plant Table Subform].Form![SA Cooling Central Plant 1] = _
       DLookup("[Chiller  System Arrangement]", "Building Name Table", "[Buildng Name] = Forms![Work Input Form]![Building]")
  End If
  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
  getSelections = strList
  
  Me.Cooling_Central_Plant_Aggregate_Text = getSelections
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Costefran]

Thanks for the advice regards the If then not on one line
Ive been trying to follow code on a small laptop screen on one line which is very difficult so that's a great help

Yes [Chiller System Arrangement] does have two spaces

The code works fine until it gets to

Do While Not rs.EOF

It then jumps to

If Not strList = "" Then

It then jumps to

End If

I'll keep searching and trying
Thanks

 

[dhookom]

This code clones the current form's record source where the code is running. If the code is in the main form, it will not clone the subform recordset.

Set rs = Me.RecordsetClone

Duane
Hook'D on Access
MS Access MVP

 

[Costefran]

Thanks

To clarify

I have the code on the after update event of the control on the subform not the main form

Paul

 

[dhookom]

I would probably use it in the after update of the record in the subform. Otherwise add code to make sure the subform record gets saved prior to running the code.

After reviewing your code, I am really confused by what is being called from when and where. Does your code compile? There had been a function named getSelections() that seems to have disappeared.

Can you provide your specifications of what you expect to do and when?

Duane
Hook'D on Access
MS Access MVP

 

[Costefran]

Looks like I've managed to confuse things so I have gone back to the beginning to try again

Here is the code as I have it now. The code runs on the subform with no compile errors. On the after update event the code seems to run fine but ignores both If statements

Private Sub Cooling_Central_Plant_Work_Type_1_AfterUpdate()

'I assume this will save the record
Me.Dirty = False

'I assume I have to call the function after update
Call getSelections

End Sub

Private Sub Form_Current()
Me.txtAll = getSelections
End Sub

Public Function getSelections() As String
Dim rs As DAO.Recordset
Dim strList As String

Set rs = Me.RecordsetClone

Do While Not rs.EOF

If rs!yourfieldName <> "None" Then
strList = strList & rs!yourfieldName & ", "
End If
rs.MoveNext
Loop
If Not strList = "" Then
strList = Left(strList, Len(strList) - 2)
End If
getSelections = strList
End Function

 

[dhookom]

Do you actually have a field named "YourFieldName"?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Public Function getSelections() As String
  Dim rs As DAO.Recordset
  Dim strList As String
  debug.print "Function Called"
  Set rs = Me.RecordsetClone
  
  Do While Not rs.EOF
   debug.print "In Loop " & strList 
   If rs![Cooling Central Plant Work Type 1] <> "None" Then
      strList = strList & rs![Cooling Central Plant Work Type 1] & ", "
    End If
    rs.MoveNext
  Loop
  debug.print "Done loop " & strList
  If Not strList = "" Then
    strList = Left(strList, Len(strList) - 2)
  End If
  debug.print "cleaned up " & strList
  getSelections = strList
End Function


Private Sub Cooling_Central_Plant_Work_Type_1_AfterUpdate()

  If Me.Cooling_Central_Plant_Work_Type_1 = "None" Then
    Me.SA_Cooling_Central_Plant_1 = Null
   Else
    Forms![Work Input Form]![Cooling Central Plant Table Subform].Form![SA Cooling Central Plant 1] = _
       DLookup("[Chiller  System Arrangement]", "Building Name Table", "[Buildng Name] = Forms![Work Input Form]![Building]")
  End If

  Me.Cooling_Central_Plant_Aggregate_Text = getSelections
End Sub

Debug it

 

[Costefran]

I have copied and pasted your code into my subform code and having the same problem

I have debugged with no issues but it still seems to jumps at the If statements

 

[dhookom]

What is the data type of [Cooling Central Plant Work Type 1]? Is it a foreign key to a lookup table? Is it actually a text field that stores a value like "none"?

If you place a breakpoint in the code and move your mouse over the rs![Cooling Central Plant Work Type 1] what do you see for a value?

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Run the code. And look at the immediate window. Paste the results back. I put those messages in so you could debug.
Or just post your database and we can take a look. I use 4shared.com as a free site, but there are several options.

 

[Costefran]

Thanks again

The data type of [Cooling Central Plant Work Type 1] is a text field where the subform is based on a table and [Cooling Central Plant Work Type 1] is based on a query in which the SQL looks like this. In essence what I am trying to do is ensure that the string "None" is always at the top of the list when the user clicks the combobox

This is the 'Cooling Central Plant Subform Q1' Query

SELECT [Actual Potential Disablement Table].[Actual Potential Disablement], 1 AS [Count]
FROM [Actual Potential Disablement Table]
GROUP BY [Actual Potential Disablement Table].[Actual Potential Disablement], [Actual Potential Disablement Table].System, 1
HAVING ((([Actual Potential Disablement Table].System)="Cooling Central Plant"))
ORDER BY [Actual Potential Disablement Table].[Actual Potential Disablement];
UNION ALL SELECT [None Baseline table for work input subforms].None, [None Baseline table for work input subforms].Count
FROM [None Baseline table for work input subforms];

This is the 'Cooling Central Plant Subform Q2' Query which is the rowsource for my control

SELECT [Cooling Central Plant Subform Q1].[Actual Potential Disablement]
FROM [Cooling Central Plant Subform Q1]
ORDER BY [Cooling Central Plant Subform Q1].Count, [Cooling Central Plant Subform Q1].[Actual Potential Disablement];


I have also put a breakpoint in my code and tried to hover over rs![Cooling Central Plant Work Type 1]as the code never stops there the step gets to
Do While Not rs.EOF and jumps the If statement
It seems to me (and as you know I'm not the expert) that
there is something wrong with my form or code regarding the
Do While Not rs.EOF

I will now look at the immediate window although I've never done this before so something new I will learn

Thanks

 

[Costefran]

And here is the results of the immediate window

Function Called
Done loop
cleaned up

 

[dhookom]

I would try figure out if there are any records in the recordset.

Set rs = Me.RecordsetClone
Debug.Print rs.RecordCount

Duane
Hook'D on Access
MS Access MVP