Problem with fConcatChild()

[Steve S]

A few days ago Allen Browne responded to a question I posted about how to
concatenate fields from sequential records and he referred me to the function
fConcatChild written by Dev Ashish. this function is great and works as
advertised. I have it working with a table but when i try to substitute a
query for the M sided table I get the error "To few parameters, expected 1"
This message seems to be coming from the line 'set rs.db =(.....'

won't this function work with queries as well as tables?

the sql string that is generated ( I captured it via a MsgBox) works when
pasted into a query and executed as SQL . It gives me expected record set
just not in the format I need ie concatenated.

Any help appreciated

 

[Allen Browne]

The request for a parameter means there is a name in the query statement
that Access doesn't recognise.

Add the line:
Debug.Print strSql
just above the OpenRecordset line.
When it fails, open the Immediate Window (Ctrl+G).
See if you can identify which names are wrong.

If you can't see it, create a new query, paste the SQL statement in, and run
the query. The parameter dialog will name the parameters it can't find.

 

[Steve S]

FYI I am running the function from the Immediate Window but that should work
shouldn't it?

 

[Steve S]

I did exactly what you suggested and the sql pasted into a new query gives me
what I expected.

Capps
Meis
Stewart

That is what I got before when I captured ths sql string with a MsgBox. The
sql string is working. I double checked the function to be sure I had not
changed something within it and it still works on a form that uses a table as
a parmater to the fucntion. the forms results were correct. ???

Steve

 

[Allen Browne]

Post the SQL statment as printed in the Debug window.

Perhaps it contains something like [Forms].[Form1].[Text0] that works
(courtesy of the Expression Service) in a query but not in OpenRecordset.

Or perhaps you need to explicitly save the record in the form before calling
the function to get the results you expect.

 

[Steve S]

thanks for looking at this Allen. Here is the results from running the
function from the immediate window.

?fconcatchild("T C Linc","T ID","C ID","Long",1466)
Select [C ID] From [T C Linc] Where [T ID] = 1466
2032, 1077, 955, 936, 1975, 942
Generated expected results

?fconcatchild("Build Contest Judges","T ID","C ID","Long",1466)
Select [C ID] From [Build Contest Judges] Where [T ID] = 1466
Generated error ‘Too few parameters, expected 1’
------------------------------------------------------------------------------------------
Table [T C Linc] has 2 fields; [T ID] (Twirler ID) long and [C ID] (Coach
ID) long

Query [Build Contest Judges] is a subset of [T C Linc] limited to only
judges for this contest.

I have verified that the [T ID] of 1466 exist in both the table and the
query dynaset.

If I change the query to a Make Table query and make a table exactly like
the query the function works OK and gives the expected results.

Thanks for any help.

Post the SQL statment as printed in the Debug window.

Perhaps it contains something like [Forms].[Form1].[Text0] that works
(courtesy of the Expression Service) in a query but not in OpenRecordset.

Or perhaps you need to explicitly save the record in the form before calling
the function to get the results you expect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did exactly what you suggested and the sql pasted into a new query gives
me
what I expected.

Capps
Meis
Stewart

That is what I got before when I captured ths sql string with a MsgBox.
The
sql string is working. I double checked the function to be sure I had not
changed something within it and it still works on a form that uses a table
as
a parmater to the fucntion. the forms results were correct. ???

Steve

 

[Allen Browne]

Okay so there is some name in the 2nd query that Access doesn't get, and our
goal is to pin down which name that is.

Firstly, uncheck the Name AutoCorrect boxes under:
Tools | Options | General
and then compact the database:
Tools | Database Utilities | Compact/Repair
In Access 2007, under:
Office Button | Access Options | Current Database
then:
Office Button | Manage | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

Next, we ask Access to help with the troubleshooting:
1. Create a new query.
2. Switch to SQL view.
3. Paste in:
Select [C ID] From [Build Contest Judges] Where [T ID] = 1466
4. Run the query. Does it ask for a parameter? What name?

If that doesn't solve it, save that query with a name such as qryDebug.
Then open the Immediate Window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("qryDebug").Parameters.Count
If it says there is one, try:
? CurrentDb.QueryDefs("qryDebug").Parameters(0).Name

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for looking at this Allen. Here is the results from running the
function from the immediate window.

?fconcatchild("T C Linc","T ID","C ID","Long",1466)
Select [C ID] From [T C Linc] Where [T ID] = 1466
2032, 1077, 955, 936, 1975, 942
Generated expected results

?fconcatchild("Build Contest Judges","T ID","C ID","Long",1466)
Select [C ID] From [Build Contest Judges] Where [T ID] = 1466
Generated error ‘Too few parameters, expected 1’
------------------------------------------------------------------------------------------
Table [T C Linc] has 2 fields; [T ID] (Twirler ID) long and [C ID] (Coach
ID) long

Query [Build Contest Judges] is a subset of [T C Linc] limited to only
judges for this contest.

I have verified that the [T ID] of 1466 exist in both the table and the
query dynaset.

If I change the query to a Make Table query and make a table exactly like
the query the function works OK and gives the expected results.

