Recordcount result intermittant

[Christine Pearc]

I have a form, frmReviewSearch, that is based on a query. The OnOpen Event for the form is

If Me.Recordset.RecordCount = 0 Then
    MsgBox "Nothing to find.", vbInformation, "No Data"
    DoCmd.Close
End If

Sometimes when you open the form, it says there aren't any records. Close the form, open it again, and the message doesn't pop up. In each case, the records in the subform appear okay.

Note: The subform uses the same query as the main form. The only difference is that the results in the subform can be filtered further based on search criteria set in the main form.

Does anyone have any clues what is wrong.

If it makes a difference, the dB is split and is on a LAN in a multi-user environment.

Cheers,
Christine

 

[KenHigg]

I think I would run this check before the form is open, maybe within the button that opens it. Say maybe with a dcount().

???
kh

 

[Christine Pearc]

Ken, I have a few questions:

Help says that code put in the form's On Open event runs before the form is opened. So, I don't understand your comment.

Next, If I move the recordcount to the Main Menu (which opens the form), can you explain how I tell Access to use the query, and then count the records? (I've searched Help without success, only finding DoCmd.OpenQuery.)

Also, I've seen other posts that refer to the Dcount function, and some combine it with Nz) but they don't explain how to use it/them, and I don't understand how to put it/them together with Me.Recordset.RecordCount.

Your further assistance will be appreciated!
Thank you,
Christine

 

[KenHigg]

You have probably hit what's going wrong with your current method:

Help says that code put in the form's On Open event runs before the form is opened.

Anyway, If you have a button that opens the form, you can use something like:

If dcount("[myFldName]","myTableName") <> 0 then
    open form stuff
else
    no records msgbox stuff
end if

Make sense?
kh

 

[Christine Pearc]

Thank you, Ken - understood it and it seems to work! I just hope it stays consistent, since like I said, "Me.Recordset.RecordCount = 0 worked most but not all the time.

One last favour: can you explain why DCount should be used rather than RecordCount? What is a valid reason for using RecordCount?

Christine

 

[KenHigg]

Hum...

Kind of a tough question since there are reasons either way on which way is best...

I try not to use domain functions like dcount() when performance might be an issue. As far as technical reasons why one may be better than the other, maybe some of the more experienced users like Pat, SJ, Doc or Wayne can answer if they read this post. I'd probably muck up an attempt at a technical answer.

kh

 

[Christine Pearc]

Thanks for your help, Ken.

Look forward to hearing from anyone who might have thoughts my the DCount vs. Recordcount question, and Ken's comment about performance.

Christine