Simple query question

[arclight]

Hi

I have a table in my Access 2007 database called Professional, which is linked to a Client table by its rClient field. The relationship from Client to Professional is one to many. There is a ProfessionalLead field in the Professional table that is bound to a checkbox. I'm trying to run a very simple query from the BeforeUpdate method of ProfessionalLead checkbox when it is clicked:

' Get all Professional Lead records for this client - should be no more than 1
Set dbs = CurrentDb
qryString = "SELECT * FROM Professional WHERE ProfessionalLead=-1 AND rClient=" & rClient.Value

Set rstProfLeads = dbs.OpenRecordset(qryString)
NumLeads = rstProfLeads.RecordCount

My hope is that rstProfLeads will contain only those records from Professional that are associated with the specified client and whose ProfessionalLead checkbox is checked. There are 3 records in Professional associated with the specified rClient, only one of which have ProfessionalLead checked, yet NumLeads receives a value of 3.
The really weird thing is that if I change the query to "SELECT * FROM Professional" (which should return all records in the table), NumLeads is always 1!

Any help greatly appreciated.

Patrick

 

[pr2-eugin]

Hello Patrick, Just wondering why the choice of Recordset when a simple DCount do the same?

Just that you know recordset count value might not be always reliable.. At times the Number of records returned would be ZERO, but might still say 1.. That would be the Null value..

If you still wish to use Recordset Object, try some of the methods described HERE.. Specially topics 3,4, and 9..

 

[arclight]

Hi Paul

Well, that was an eye-opener!

I had used a Recordset instead of DCount as I needed to access some fields further down in my code. But I popped a DCount into the code (as you suggested) and it gave sensible results.

I found some more stuff on Recordsets posted in TechNet.
Apparently Recordsets only fill as required. In order to be sure the Recordset is filled properly, you can call the MoveFirst & then MoveLast methods. This solved my problem. They really are a bit of a minefield

I lost a lot of time on this today but would have lost a lot more without your help - many thanks!

Pat

 

[pr2-eugin]

Glad to have helped..

As Allen describes,

Test before using any of the Move methods.

Don't MoveLast unless you really need to: this will be slow with a large recordset or a recordset drawn across a network.

Sometimes, Domain functions drive me up the wall.. but at times they make my life so easy.. Good Luck !!