Help With RecordSet Variable

[S Jackson]

I am trying to build in a message box that displays the number of records a
user finds when he executes a Search from my fdlgSearch form. Here is the
code so far:
Dim stDocName As String
Dim stLinkCriteria As String
Dim rst As Recordset

stDocName = "frmMaster"

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

Set rst = CurrentDb.OpenRecordset(stLinkCriteria)

If rst.RecordCount = 0 Then
MsgBox "The database did not fine any records, please try again."
DoCmd.Close

Else

rst.MoveLast
MsgBox "The database found " _
& rst.RecordCount _
& " case(s).", vbInformation, "Record Count"
End If

I get a message when the "frmMaster" opens that reads:

The Microsoft Jet database engine cannot find the input table or query
'[CaseName]Like'new*". Make sure it exists that that its name is spelled
correctly.

Any suggestions are greatly appreciated. Thanks.

P.S. I know I can put simple code in the "frmMaster" that counts the number
of records that the it finds, but I use this form for other purposes and I
do not want a Message Box displaying the number of records it find every
time a user opens this form. The other alternative is to create another
form, but I am trying to reduce the size of my database objects as it seems
that I have so many of them now.

Which makes the database run more efficiently:

a. create a second form and put in code to count the records in the
"OnOpen" Event; or
b. stay with a single form and attempt to make the code work above.

Thanks.
S. Jackson

 

[PC Datasheet]

You need some spaces in the stLinkCriteria line!

 

[Bruce M. Thompson]

Set rst = CurrentDb.OpenRecordset(stLinkCriteria)

stLinkCriteria contains only a "WHERE" clause that filters the form's recordset,
not a full sql statement, so there is nothing to open. Try, instead, opening the
form hidden and then retrieve the recordcount from the form's recordsetclone
property (I have merged suggested changes with your existing code):

'***
With Forms(stDocName).RecordsetClone
.MoveLast
If .RecordCount = 0 then

MsgBox "The database did not fine any records, please try again." DoCmd.Close acForm, stDocName

Else

MsgBox "The database found " _ & .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Record Count"
End If

End With
'***

 

[Bruce M. Thompson]

Forms(stDocName).Visible = True

End With
'***

 

[S Jackson]

Thanks Bruce.

Thank you so much for your help. After playing with it I figured out how to
make the changes you suggested and it works now, for the most part.
However, when I run this requesting a record I know does NOT exist, I get a
message box that states, "No current record," instead of getting the message
box with the prompt that I specified in the If statement:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmMaster"

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

With Forms(stDocName).RecordsetClone
.MoveLast
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try again."
DoCmd.Close acForm, stDocName

Else

MsgBox "The database found " _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Record Count"
Forms(stDocName).Visible = True
End If
End With

Any thoughts?
Thanks again for your help, because regardless it works.
S. Jackson

 

[Bruce M. Thompson]

Thank you so much for your help. After playing with it I figured out how to

make the changes you suggested and it works now, for the most part.
However, when I run this requesting a record I know does NOT exist, I get a
message box that states, "No current record," instead of getting the message
box with the prompt that I specified in the If statement:

Oops. Leave out this line:

.MoveLast

I just don't know how that line slipped in there.

 

[S Jackson]

Hey! Thanks again.

One more question:
- the Main form opens up to the stLinkCriteria selected by the user, but
the form opens hidden
(DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden)
- then my code counts the records found:
- if not records found, it displays a message "No records found . .
.."
- if records ARE found, it displays a message with the number of
records found and makes the Main form visible.

If the user does not find any records, and decides to discontinue his
search, I need to close the Main form (that is not visible). Is this what I
add to the code?

If .RecordCount = 0 Then
MsgBox "No records found, please try again"
Forms(stDocName).SetFocus
DoCmd.Close
Else

It works, but the Main form (stDocName) flashes up on the screen (SetFocus)
before it closes. Correct way to do this?

Thanks Again!
S. Jackson

 

[Bruce M. Thompson]

If the user does not find any records, and decides to discontinue his

search, I need to close the Main form (that is not visible). Is this what I
add to the code?

If .RecordCount = 0 Then
MsgBox "No records found, please try again"
Forms(stDocName).SetFocus
DoCmd.Close
Else

It works, but the Main form (stDocName) flashes up on the screen (SetFocus)
before it closes. Correct way to do this?

The answer to that appears in an earlier response from me. Do *not* set the
focus, simply close the form using:

DoCmd.Close acForm, stDocName

Using "DoCmd.Close" requires the target form to have the focus, but explicitly
closing the form by name does not.