No Value Given for one or more required parameters error 1

[scottetombleson]

What the heck am I doing wrong here? It seems so simple and I just can't figure it out.

I Keep gitting the "No Value Given for one or more required parameters" error.


My strSQL is "SELECT qrystCONTIG2.*, qrystCONTIG2.BEDID FROM qrystCONTIG2 WHERE (((qrystCONTIG2.BEDID) = 33) OR ((qrystCONTIG2.BEDID) = 34) OR ((qrystCONTIG2.BEDID) = 35) OR ((qrystCONTIG2.BEDID) = 36));"

This error happens when I try to open the recordset.

        Set rst8 = New ADODB.Recordset
        rst8.CursorType = adOpenDynamic
        rst8.LockType = adLockOptimistic
        rst8.ActiveConnection = CurrentProject.Connection
        rst8.Open strSQL, , , , adCmdText
        rst8.MoveFirst

I've double-triple checked the field names and they are correct.

Thanks in advance for any help. I appreciate it.

Scott

 

[PHV]

Is by chance qrystCONTIG2 a parametized query ?
Does qrystCONTIG2 return a field named BEDID ?
Anyway, you may simplify your SQL like this:
strSQL = "SELECT * FROM qrystCONTIG2 WHERE BEDID In (33, 34, 35, 36)"
Or like this:
strSQL = "SELECT * FROM qrystCONTIG2 WHERE BEDID Between 33 And 36"

Can you please post the SQL code of qrystCONTIG2 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scottetombleson]

strSQL gets populated from a multi select list box using the following

Dim frm As Form
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
    Set ctl = Me!lstREGISTRATIONS
    strSQL = "SELECT qrystCONTIG2.*, qrystCONTIG2.BEDID FROM qrystCONTIG2 WHERE (((qrystCONTIG2.BEDID) = "
    
   For Each varItem In ctl.ItemsSelected
        strSQL = strSQL & ctl.ItemData(varItem) & ") OR ((qrystCONTIG2.BEDID) = "
    Next varItem
    'Trim the end of strSQL
    strSQL = Left$(strSQL, Len(strSQL) - 28)
    strSQL = strSQL + ");"

The code for qrystCONTIG2 is

SELECT FamilyMembers.FirstName, FamilyMembers.MiddleInit, FamilyMembers.LastName, FamilyMembers.Birth_Date, Registrations.RegArrDte, Registrations.RegDepDte, Registrations.ASSROOM, tblRES.BEDID, Registrations.FamilyId, tblBEDS.ROOMID, Registrations.RegistrationID
FROM ((FamilyMembers RIGHT JOIN Registrations ON FamilyMembers.FamMemID = Registrations.FamMemID) LEFT JOIN tblRES ON FamilyMembers.FamMemID = tblRES.FAMMEMID) LEFT JOIN tblBEDS ON tblRES.BEDID = tblBEDS.BEDID
WHERE (((Registrations.ProgramID)=[Forms]![frmstCONTIG].[ProgramID]));

Thanks for your quick reply and help PHV!

 

[PHV]

So, your required parameter is here:
[Forms]![frmstCONTIG].[ProgramID]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scottetombleson]

That control is populated with an appropriate value.


Thanks for sticking with me here PHV!

 

[PHV]

That control is populated with an appropriate value
If frmstCONTIG is an open mainform then a saved query may grab the value, but a Recordset can't ...
You may consider to play with the Parameters collection of the ADODB.Command object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[scottetombleson]

BRILLIANT! I, however, am not. Could you give me a bit more guidance as to how I might accomplish what i need to do?

Thanks PVH! You ROCK!

Scott

 

[scottetombleson]

PHV Got it Thanks for your help!

 

[PHV]

Glad you solved your issue.
For the benefit of the members can you please post your solution ?