Return multiple values from subform into single field

[Carina1628]

I have a main form which is a deed, and a subform which lists all the spaces associated with the deed. Each space has its own record. I want all the space numbers listed in a field on the main form. Basically what I want is:

List [SpacesSubform].[form]![space] where [SpacesSubform].[form]![deed#] = [frmDeedEntry].[form]![deed#]

When I used =[SpacesSubform].[form]![space] all it returned was the space number of the first record.

I want all the space numbers listed in a field on the main form.

Thanks,
Carina

 

[mas5]

I think I'm not understanding what you are looking for. Do you want to list all the spaces in a list box on the main form? I'm not clear on the relationship between these two forms.

 

[Carina1628]

The two forms are pulling data from the same table, just different levels of information. Each space has to have it's own record because it can be sold without selling all the other spaces. Ultimately what I want to do is print all the spaces on the deed like: "Space 001, 002, 003, 004"
Thanks for your help, Carina

 

[mas5]

I think I understand. The only way I can think of to do this is with Visual Basic. I can't think of a way to get the info from your subform since you really want to concatenate strings and not add numbers.

Use an event on your main form (perhaps on current since that event should happen with each new deed).

Set RST = New ADODB.Recordset

RST.ActiveConnection = CurrentProject.Connection
RST.CursorType = adOpenStatic
RST.LockType = adLockOptimistic
strsql = "Select space from yourtable where deed = " & me.deed#

RST.Open strsql

me.concatenatedspaces = "Space "

'*** Here you would need a loop to go through all the records that you have retrieved. I'm not sure of the proper syntax but you want to loop through and each time you add to your string:
me.concatenatedspaces = me.concatenatedspace & space & ", "
'*** Make sure you don't put a comma after the last one.
RST.Close
Set RST = Nothing


Hope this helps. I don't know if there is an easier way to concatenate the strings from within the subform. I'll try to look into it today, but it's not something I've ever tried.

 

[fneily]

Maybe you can modify this - it concatenates information within a field (column).

I made a table with two fields - partno, desc. Desc can span more than one record, eg. partno desc
abcd this is one desc
abcd this is two desc
xyz another desc
xyz more desc
etc. etc.

I want to concatenate the desc's into one field.

create a query that looks like this:
SELECT [partno], fConcatFld("parttable","partno","desc","string",[partno]) AS descs
FROM parttable
GROUP BY [partno];

Do ALT+F11, insert a new module and put the following code for the fConcatFld function:

Function fConcatFld(stTable As String, _
stForFld As String, _
stFldToConcat As String, _
stForFldType As String, _
vForFldVal As Variant) _
As String

'Usage Examples:
' ?fConcatFld(("Customers","ContactTitle","CustomerID", _
' "string","Owner&quot
'Where Customers = The parent Table
' ContactTitle = The field whose values to use for lookups
' CustomerID = Field name to concatenate
' string = DataType of ContactTitle field
' Owner = Value on which to return concatenated CustomerID
'
Dim lodb As Database, lors As DAO.Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String

On Error GoTo Err_fConcatFld

lovConcat = Null
Set lodb = CurrentDb


loSQL = "SELECT [" & stFldToConcat & "] FROM [" & stTable & "] WHERE [" & stForFld & "] = '" & vForFldVal & "' ;" 'place this line on one line in the VBA window.

Set lors = lodb.OpenRecordset(loSQL, dbOpenSnapshot)

'Are we sure that duplicates exist in stFldToConcat
With lors
If .RecordCount <> 0 Then
'start concatenating records
Do While Not .EOF
lovConcat = lovConcat & lors(stFldToConcat) & &quot; &quot;
.MoveNext
Loop
Else
GoTo Exit_fConcatFld
End If
End With

'Just Trim the trailing ;
fConcatFld = Left(lovConcat, Len(lovConcat) - 1)


Exit_fConcatFld:
Set lors = Nothing: Set lodb = Nothing
Exit Function

Err_fConcatFld:
MsgBox &quot;Error#: &quot; & Err.Number & vbCrLf & Err.description
Resume Exit_fConcatFld
End Function

Run the query, which calls the function. Besure in your VBA window, click on Tools , the References and make sure the DAO library is referenced first.

Neil