Problem with fConcatChild

Hi,

I was hoping fConcatChild would be the answer to my concatenation problem I
am receiving really strange results.

Here's my 1:M data (Directive-Number is in tblMaxDirective and GSWT# is in
tblGSWT):

DIRECTIVE-NUMBER GSWT#
115451 34
125061 90
125061 3
125143 51
125253 36
129069 12
129161 90
129161 37
136147 30
154981 30
166259 90
166259 31

Here's my query to concatenate:

SELECT tblMaxDirective.[DIRECTIVE-NUMBER], fConcatChild("tblGSWT","Directive-
Number","GSWT#","string",[Directive-Number]) AS GNums
FROM tblMaxDirective;

Here's the weird results I get:

DIRECTIVE-NUMBER GNums
115451 34
125061 3;90;3;90;3;90
125143 51
125253 36;36;36
129069 12
129161 37;90
136147 30
154981 30
166259 31;90;31;90;31;90;31;90

Any ideas why I am getting repeated instances of the GSWT# for Directive-
Numbers = 125061, 125253, and 166259?

I am totally stumped.

Here's my standard module code for fConcatChild:

Option Compare Database

'************ 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 **********


Please help!

dalecorey1

--
Sincerely,

Dale Corey

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1

Try setting a break point after the line

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

and look in the Variables window to see what's in rs. If that looks ok,
then step through the loop and see what's going on there (try debug.print
statements).

HTH;

Amy



"dalecorey1 via AccessMonster.com" u23739@uwe wrote in message
news:86e41cc8548c6@uwe...
Hi,

I was hoping fConcatChild would be the answer to my concatenation problem
I
am receiving really strange results.

Here's my 1:M data (Directive-Number is in tblMaxDirective and GSWT# is in
tblGSWT):

DIRECTIVE-NUMBER GSWT#
115451 34
125061 90
125061 3
125143 51
125253 36
129069 12
129161 90
129161 37
136147 30
154981 30
166259 90
166259 31

Here's my query to concatenate:

SELECT tblMaxDirective.[DIRECTIVE-NUMBER],
fConcatChild("tblGSWT","Directive-
Number","GSWT#","string",[Directive-Number]) AS GNums
FROM tblMaxDirective;

Here's the weird results I get:

DIRECTIVE-NUMBER GNums
115451 34
125061 3;90;3;90;3;90
125143 51
125253 36;36;36
129069 12
129161 37;90
136147 30
154981 30
166259 31;90;31;90;31;90;31;90

Any ideas why I am getting repeated instances of the GSWT# for Directive-
Numbers = 125061, 125253, and 166259?

I am totally stumped.

Here's my standard module code for fConcatChild:

Option Compare Database

'************ 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 **********


Please help!

dalecorey1

--
Sincerely,

Dale Corey

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/200807/1