Thanks for any help.

Post the SQL statment as printed in the Debug window.

Perhaps it contains something like [Forms].[Form1].[Text0] that works
(courtesy of the Expression Service) in a query but not in OpenRecordset.

Or perhaps you need to explicitly save the record in the form before
calling
the function to get the results you expect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did exactly what you suggested and the sql pasted into a new query
gives
me
what I expected.

Capps
Meis
Stewart

That is what I got before when I captured ths sql string with a MsgBox.
The
sql string is working. I double checked the function to be sure I had
not
changed something within it and it still works on a form that uses a
table
as
a parmater to the fucntion. the forms results were correct. ???

Steve




:

The request for a parameter means there is a name in the query
statement
that Access doesn't recognise.

Add the line:
Debug.Print strSql
just above the OpenRecordset line.
When it fails, open the Immediate Window (Ctrl+G).
See if you can identify which names are wrong.

If you can't see it, create a new query, paste the SQL statement in,
and
run
the query. The parameter dialog will name the parameters it can't
find.

A few days ago Allen Browne responded to a question I posted about
how
to
concatenate fields from sequential records and he referred me to the
function
fConcatChild written by Dev Ashish. this function is great and
works
as
advertised. I have it working with a table but when i try to
substitute a
query for the M sided table I get the error "To few parameters,
expected
1"
This message seems to be coming from the line 'set rs.db =(.....'

won't this function work with queries as well as tables?

the sql string that is generated ( I captured it via a MsgBox) works
when
pasted into a query and executed as SQL . It gives me expected
record
set
just not in the format I need ie concatenated.

 

[Steve S]

Getting close. see my responces imbedded in your most recent post.

Okay so there is some name in the 2nd query that Access doesn't get, and our
goal is to pin down which name that is.

Firstly, uncheck the Name AutoCorrect boxes under:
Tools | Options | General
and then compact the database:
Tools | Database Utilities | Compact/Repair

******** Name AutoCorrect was turned off months ago but I verified ******
******** that just to be sure
******

In Access 2007, under:
Office Button | Access Options | Current Database
then:
Office Button | Manage | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

Next, we ask Access to help with the troubleshooting:
1. Create a new query.
2. Switch to SQL view.
3. Paste in:
Select [C ID] From [Build Contest Judges] Where [T ID] = 1466
4. Run the query. Does it ask for a parameter? What name?

**** No the query works just fine as a standalone *****'

If that doesn't solve it, save that query with a name such as qryDebug.
Then open the Immediate Window (Ctrl+ G) and enter:
? CurrentDb.QueryDefs("qryDebug").Parameters.Count
If it says there is one, try:
? CurrentDb.QueryDefs("qryDebug").Parameters(0).Name

**** Name shows Forms![Menu2]![Contest ID]

**** that is the problem as noted in a previous post. I hardcoded a
constant **
**** in the query and the OpenRecordset worked OK and I got the 3 values ***
**** concatenated as I expected. The problem now is how do I code a ***
*** variable criteria in the query so OpenRecordset doesn't choke???

Steve

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for looking at this Allen. Here is the results from running the
function from the immediate window.

?fconcatchild("T C Linc","T ID","C ID","Long",1466)
Select [C ID] From [T C Linc] Where [T ID] = 1466
2032, 1077, 955, 936, 1975, 942
Generated expected results

?fconcatchild("Build Contest Judges","T ID","C ID","Long",1466)
Select [C ID] From [Build Contest Judges] Where [T ID] = 1466
Generated error ‘Too few parameters, expected 1’
------------------------------------------------------------------------------------------
Table [T C Linc] has 2 fields; [T ID] (Twirler ID) long and [C ID] (Coach
ID) long

Query [Build Contest Judges] is a subset of [T C Linc] limited to only
judges for this contest.

I have verified that the [T ID] of 1466 exist in both the table and the
query dynaset.

If I change the query to a Make Table query and make a table exactly like
the query the function works OK and gives the expected results.

Thanks for any help.

Post the SQL statment as printed in the Debug window.

Perhaps it contains something like [Forms].[Form1].[Text0] that works
(courtesy of the Expression Service) in a query but not in OpenRecordset.

Or perhaps you need to explicitly save the record in the form before
calling
the function to get the results you expect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did exactly what you suggested and the sql pasted into a new query
gives
me
what I expected.

Capps
Meis
Stewart

That is what I got before when I captured ths sql string with a MsgBox.
The
sql string is working. I double checked the function to be sure I had
not
changed something within it and it still works on a form that uses a
table
as
a parmater to the fucntion. the forms results were correct. ???

Steve




:

The request for a parameter means there is a name in the query
statement
that Access doesn't recognise.

Add the line:
Debug.Print strSql
just above the OpenRecordset line.
When it fails, open the Immediate Window (Ctrl+G).
See if you can identify which names are wrong.

If you can't see it, create a new query, paste the SQL statement in,
and
run
the query. The parameter dialog will name the parameters it can't
find.

