subform programming question

[gevayl]

Using Access 2000.
Say you have a form that reads some table. Within this form you have a
subform that reads a given table of n records, but only displays say 5
of them. I want to take a certain cell from this subform and put all
those 5 cells from 5 different rows into a string. Bottom line is how
would one unite cells from a given column for a given number of rows of
that column into a single string that would display in a text field.

Thanks in advance.

[ajamrozek]

Cast your subform RecordSetClone into a recordset you can work with in
code. i.e. in an event that's fired;
Dim rs as RecordSet
Set rs = Forms!frmSubFormName.Form.RecordSetClone
Then work in the scope of the recordset you've got here instead of what
the form is showing. They're more or less the same thing, but it's a
little more staright forward to work with the recordset.
So you could loop through the recordset 5 times
Dim intX as Integer, strX as String
With rs
..MoveLast
..MoveFirst
While Not .EOF Or intx <=5
strX = strX & "," & .Fields("FieldName").Value
intX = intX + 1
.MoveNext
Wend

[Salad]

ge****@gmail.com wrote:

Using Access 2000.
Say you have a form that reads some table. Within this form you have a
subform that reads a given table of n records, but only displays say 5
of them. I want to take a certain cell from this subform and put all
those 5 cells from 5 different rows into a string. Bottom line is how
would one unite cells from a given column for a given number of rows of
that column into a single string that would display in a text field.

Thanks in advance.

'aircode. called form main form. pass the column name to the function
Function MakeString(strCol As String) As String
Dim rst As Recordset

'get the current set of records in the Subform
Set rst = Me("YourSubFormName").Form.RecordsetClone

If rst.RecordCount > 0 then
rst.MoveFirst
Do while Not rst.Eof
MakeString = MakeString & rst(strCol) & ", "
rst.MoveNext
Loop
'remove comma/space
MakeString = Left(MakeString,Len(MakeString)-2)

ENdif
rst.close
set rst = Nothing
End Function

Change it so the recordset points to your subform name. How it would work.

Dim strCell = MakeString("FieldName1")
and this would assign to strCell the values from FieldName1

[gevayl]

Thank you both.

[gevayl]

One quick question. When I tried to implement this code I keep getting
errors for either lines of code: Set rst =
Me("YourSubFormName").Form.RecordsetClone or Set rs =
Forms!frmSubFormName.Form.RecordSetClone. The first keeps saying that
it can't fine the field "YourSubFormName, but its not a field its a
recordset? The other says can't find recordset:frmSubFormName.?

[Salad]

ge****@gmail.com wrote:

One quick question. When I tried to implement this code I keep getting
errors for either lines of code: Set rst =
Me("YourSubFormName").Form.RecordsetClone or Set rs =
Forms!frmSubFormName.Form.RecordSetClone. The first keeps saying that
it can't fine the field "YourSubFormName, but its not a field its a
recordset? The other says can't find recordset:frmSubFormName.?

I had hoped you would substitute YourSubFormName with the subformname in
your database.

When refering to a Subform, the syntax is
'for a field
Forms!MainForm!SubForm!FieldName
'for a property
Forms!MainForm!SubForm.Form.PropertySetting

[gevayl]

I did substitute "YourSubFormName" with my subform name. I tried using
the code in Subform.current() and mainform.current() event procedures
but nogo.

[ajamrozek]

what are the object names you're working with (parent form, child form,
controls)? and in what event are you trying to execute this in? maybe
just post all the code you're using so far

[gevayl]

Ok...I think I figured out the problem. My subform may not have
actually been a subform. It was a select statement. When you want to
insert a subform in a form you click on subform and a wizzard pops up.
You can use an existing form or choose fields from existing tables and
queries. I did the later. So in the data=>source object properties of
my subform I had a select statement. I then created a real form called
it Form1. It had the same contents as my prevous subform/select
statement. But thistime the data=>source object had "Form1". After that
the above code worked.