Inconsistent??

[bginhb]

I am using 2 forms. The first is a simple single table form that manages a key table called "Function". The second is a form with a sub-form. The main form is bound to the "Function" table with the sub-form managing a table called "Category".



The sequence of presentation for other forms and reports is critical for the data in both tables and each has a column called "ListNo" which is managed by the system administrator. Owing to processes in the system IT IS CRITICAL that the "ListNo" in both tables be unique and I wrote 2 simple virtually identical functions that attempt to open a recordset using a count query that simply counts the number of times each "ListNo" is used then validates that the counts do not exceed 1 ... see code below.

Should be easy stuff however the "Function" one works and the "Category" one does not. More importantly the "Category" one fails with a run-time error indicating that it cannot find the required first parameter which in this case is a query name or its SQL string. I have tried both ... they both produce the same error yet in other identical other function it works fine. Run-time error '3061': Too few Parameters. Expected 1. on the Set rs = db.OpenRecordset ("queryname") command that clearly provides the requested parameter.

Here's the code for the one that doesn't work ...

Public Function validateCategoryListNos()
    Dim db As Database, rs As Recordset
    Dim SQL As String
    
    Set db = CurrentDb
    'Set rs = db.OpenRecordset("CatDupListNoCheck")
    Set rs = db.OpenRecordset("SELECT Count(tblCategory.ListNo) AS CountOfListNo FROM tblCategory GROUP BY tblCategory.Function, tblCategory.ListNo HAVING (((tblCategory.Function)=[TempVars]![FuncID])) ORDER BY Count(tblCategory.ListNo) DESC;")
    
    rs.MoveFirst
    
    'If value of 1st record >1 then problem
    [TempVars]!
[ListDup] = False
    If rs.Fields(0) > 1 Then
        [TempVars]!
[ListDup] = True
    End If
        
    rs.Close
    db.Close
    
End Function

Appreciate any help. Thanks

[pbaldy]

Actually that error is usually associated with either a parameter in "queryname" or in this case likely the TempVars bit. Try this, presuming Function has a numeric data type:

Set rs = db.OpenRecordset("SELECT Count(tblCategory.ListNo) AS CountOfListNo FROM tblCategory GROUP BY tblCategory.Function, tblCategory.ListNo HAVING tblCategory.Function= " & [TempVars]![FuncID] & " ORDER BY Count(tblCategory.ListNo) DESC;")

I haven't used TempVars, but I think that will work.

[bginhb]

Thanks pbaldy!

I could have looked at it for a month and would never had guessed that it related to the query internals rather than the VBA RecordSet statement.

I use TempVars because a goodly portion of the application is macro based and Tempvars is about the only way to set conditions in the macro system ... and they're global but can be difficult to manage.

[pbaldy]

No problemo! If you used this

Set rs = db.OpenRecordset ("queryname")

and the specified query had 3 form references in the criteria for example, the error would have been"Too few Parameters. Expected 3". Basically it's telling you that it can't resolve something you're referring to, whether it be a form reference or the TempVars.