Concatenate from a Combo Box

[Sidney]

Hi

I have a 1:many table set up. In the many table, the user uses a combo box
to make the selection. In a downstream form, I want a text field that will
show the choices from the many table in a comma delimited list. I found some
code to concatenate the choices from
http://www.mvps.org/access/modules/mdl0004.htm. I have this working fairly
close.

The problem I am having with it is that it displays the ID from the combo
box since that is the bound column. I cannot seem to modify the code
correctly to display the actual text from the combox box.

Can somebody help?

Thanks
Sidney

 

[Douglas J. Steele]

Are you saying that you're working directly with tables and that you've put
a combo box (aka a lookup field) in the table? That's your problem: that's
what's really stored in the table, not the text you're seeing. See
http://www.mvps.org/access/lookupfields.htm at "The Access Web" for other
reasons why lookup fields are a bad idea. As well, you should never be
working directly with tables: you should always be using forms.

If that's not the issue, then you'll have to explain a little more what
changes you've made to the sample code, since it doesn't rely on a combo box
in any way I can see.

 

[Sidney]

I will try to explain further. I have a form to log issues. In this form,
there is a subform so that a user can categorize the issue type. The user
can use a combo box to select multiple issue types. These types are then
stored in a different table, i.e. tblIssues (for the actual issue recorded)
and tblIssueType to record the types of issues.

I have a different form that summaries the issues. I would like to display
the Issue type in a comma delimited list, which is what I was trying to get
working.

If I should not have users use a combo box to select discrete data to record
the Issue Type, how do you recommend that I do this?

Thank you
Sidney

 

[Douglas J. Steele]

I'm hard-pressed to understand your setup, since combo boxes only allow
single-select. (And I never suggested not to use combo boxes: combo boxes on
forms are fine, it's combo boxes in tables that aren't.)

Sounds to me as though the combo box is a red herring. You've got a table
that holds the data, so your code runs against that table: nothing to do
with the combo box.

So what does your code look like now?

 

[Sidney]

Hi Douglas

Here is the code that I am using. I got it from
http://www.mvps.org/access/modules/mdl0004.htm. I would like the results to
return something like this: Red, Blue, Green. However, it returns 8, 3, 10.
The numbers are the IDs from the bound column. (I have these items stored in
a different table since they can be determined by a user.) I already tried
modifying the code to use the "Column" attribute, but it would error out or
not return anything.

I have a main form where the user enters most of the data on an issue. I
need a way for the user to select multiple types to categorize the issue.
Originally I tried using a multi-select list box, but ran into issues with
it. After spending hours on trying to fix it, I decided to simply create a
sub form (set as a continuous form) with just the combo box to select the
issue type - this is stored in a different table than the issue. I then
embedded the subform in the main form. This allows the user to select
multiple issue types. I hope this helps explain the set up some more.

Thanks
Sidney

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
' Set a reference to DAO

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

 

[Douglas J. Steele]

You need to create a query that joins your two tables together to pick up
the descriptions and use that query rather than the table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi Douglas

Here is the code that I am using. I got it from
http://www.mvps.org/access/modules/mdl0004.htm. I would like the results
to
return something like this: Red, Blue, Green. However, it returns 8, 3,
10.
The numbers are the IDs from the bound column. (I have these items stored
in
a different table since they can be determined by a user.) I already
tried
modifying the code to use the "Column" attribute, but it would error out
or
not return anything.

I have a main form where the user enters most of the data on an issue. I
need a way for the user to select multiple types to categorize the issue.
Originally I tried using a multi-select list box, but ran into issues with
it. After spending hours on trying to fix it, I decided to simply create
a
sub form (set as a continuous form) with just the combo box to select the
issue type - this is stored in a different table than the issue. I then
embedded the subform in the main form. This allows the user to select
multiple issue types. I hope this helps explain the set up some more.

Thanks
Sidney

'************ Code Start **********
'This code was originally written by Dev Ashish
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'Code Courtesy of
'Dev Ashish
'
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
As String
'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity
'
' Set a reference to DAO

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";"
.MoveNext
Loop
End If
End With

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 1)

Exit_fConcatChild:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set db = Nothing
Exit Function

Err_fConcatChild:
Resume Exit_fConcatChild
End Function

'************ Code End **********

I'm hard-pressed to understand your setup, since combo boxes only allow
single-select. (And I never suggested not to use combo boxes: combo boxes
on
forms are fine, it's combo boxes in tables that aren't.)

Sounds to me as though the combo box is a red herring. You've got a table
that holds the data, so your code runs against that table: nothing to do
with the combo box.

So what does your code look like now?