Hi,

I need help with this problem. I have some code that has to run if I have records that have specific criterias. I don't need to get the list of records, just need to know if the record count is 0 or not.

Here is the code I have.


    Dim rec As Recordset
    Dim db As Database
    Dim strSQL As String

strSQL = "SELECT COMMANDES.RefCommande " & _
"FROM ((COMMANDES INNER JOIN COMMANDESDetails ON COMMANDES.IdCommande = COMMANDESDetails.IdCommande) INNER JOIN MASTERSHOW ON COMMANDESDetails.IdMaster = MASTERSHOW.IdMaster) INNER JOIN RESUME ON MASTERSHOW.IdEvenement = RESUME.IdResume " & _
"WHERE RESUME.CodeEvenement)= " & [parmResume] & "AND " & "COMMANDES.StatutDepenses= 0 ;"

        Set db = CurrentDb()
        Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)

        If rec.RecordCount > 0 Then ...

        Else ...

When I run this code, I get a message stating "not enough parameters... 1 expected". When I step through my code, I get this message at the "set rec = db.openrecordset..."

Can anyone help me with this please ... :(

Chantal

While you have the VBE open in debug mode, open the Immediate window and enter ?strSQL to show the SQL string. My guess is that your literal [parmResume] is a variable parameter for the query and you haven't assigned a value to it.


Darrell L. Embrey

You are right... My problem is with the parmResume... but I do assign it a value through a form. I just don't know how to do the statement for SQL to use the value of my variable. Can you help me ?


Thanks.

Chantal

Use the name of the control (i.e. textbox, combobox, listbox) on the form that holds the value for your variable. For example, if the control's name is txtResume, replace [parmResume] with Me.txtResume. Your new SQL string would be as follows:
strSQL = "SELECT COMMANDES.RefCommande " & _
"FROM ((COMMANDES INNER JOIN COMMANDESDetails ON COMMANDES.IdCommande = COMMANDESDetails.IdCommande) INNER JOIN MASTERSHOW ON COMMANDESDetails.IdMaster = MASTERSHOW.IdMaster) INNER JOIN RESUME ON MASTERSHOW.IdEvenement = RESUME.IdResume " & _
"WHERE RESUME.CodeEvenement)= " & Me.txtResume & "AND " & "COMMANDES.StatutDepenses= 0 ;"


Darrell L. Embrey

If Me.txtResume is text then this bit of coding need to include '

....."WHERE RESUME.CodeEvenement)= " & Me.txtResume & "AND " & "COMMANDES.StatutDepenses= 0 ;"

i.e.

....."WHERE RESUME.CodeEvenement)= '" & Me.txtResume & "' AND COMMANDES.StatutDepenses= 0 ;"


Cheers Ray

Cheers Ray