Queries, Parameters and Confusion

[snicker]

All I want to do is pop up a message box that warns a user if a recordset has more than 1 record in it. here is the query I am using:

SELECT Count(TrustPinNumbers.PinNumber) AS CountOfPinNumber, First(TrustPinNumbers.ClientNum) AS FirstOfClientNum FROM TrustPinNumbers HAVING (((First(TrustPinNumbers. ClientNum))=[Forms]![BCBTaxPayments]![txtClientNum2]));

This is one of the many Subs I have tried:
Code
_________________________________________
Private Sub cboClientName2_AfterUpdate()
Dim db As Database
Dim qrd As QueryDef
Dim rst As Recordset

Set db = DBEngine(0)(0)
Set qrd = db.QueryDefs("QryPinCount")
qrd.Parameters("ClientNum") = Me.Controls("cboClientName2")
Set rst = qrd.OpenRecordset(dbOpenDynaset)

MsgBox "worked"
Set rst = Nothing
Set qrd = Nothing
Set db = Nothing
__________________________________________
Outcome
Run-time error '3265'
Item not found in this collection.

There is something I am missing. All I really want to do is fiqure out how to open a query as a recordset..... right? Any help would be greatly appreciated.

thx in advance Mike
Access 2002 Developer Edition

 

[Jack Cowley]

If DCount("[ClientNum]", "TrustPinNumbers", "[ClientNum] = " & Me.txtClientNum) >= 2 Then
MsgBox "More than one Record with this client number"
End If

If you have bazillions of records then this will not be the fastest route, but it should do the trick.

hth,
Jack

 

[snicker]

If you have bazillions of records then this will not be the fastest route, but it should do the trick.

That's the thing. This DB can hold 3,000 to 10,000 pin numbers. Is there anyway to clean up or modify the original code to make it work?

I have tried this code also.
______________________________________________
Private Sub cboClientName2_AfterUpdate()
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("QryPinCount", dbOpenDynaset)
With rs
MsgBox .RecordCount
End With
Set rs = Nothing
Set db = Nothing
End Sub
_______________________________________________
Result: Run-time error '3061':
Too few parameters expected

Mike
Access 2002 Developer Edition

 

[Jack Cowley]

10,000 is not very many records for Access so why not try the DCount() and see what happens as you can always use code if it proves too slow... Take a look at this article as it should help you solve the problem of the 'Too few parameters...' error.

hth,
Jack

 

[Jon K]

Take a look at this thread for how to open a parameter QueryDef using DAO:-

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=59270