record set. too few parameters set

[shutzy]

i have tried to do this record set all on my own but i am struggling. i get an error that causes me to have to shut down access through task manager. luckily i have learnt to save it before i test it(experience)

'open record set'
Dim rs As DAO.Recordset
On Error GoTo Err_Proc
'Check To See If Client Has Has Treatment Before'
Set rs = CurrentDb.OpenRecordset("SELECT tblOrders.ClientDetailsID, tblItems.ItemsID FROM tblItems INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) ON tblItems.ItemsID = tblOrdersItems.ItemsID where (((tblOrders.Status) = 3)) GROUP BY tblOrders.ClientDetailsID, tblItems.ItemsID HAVING (((tblOrders.ClientDetailsID)=[Forms]![frmDepartures]![ClientDetailsID]));")
If rs.RecordCount <> 0 Then
Do While Not rs.EOF
If rs.Fields(ItemsID) = Me.ItemsID Then
MsgBox "The Client Has Had This Treatment Beofre!"
Me.checkPaidWithLoyaltyPoints.Value = False
Exit Sub
End If
rs.MoveNext
Loop
End If
 
Exit_Proc:
rs.Close
Set rs = Nothing
Exit Sub
Err_Proc:
MsgBox Err.Number & " " & Err.Description
Resume Exit_Proc

can anyone see any faults with what i have done. im in no rush

the error message is

3061. too few parameters. expected 1


thank you

 

[pr2-eugin]

can anyone see any faults with what i have done. im in no rush

I can ! You need to concatenate variables to the SQL statement. Also, make your SQL more readable by inserting line breaks, when compiling a complicated query assign it to a variable so debugging can be easier.

Dim mySQL As String
mySQL = "SELECT tblOrders.ClientDetailsID, tblItems.ItemsID FROM tblItems " & _
        "INNER JOIN (tblOrders INNER JOIN tblOrdersItems ON tblOrders.OrderID = tblOrdersItems.OrderID) " & _
        "ON tblItems.ItemsID = tblOrdersItems.ItemsID WHERE (tblOrders.Status = 3) " & _
        "GROUP BY tblOrders.ClientDetailsID, tblItems.ItemsID " & _
        "HAVING (tblOrders.ClientDetailsID =[COLOR=Red][B] " & [Forms]![frmDepartures]![ClientDetailsID] &[/B][/COLOR] ");"
Set rs = CurrentDb.OpenRecordset(mySQL)

 

[shutzy]

thank you for the reply. infact it is a copy of a record set that you have done for me in the past. you helped me out with this about a year ago. the reason i dont do the sql like that is that i am not to good with them laid out that way. i prefer to put them in one long line. i will give it a try.

wasnt sure if you was still on this forum, glad you are!!

 

[shutzy]

thank you. it works.

 

[pr2-eugin]

Glad to help !

wasnt sure if you was still on this forum, glad you are!!

Yes I am ! Trying to learn something new everyday

 

[shutzy]

one more thing to get the cogs ticking. the record set seems to exit sub
Exit_Proc:
rs.Close
Set rs = Nothing
Exit Sub
i have some more vba that i want to continue after this. how do i take out the exit sub without getting an error.

thank you