A few days ago Allen Browne responded to a question I posted about
how
to
concatenate fields from sequential records and he referred me to the
function
fConcatChild written by Dev Ashish. this function is great and
works
as
advertised. I have it working with a table but when i try to
substitute a
query for the M sided table I get the error "To few parameters,
expected
1"
This message seems to be coming from the line 'set rs.db =(.....'

won't this function work with queries as well as tables?

the sql string that is generated ( I captured it via a MsgBox) works
when
pasted into a query and executed as SQL . It gives me expected
record
set
just not in the format I need ie concatenated.

 

[Allen Browne]

Right: as mentioned, OpenRecordset() does not have access to the Expression
Service the way the query itself does so that won't work.

Perhaps you could modify the function so it takes an extra optional argument
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant
Optional strWhere As String) _
As String

Then concatenate it into strSql after the End Select:
If strWhere <> vbNullString Then
strSql = strSql & " AND (" & strWhere & ")"
End If

Then instead of using the query at all, you can include the the last
argument:
"[SomeField] = " & Forms![Menu2]![Contest ID]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Getting close. see my responces imbedded in your most recent post.

Okay so there is some name in the 2nd query that Access doesn't get, and
our
goal is to pin down which name that is.

Firstly, uncheck the Name AutoCorrect boxes under:
Tools | Options | General
and then compact the database:
Tools | Database Utilities | Compact/Repair

******** Name AutoCorrect was turned off months ago but I verified ******
******** that just to be sure
******

In Access 2007, under:
Office Button | Access Options | Current Database
then:
Office Button | Manage | Compact/Repair
Explanation of why:
http://allenbrowne.com/bug-03.html

Next, we ask Access to help with the troubleshooting:
1. Create a new query.
2. Switch to SQL view.
3. Paste in:
Select [C ID] From [Build Contest Judges] Where [T ID] = 1466
4. Run the query. Does it ask for a parameter? What name?

**** No the query works just fine as a standalone *****'

If that doesn't solve it, save that query with a name such as qryDebug.
Then open the Immediate Window (Ctrl+ G) and enter:
? CurrentDb.QueryDefs("qryDebug").Parameters.Count
If it says there is one, try:
? CurrentDb.QueryDefs("qryDebug").Parameters(0).Name

**** Name shows Forms![Menu2]![Contest ID]

**** that is the problem as noted in a previous post. I hardcoded a
constant **
**** in the query and the OpenRecordset worked OK and I got the 3 values
***
**** concatenated as I expected. The problem now is how do I code a
***
*** variable criteria in the query so OpenRecordset doesn't choke???

Steve

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

thanks for looking at this Allen. Here is the results from running the
function from the immediate window.

?fconcatchild("T C Linc","T ID","C ID","Long",1466)
Select [C ID] From [T C Linc] Where [T ID] = 1466
2032, 1077, 955, 936, 1975, 942
Generated expected results

?fconcatchild("Build Contest Judges","T ID","C ID","Long",1466)
Select [C ID] From [Build Contest Judges] Where [T ID] = 1466
Generated error ‘Too few parameters, expected 1’
------------------------------------------------------------------------------------------
Table [T C Linc] has 2 fields; [T ID] (Twirler ID) long and [C ID]
(Coach
ID) long

Query [Build Contest Judges] is a subset of [T C Linc] limited to only
judges for this contest.

I have verified that the [T ID] of 1466 exist in both the table and the
query dynaset.

If I change the query to a Make Table query and make a table exactly
like
the query the function works OK and gives the expected results.

Thanks for any help.



:

Post the SQL statment as printed in the Debug window.

Perhaps it contains something like [Forms].[Form1].[Text0] that works
(courtesy of the Expression Service) in a query but not in
OpenRecordset.

Or perhaps you need to explicitly save the record in the form before
calling
the function to get the results you expect.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I did exactly what you suggested and the sql pasted into a new query
gives
me
what I expected.

Capps
Meis
Stewart

That is what I got before when I captured ths sql string with a
MsgBox.
The
sql string is working. I double checked the function to be sure I
had
not
changed something within it and it still works on a form that uses a
table
as
a parmater to the fucntion. the forms results were correct. ???

Steve




:

The request for a parameter means there is a name in the query
statement
that Access doesn't recognise.

Add the line:
Debug.Print strSql
just above the OpenRecordset line.
When it fails, open the Immediate Window (Ctrl+G).
See if you can identify which names are wrong.

If you can't see it, create a new query, paste the SQL statement
in,
and
run
the query. The parameter dialog will name the parameters it can't
find.

A few days ago Allen Browne responded to a question I posted about
how
to
concatenate fields from sequential records and he referred me to
the
function
fConcatChild written by Dev Ashish. this function is great and
works
as
advertised. I have it working with a table but when i try to
substitute a
query for the M sided table I get the error "To few parameters,
expected
1"
This message seems to be coming from the line 'set rs.db
=(.....'

won't this function work with queries as well as tables?

the sql string that is generated ( I captured it via a MsgBox)
works
when
pasted into a query and executed as SQL . It gives me expected
record
set
just not in the format I need ie concatenated.