No value given for one or more required parameters

S

shank

I have the below query and function.

Error: No value given for one or more required parameters.

I'm using his code in another database that runs fine. Can anyone see what
I'm missing here?

thanks
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UPDATE [Products Local] INNER JOIN [NEW ItemTitles] ON [Products
Local].ProductNo = [NEW ItemTitles].OrderNo SET [Products Local].LongDesc =
Concatenate("SELECT Artist FROM [NEW ItemTitles] WHERE [NEW
ItemTitles]![OrderNo] = " & [Products Local]![ProductNo] & " ORDER BY [NEW
ItemTitles]![SortKey] ",", ");
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
These lines highlighted in yellow...
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
 
S

shank

then copy result and paste into new query SQL window<<
What results? I inserted ?pstrSQL into the Immediate Window, ran query, and
I just get the same END/DEBUG error dialog box. I click DEBUG and get the
same yellow highlighted text.

Please explain the exact steps I should do after I paste ?pstrSQL.
thanks
- - - - - - - - - - - - - - - - - - - - - - - - - - - -

Alex Dybenko said:
Hi,
once you get this error - go to immediate window (ctrl+G), type there:
?pstrSQL
then copy result and paste into new query SQL window. try to run it -
access will tell you what is missing there

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


shank said:
I have the below query and function.

Error: No value given for one or more required parameters.

I'm using his code in another database that runs fine. Can anyone see
what I'm missing here?

thanks
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UPDATE [Products Local] INNER JOIN [NEW ItemTitles] ON [Products
Local].ProductNo = [NEW ItemTitles].OrderNo SET [Products Local].LongDesc
= Concatenate("SELECT Artist FROM [NEW ItemTitles] WHERE [NEW
ItemTitles]![OrderNo] = " & [Products Local]![ProductNo] & " ORDER BY
[NEW ItemTitles]![SortKey] ",", ");
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
These lines highlighted in yellow...
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
Click to expand...
Click to expand...
 
A

Alex Dybenko

when you on yellow highlighted text:

press ctrl+G
type there:
?pstrSQL
hit enter
you will see the content of your variable
now you can copy it and paste to query SQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com



shank said:
What results? I inserted ?pstrSQL into the Immediate Window, ran query,
and I just get the same END/DEBUG error dialog box. I click DEBUG and get
the same yellow highlighted text.

Please explain the exact steps I should do after I paste ?pstrSQL.
thanks
- - - - - - - - - - - - - - - - - - - - - - - - - - - -

Alex Dybenko said:
Hi,
once you get this error - go to immediate window (ctrl+G), type there:
?pstrSQL
then copy result and paste into new query SQL window. try to run it -
access will tell you what is missing there

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


shank said:
I have the below query and function.

Error: No value given for one or more required parameters.

I'm using his code in another database that runs fine. Can anyone see
what I'm missing here?

thanks
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
UPDATE [Products Local] INNER JOIN [NEW ItemTitles] ON [Products
Local].ProductNo = [NEW ItemTitles].OrderNo SET [Products
Local].LongDesc = Concatenate("SELECT Artist FROM [NEW ItemTitles]
WHERE [NEW ItemTitles]![OrderNo] = " & [Products Local]![ProductNo] &
" ORDER BY [NEW ItemTitles]![SortKey] ",", ");
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
These lines highlighted in yellow...
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = "") _
As String
'Created by Duane Hookom, 2003
'this code may be included in any application/mdb providing
' this statement is left intact
'example
'tblFamily with FamID as numeric primary key
'tblFamMem with FamID, FirstName, DOB,...
'return a comma separated list of FirstNames
'for a FamID
' John, Mary, Susan
'in a Query
'SELECT FamID,
'Concatenate("SELECT FirstName FROM tblFamMem
' WHERE FamID =" & [FamID]) as FirstNames
'FROM tblFamily
'

'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
'Dim db As DAO.Database
'Dim rs As DAO.Recordset
'Set db = CurrentDb
'Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
Dim rs As New ADODB.Recordset
rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
'Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

user defined not defined 1
Concatenation Function 12
Concatenate Function 6
Concatenate hierarchy 1
character limits 4
Concatenating not working with primary key as text field 2
concatenate function 6
Undefined function 'concatenate' in expression 0

Top