fConcatChild issues

[Claire]

I am trying to use fConcatChild and am unsuccessful.
I have a query that uses a value from a form along the way that has a list
of scheduled tests during a certain month (qryScheduling5). These all have a
CustNo along with site information and a scheduled date.

There is also a table (Customers) that has a CustNo primary key and various
contact information that I am going to use in a mail merge.

There may be 0, 1, or many scheduled dates for each CustNo. I would like to
concatenate the dates into one line so that I can use it one one page of a
mail merge.

Right now I am getting no values. If I comment out the first On Error
(after the Dim statements) the code gets stuck at Set rs =
db.OpenRecordset(strSQL, dbOpenSnapshot), with "Too few parameters. Expected
2." This sure looks like two parameters to me, and if I hover over both
strSQL and dbOpenSnapshot they each have values.

Thank you for any suggestions you have- hitting my head on the desk has
proven to be unfruitful.

~Claire

My SQL statement reads:
SELECT Customers.CustName, Customers.CustomerName2, Customers.CustAddress,
Customers.CustCity, Customers.CustState, Customers.CustZip,
Customers.CustomerContactTitle, Customers.CustContact,
fConcatChild("qryScheduling5","CustNo","SiteName","String",[CustNo]) AS Days
FROM Customers;

The code reads:
'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

 

[Jeanette Cunningham]

Hi Claire,
here's how to trouble shoot the 'too few parameters' error.
Put a debug.print line after your sql statement like this-->


SELECT Customers.CustName, Customers.CustomerName2, Customers.CustAddress,
Customers.CustCity, Customers.CustState, Customers.CustZip,
Customers.CustomerContactTitle, Customers.CustContact,
fConcatChild("qryScheduling5","CustNo","SiteName","String",[CustNo]) AS Days
FROM Customers;
Debug.Print SQL

Note: replace SQL with whatever variable you have used for the sql
statement.

Now run the form and code.
Open the immediate window and see what access has put for sql.
Copy the sql from the immediate window to a new query in sql view.
Switch the query to datasheet view to see what error messages access gives
you.




Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I am trying to use fConcatChild and am unsuccessful.
I have a query that uses a value from a form along the way that has a list
of scheduled tests during a certain month (qryScheduling5). These all
have a
CustNo along with site information and a scheduled date.

There is also a table (Customers) that has a CustNo primary key and
various
contact information that I am going to use in a mail merge.

There may be 0, 1, or many scheduled dates for each CustNo. I would like
to
concatenate the dates into one line so that I can use it one one page of a
mail merge.

Right now I am getting no values. If I comment out the first On Error
(after the Dim statements) the code gets stuck at Set rs =
db.OpenRecordset(strSQL, dbOpenSnapshot), with "Too few parameters.
Expected
2." This sure looks like two parameters to me, and if I hover over both
strSQL and dbOpenSnapshot they each have values.

Thank you for any suggestions you have- hitting my head on the desk has
proven to be unfruitful.

~Claire

My SQL statement reads:
SELECT Customers.CustName, Customers.CustomerName2, Customers.CustAddress,
Customers.CustCity, Customers.CustState, Customers.CustZip,
Customers.CustomerContactTitle, Customers.CustContact,
fConcatChild("qryScheduling5","CustNo","SiteName","String",[CustNo]) AS
Days
FROM Customers;

The code reads:
'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

 

[Daryl S]

Claire -

The function looks fine.

Is the CustNo field a string field? That is what you are indicating in the
function call. If it is really an autonumber or Long field, then you need to
change your function call in the query to this:

fConcatChild("qryScheduling5","CustNo","SiteName","Long",[CustNo]) AS Days

If CustNo is really a string field, then I would check the field name
"SiteName" to make sure there isn't a typo in it. Don't you want the
scheduled date field there instead anyway?

--
Daryl S

I am trying to use fConcatChild and am unsuccessful.
I have a query that uses a value from a form along the way that has a list
of scheduled tests during a certain month (qryScheduling5). These all have a
CustNo along with site information and a scheduled date.

There is also a table (Customers) that has a CustNo primary key and various
contact information that I am going to use in a mail merge.

There may be 0, 1, or many scheduled dates for each CustNo. I would like to
concatenate the dates into one line so that I can use it one one page of a
mail merge.

