Search Dialog Forms - Wierd Problem

[S Jackson]

I have four different search dialog forms.

The first search dialog form works great - user enters text, the db finds
records that are "like" the text entered and displays a message box with the
number of records found.

The rest of the dialog forms give an incorrect record count - they give the
count of 1 and when the (stDocName) form opens up, there is a pause before
the the number of records found shows up at the bottom beside the record
navigation buttons. I have checked the code in all four of my forms - it is
identical - especially since I cut and pasted it. Why does the code work
sometimes and not all the time? The only different I can see at this point
is that the first form opens up another form who's data source is a query.
The rest of the search forms open up other forms based on tables. Is this
the problem?

TIA
S. Jackson

Here is the code:

Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmReps"

'Set search criteria, "*Smith" opens up all Attorneys with the last "Smith"
stLinkCriteria = "[Representative]Like" & "'" & Me![txtSearch] & "*" &
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
' If no records found, close form and display message
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try again", ,
"Open Case Search Results"
DoCmd.Close acForm, stDocName

Else

MsgBox "The database found " _
& .RecordCount _
& " attorney(s).", vbOKOnly + vbInformation, "Open Case Search Results"
Forms(stDocName).Visible = True
End If
End With

 

[Nikos Yannacopoulos]

Insert a line:
.movelast
in the Else pasrt before the .recordcount, to force the
code to go to the last record before you get the record
count.

In the first form you get the right number because the
underlying query is forced to run anyway.

Nikos

 

[S Jackson]

Thanks Nikos.

I kept trying to put the .movelast in the line " &
..RecordCount.MoveLast " which resulted in a object required error.

S. Jackson

Insert a line:
.movelast
in the Else pasrt before the .recordcount, to force the
code to go to the last record before you get the record
count.

In the first form you get the right number because the
underlying query is forced to run anyway.

Nikos

-----Original Message-----
I have four different search dialog forms.

The first search dialog form works great - user enters text, the db finds
records that are "like" the text entered and displays a message box with the
number of records found.

The rest of the dialog forms give an incorrect record count - they give the
count of 1 and when the (stDocName) form opens up, there is a pause before
the the number of records found shows up at the bottom beside the record
navigation buttons. I have checked the code in all four of my forms - it is
identical - especially since I cut and pasted it. Why does the code work
sometimes and not all the time? The only different I can see at this point
is that the first form opens up another form who's data source is a query.
The rest of the search forms open up other forms based on tables. Is this
the problem?

TIA
S. Jackson

Here is the code:

Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmReps"

'Set search criteria, "*Smith" opens up all Attorneys with the last "Smith"
stLinkCriteria = "[Representative]Like" & "'" & Me! [txtSearch] & "*" &
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
' If no records found, close form and display message
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try again", ,
"Open Case Search Results"
DoCmd.Close acForm, stDocName

Else

MsgBox "The database found " _
& .RecordCount _
& " attorney(s).", vbOKOnly + vbInformation, "Open Case Search Results"
Forms(stDocName).Visible = True
End If
End With




.

 

[Nikos Yannacopoulos]

Sorry, it seems I confused you... what I meant is:

Else
.movelast '(force recordset to last record)
MsgBox "The database found " & .RecordCount & _
" attorney(s).", vbOKOnly + vbInformation, _
"Open Case Search Results"
Forms(stDocName).Visible = True
End If
End With

HTH,
Nikos

-----Original Message-----
Thanks Nikos.

I kept trying to put the .movelast in the line " &
..RecordCount.MoveLast " which resulted in a object required error.

S. Jackson

Insert a line:
.movelast
in the Else pasrt before the .recordcount, to force the
code to go to the last record before you get the record
count.

In the first form you get the right number because the
underlying query is forced to run anyway.

Nikos

-----Original Message-----
I have four different search dialog forms.

The first search dialog form works great - user enters text, the db finds
records that are "like" the text entered and displays a message box with the
number of records found.

The rest of the dialog forms give an incorrect record count - they give the
count of 1 and when the (stDocName) form opens up,

there
is a pause before

the the number of records found shows up at the bottom beside the record
navigation buttons. I have checked the code in all

four
of my forms - it is

identical - especially since I cut and pasted it. Why does the code work
sometimes and not all the time? The only different I can see at this point
is that the first form opens up another form who's data source is a query.
The rest of the search forms open up other forms based

on
tables. Is this

the problem?

TIA
S. Jackson

Here is the code:

Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmReps"

'Set search criteria, "*Smith" opens up all Attorneys with the last "Smith"
stLinkCriteria = "[Representative]Like" & "'" &

Me!
[txtSearch] & "*" &

"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden
' If no records found, close form and display message
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records,

please
try again", ,

"Open Case Search Results"
DoCmd.Close acForm, stDocName

Else

MsgBox "The database found " _
& .RecordCount _
& " attorney(s).", vbOKOnly + vbInformation, "Open Case Search Results"
Forms(stDocName).Visible = True
End If
End With




.

.