counting records in open form after filter, trapping for empty recordsets

[allyn44]

Hello,

I have built a serch form for users to edit records. I only want them
to pull up the record they need, and I want to check for nulls. There
should not be dupes becasue the underlying tables are indexed. So the
user will pull up either one record or none. Here is my problem:

I pass the form name through a tmpvariable and ther other search
parameters through unbound fields on an unbound form--this works fine
unless the form opens to a null recordset--I want to trap for that. I
am currenlty oepning the form using a where clause in the OpenForm
action--relevant code is below:
MAKES SURE USER ENTERS A SEARCH TERM
If IsNull(tmpPartid) Then
MsgBox "You must enter an enrollment number to search for."
Exit Sub
End If

TMPFORMNAME IS MY HOLDING VARIABLE FOR THE FROM NAME
Dim strlinkcriteria As String
strlinkcriteria = "[PartID]=" & Me![tmpPartid] & ""
docname = tmpformname
DoCmd.OpenForm docname, , , strlinkcriteria, acFormEdit

'WANT TO TEST FOR NULL RECORDSETS HERE--i TRIED CREATING A RECORDSET
CLONE, THAT DIDN'T WORK FOR ME--I DON'T KNOW WHY. HOW CAN I EITHER
CREATE A RECORDSETCLONE AND COUNT THE RECORDS OR FIND ANOTHER WAY TO
COUNT THE RECORDS AND SEND THE USER BACK TO THE SEARCH SCREEN. i
WOULD LIKE TO COUNT THE RECORDS IN THE CURRENT OPEN FORM--SO THIS CODE
CAN APPLY TO WHATEVER FORM THE USER NEEDS TO EDIT???

OR ANY BETTER WAY I HAVE OVERLLOKED?

"If search produces no records then

MsgBox "There is no record with the ParticipantID you entered. Please
re-enter the ParticipantID"
Exit Sub
Else

I HAVE CODE THAT OPENS FORM TO THE DESIRED RECORD HERE
SNIPPED

THANKS
BOB

[Allen Browne]

Assuming the form is not already open, you can cancel its Open event if it
has no records:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No matches."
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<al*****@cox.net> wrote in message
news:5r********************************@4ax.com...

Hello,

I have built a serch form for users to edit records. I only want them
to pull up the record they need, and I want to check for nulls. There
should not be dupes becasue the underlying tables are indexed. So the
user will pull up either one record or none. Here is my problem:

I pass the form name through a tmpvariable and ther other search
parameters through unbound fields on an unbound form--this works fine
unless the form opens to a null recordset--I want to trap for that. I
am currenlty oepning the form using a where clause in the OpenForm
action--relevant code is below:
MAKES SURE USER ENTERS A SEARCH TERM
If IsNull(tmpPartid) Then
MsgBox "You must enter an enrollment number to search for."
Exit Sub
End If

TMPFORMNAME IS MY HOLDING VARIABLE FOR THE FROM NAME
Dim strlinkcriteria As String
strlinkcriteria = "[PartID]=" & Me![tmpPartid] & ""
docname = tmpformname
DoCmd.OpenForm docname, , , strlinkcriteria, acFormEdit

'WANT TO TEST FOR NULL RECORDSETS HERE--i TRIED CREATING A RECORDSET
CLONE, THAT DIDN'T WORK FOR ME--I DON'T KNOW WHY. HOW CAN I EITHER
CREATE A RECORDSETCLONE AND COUNT THE RECORDS OR FIND ANOTHER WAY TO
COUNT THE RECORDS AND SEND THE USER BACK TO THE SEARCH SCREEN. i
WOULD LIKE TO COUNT THE RECORDS IN THE CURRENT OPEN FORM--SO THIS CODE
CAN APPLY TO WHATEVER FORM THE USER NEEDS TO EDIT???

OR ANY BETTER WAY I HAVE OVERLLOKED?

"If search produces no records then

MsgBox "There is no record with the ParticipantID you entered. Please
re-enter the ParticipantID"
Exit Sub
Else

I HAVE CODE THAT OPENS FORM TO THE DESIRED RECORD HERE
SNIPPED

THANKS
BOB

[allyn44]

Thank you--knewe it was simple!

bob
On Tue, 14 Feb 2006 10:13:01 +0800, "Allen Browne"
<Al*********@SeeSig.Invalid> wrote:

Assuming the form is not already open, you can cancel its Open event if it
has no records:

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
MsgBox "No matches."
End If
End Sub