Right now I am getting no values. If I comment out the first On Error
(after the Dim statements) the code gets stuck at Set rs =
db.OpenRecordset(strSQL, dbOpenSnapshot), with "Too few parameters. Expected
2." This sure looks like two parameters to me, and if I hover over both
strSQL and dbOpenSnapshot they each have values.

Thank you for any suggestions you have- hitting my head on the desk has
proven to be unfruitful.

~Claire

My SQL statement reads:
SELECT Customers.CustName, Customers.CustomerName2, Customers.CustAddress,
Customers.CustCity, Customers.CustState, Customers.CustZip,
Customers.CustomerContactTitle, Customers.CustContact,
fConcatChild("qryScheduling5","CustNo","SiteName","String",[CustNo]) AS Days
FROM Customers;

The code reads:
'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

 

[Claire]

Thanks for the help, Daryl. I am going to run this function both for
SiteName and for ScheduledDate. Sorry about putting in the example for one
and the code for the other! CustNo is a text field (some have letters in
them), so I can't see any reason it would be anything but a string.

I have tried using the immediate window and printing variables as it goes.
One of the strSQL reads as follows:

Select [ScheduledDate] From [qryScheduling5] Where [CustNo] = '10001'

I don't know enough to know if it should have the single quotes around the
CustNo.

It looks like the function is getting stuck on the following line:
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
I can debug.print something before that line, but nothing after it. And the
function is not getting to the point where it actually concats.

Any thoughts about what's going on in the line that could be the problem?

Thanks,
Claire

Claire -

The function looks fine.

Is the CustNo field a string field? That is what you are indicating in the
function call. If it is really an autonumber or Long field, then you need to
change your function call in the query to this:

fConcatChild("qryScheduling5","CustNo","SiteName","Long",[CustNo]) AS Days

If CustNo is really a string field, then I would check the field name
"SiteName" to make sure there isn't a typo in it. Don't you want the
scheduled date field there instead anyway?

--
Daryl S

I am trying to use fConcatChild and am unsuccessful.
I have a query that uses a value from a form along the way that has a list
of scheduled tests during a certain month (qryScheduling5). These all have a
CustNo along with site information and a scheduled date.

There is also a table (Customers) that has a CustNo primary key and various
contact information that I am going to use in a mail merge.

There may be 0, 1, or many scheduled dates for each CustNo. I would like to
concatenate the dates into one line so that I can use it one one page of a
mail merge.

Right now I am getting no values. If I comment out the first On Error
(after the Dim statements) the code gets stuck at Set rs =
db.OpenRecordset(strSQL, dbOpenSnapshot), with "Too few parameters. Expected
2." This sure looks like two parameters to me, and if I hover over both
strSQL and dbOpenSnapshot they each have values.

Thank you for any suggestions you have- hitting my head on the desk has
proven to be unfruitful.

~Claire

My SQL statement reads:
SELECT Customers.CustName, Customers.CustomerName2, Customers.CustAddress,
Customers.CustCity, Customers.CustState, Customers.CustZip,
Customers.CustomerContactTitle, Customers.CustContact,
fConcatChild("qryScheduling5","CustNo","SiteName","String",[CustNo]) AS Days
FROM Customers;

The code reads:
'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

 

[Claire]

FYI:
The function is looping through the CustNo enough to write a separate strSQL
for each CustNo, and it does so twice, despite the fact that the function can
not make it past Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot). I have
NO IDEA when that happens. Or why it's repeating it twice, even when there is
only one record for the CustNo. If there are three scheduled dates it's
writing the same strSQL 6 times, not three as I would expect.

 

[Claire]

After racking my brain and books and debeug.print-ing in a bazillion places,
it appears the problem is that my query depends on a form. Openrecordset
would work if I used a table or a query that didn't have any paramenters, but
not for the query to which I was referring. Apparently DAO doesn't pass in
those values. Now to see if I can get around this issue, but at least I
found out what the issue is!

 

[Daryl S]

Claire -

Good for you and your debugging skills!
Is there anything else you need on this?

 

[Claire]

Sorry about my delay on answering this one. Apparently Microsoft has decided
it doesn't want to notify me of replies anymore, and I hadn't tracked down
the thread again. I got past the parameters by adding another optional
element to the function and passing the form values in through that. It's
not that elegant, but it worked. So, we're good on this one for now.