Record Count Problems

[S Jackson]

I have a dialog form that enables the user to search for a record. I want a
message box to come up telling the user how many records were found, or if
no records were found. Here is my code so far that does not work:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If stDocName.RecordsetClone.RecordCount = 0 Then
DoCmd.Close
MsgBox "No records found - Please try again."
Else
stDocName.RecordsetClone.MoveLast
MsgBox "The database found " _
& stDocName.RecordsetClone.RecordCount _
& " case(s).", vbInformation, "Record Count"
End If

Of course, I am getting an error that says "Invalid qualifier" because the
variable is out of scope (I think). How do I rearrange all this to work?

TIA
S Jackson

 

[S Jackson]

Sorry for prematurely posting and showing my sheer ignorance, but I have
figured out part of my error. Now I am getting a "syntax" error.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"
stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"

If Forms!frmMaster!RecordsetClone!RecordCount = 0 Then
MsgBox "No records found - Please try again."
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!frmMaster!RecordsetClone!MoveLast
MsgBox "The database found " _
& Forms!frmMaster!RecordsetClone!RecordCount _
& " case(s).", vbInformation, "Record Count"
End If

The syntax error is at the end of the following line:

Forms!frmMaster!RecordsetClone!MoveLast

Any help is greatly appreciated!
S. Jackson

 

[Dan Artuso]

Hi,
To solve your syntax error, use . instead of !
General rule:
.. is for properties and methods
! is to refer to an item within a collection

However, your code will not work because you can't
refer to a form until it's open, therefore this line:
If Forms!frmMaster!RecordsetClone!RecordCount = 0 Then

will give you a runtime error because the from is not open yet.

Put similiar code in the form's Open event. If the record count is 0,
display a message and then Cancel the event.
--
HTH
Dan Artuso, Access MVP

Sorry for prematurely posting and showing my sheer ignorance, but I have
figured out part of my error. Now I am getting a "syntax" error.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"
stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"

If Forms!frmMaster!RecordsetClone!RecordCount = 0 Then
MsgBox "No records found - Please try again."
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!frmMaster!RecordsetClone!MoveLast
MsgBox "The database found " _
& Forms!frmMaster!RecordsetClone!RecordCount _
& " case(s).", vbInformation, "Record Count"
End If

The syntax error is at the end of the following line:

Forms!frmMaster!RecordsetClone!MoveLast

Any help is greatly appreciated!
S. Jackson

I have a dialog form that enables the user to search for a record. I want a
message box to come up telling the user how many records were found, or if
no records were found. Here is my code so far that does not work:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If stDocName.RecordsetClone.RecordCount = 0 Then
DoCmd.Close
MsgBox "No records found - Please try again."
Else
stDocName.RecordsetClone.MoveLast
MsgBox "The database found " _
& stDocName.RecordsetClone.RecordCount _
& " case(s).", vbInformation, "Record Count"
End If

Of course, I am getting an error that says "Invalid qualifier" because the
variable is out of scope (I think). How do I rearrange all this to work?

TIA
S Jackson

 

[S. Jackson]

I use the form for other purposes so I do not want the user to receive the
MsgBox regarding how many records they are viewing every time they access
for form. I only wanted the message to appear when the user is searching
for a record, not when they are simply reviewing all records. But, if there
is no other choice, then that's what I'll do. Thanks.

S. Jackson

Hi,
To solve your syntax error, use . instead of !
General rule:
. is for properties and methods
! is to refer to an item within a collection

However, your code will not work because you can't
refer to a form until it's open, therefore this line:
If Forms!frmMaster!RecordsetClone!RecordCount = 0 Then

will give you a runtime error because the from is not open yet.

Put similiar code in the form's Open event. If the record count is 0,
display a message and then Cancel the event.
--
HTH
Dan Artuso, Access MVP

Sorry for prematurely posting and showing my sheer ignorance, but I have
figured out part of my error. Now I am getting a "syntax" error.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"
stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"

If Forms!frmMaster!RecordsetClone!RecordCount = 0 Then
MsgBox "No records found - Please try again."
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!frmMaster!RecordsetClone!MoveLast
MsgBox "The database found " _
& Forms!frmMaster!RecordsetClone!RecordCount _
& " case(s).", vbInformation, "Record Count"
End If

The syntax error is at the end of the following line:

Forms!frmMaster!RecordsetClone!MoveLast

Any help is greatly appreciated!
S. Jackson

I have a dialog form that enables the user to search for a record. I

want
a

message box to come up telling the user how many records were found, or if
no records were found. Here is my code so far that does not work:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

stLinkCriteria = "[CaseName]Like" & "'" & Me![txtSearch] & "*" & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

If stDocName.RecordsetClone.RecordCount = 0 Then
DoCmd.Close
MsgBox "No records found - Please try again."
Else
stDocName.RecordsetClone.MoveLast
MsgBox "The database found " _
& stDocName.RecordsetClone.RecordCount _
& " case(s).", vbInformation, "Record Count"
End If

Of course, I am getting an error that says "Invalid qualifier" because the
variable is out of scope (I think). How do I rearrange all this to work?

TIA
S Jackson