SQL parameter problem

[lxh]

Hi all,

I have an SQL statement that is giving me some trouble. I've had to re-write it from a working piece of code as I discovered that when the I was opening a new record I got an error. Having written the SQL and checked the it in a query I thought it would work but, transfering it to code I have an error.

Can any one dee teh problem? or has anyone sufferd the same problem and know how to cure it?

Thanks for any replies
Lex

I get the error

'error 3061: Too few parameters. Expected 2'

This is the code, I've checked the SQl in a query and it runs fine. I'm stumped as to what the problem could be.

Private Sub Form_Open(Cancel As Integer)
Dim MySQL As String,Dim rs As DAO.Recordset
Dim pfix As String
Dim MyForm As String
pfix = "NA"

MySQL = "SELECT IIf(IsNull(ProjectDetails!ProjectType)," & pfix & ",[ProjectDetails]![ProjectType]) AS ProjectType1, ProjectDetails.ProjectDetailsID" & _
        " FROM ProjectDetails" & _
        " GROUP BY IIf(IsNull([ProjectDetails]![ProjectType])," & pfix & ",[ProjectDetails]![ProjectType]), ProjectDetails.ProjectDetailsID" & _
        " HAVING (((ProjectDetails.ProjectDetailsID)=[Forms]![TeamMember]![ProjectDetailsID]));"

Set rs = CurrentDb.OpenRecordset(MySQL)
If Not IsNull(rs!ProjectType) Then
    pfix = rs!ProjectType
End If
rs.Close
Set rs = Nothing
If pfix = "Community" Then
    prefix = "CGP"
ElseIf pfix = "Social" Then
    prefix = "SEB"
Else
    prefix = ""
End If

End Sub

 

[Kodo]

did you debug.print your sql statement to see if all the parameters had a value passed?

 

[lxh]

Print.Debug

did you debug.print your sql statement to see if all the parameters had a value passed?

a debug.print MySQL results in the following

SELECT IIf(IsNull(ProjectDetails!ProjectType),NA,[ProjectDetails]![ProjectType]) AS ProjectType1, ProjectDetails.ProjectDetailsID FROM ProjectDetails GROUP BY IIf(IsNull([ProjectDetails]![ProjectType]),NA,[ProjectDetails]![ProjectType]), ProjectDetails.ProjectDetailsID HAVING (((ProjectDetails.ProjectDetailsID)=[Forms]![TeamMember]![ProjectDetailsID]));

Does that help anyone?

Thanks
Lex

 

[Jon K]

Change your SQL string to this:-

   MySQL = "SELECT IIf(IsNull([ProjectDetails]![ProjectType]),'" & pfix & "'," & _
           " [ProjectDetails]![ProjectType]) AS ProjectType," & _
           " [ProjectDetails].[ProjectDetailsID]" & _
           " FROM ProjectDetails" & _
           " WHERE ProjectDetailsID ='" & Forms!TeamMember!ProjectDetailsID & "'" & _
           " GROUP BY IIf(IsNull([ProjectDetails]![ProjectType]),'" & pfix & "'," & _
           " [ProjectDetails]![ProjectType]), [ProjectDetails].[ProjectDetailsID];"

or this, which is simpler:-

   MySQL = "SELECT Nz([ProjectDetails]![ProjectType],'NA') AS ProjectType," & _
           " ProjectDetailsID" & _
           " FROM ProjectDetails" & _
           " WHERE ProjectDetailsID ='" & Forms!TeamMember!ProjectDetailsID & "'" & _
           " GROUP BY Nz([ProjectDetails]![ProjectType],'NA'), ProjectDetailsID;"

Note If ProjectDetailsID is a numeric field, remove the two single-quotes from the Where Clause, that is change the Where Clause to:-

" WHERE ProjectDetailsID =" & Forms!TeamMember!ProjectDetailsID & _

 

[lxh]

The error is on creating a new record

Thanks Jon K - you're right ProjectDetailsID is numeric. I replaced my SQL statment with the one(s) you suggested. Both Work well as queries but when run as code I get the error:

Run-time error 3021
No current record

which is exactly what I'm trying to aviod. The form it runs on is having problems only when it opens to create a new record - I'm stumped
**************
NEW INFORMATION
Ok, I currently have the code in the onopen event - thhis is the problem, it tries to look up the projectdetailsID before it haas been assigned. I put in an onclick event (happening after ID assignment) and it worked well.
Which event will wait until after assignment but still be automatic?

Thanks
Lex