By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,963 Members | 1,947 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,963 IT Pros & Developers. It's quick & easy.

EOF causing form/controls to go blank

P: n/a
Hello,

This is driving me crazy, but I have a form with a filter button and
when it is selected if there are no results then it is supposed to open
a subform (error box). Below is my code:

Private Sub cmdFilter_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim strMsg As String

Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry")
qdf.Parameters(0) = Forms![frmMain]![txtZipCode]
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Set Recordset = rst

Me.FilterOn = False
If RecordsetClone.EOF And RecordsetClone.BOF Then
DoCmd.OpenForm ("frmError"), acNormal
Me.FilterOn = True
End If
End Sub

Unfortunately, it is opening the frmError but everything on all forms
is blank. HELP!!

Thanks!

Sep 28 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
TRy
Function test()

Dim Rst As Recordset
Dim MyDb As Database

On Error GoTo Test_Err

Set MyDb = CurrentDb
Set Rst = MyDb.openrecordset("Query")

With Rst
.MoveLast
.Close
End With
Set Rst = Nothing
Exit Function

Test_Err:
If Err = 3021 Then
'Open subform or
MsgBox "There are no records"
Else
MsgBox Err.Description
End If
Resume Next

End Function

"Taylor" <mn*********@msn.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Hello,

This is driving me crazy, but I have a form with a filter button and
when it is selected if there are no results then it is supposed to open
a subform (error box). Below is my code:

Private Sub cmdFilter_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim strMsg As String

Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry")
qdf.Parameters(0) = Forms![frmMain]![txtZipCode]
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Set Recordset = rst

Me.FilterOn = False
If RecordsetClone.EOF And RecordsetClone.BOF Then
DoCmd.OpenForm ("frmError"), acNormal
Me.FilterOn = True
End If
End Sub

Unfortunately, it is opening the frmError but everything on all forms
is blank. HELP!!

Thanks!

Sep 28 '06 #2

P: n/a
Try checking for rst.RecordCount = 0 after opening the record set. See code
below.

Also you need to close the recordset and set the object variables to Nothing
before returning from the Sub.

"Taylor" <mn*********@msn.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Hello,

This is driving me crazy, but I have a form with a filter button and
when it is selected if there are no results then it is supposed to open
a subform (error box). Below is my code:

Private Sub cmdFilter_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim strMsg As String

Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry")
qdf.Parameters(0) = Forms![frmMain]![txtZipCode]
Set rst = qdf.OpenRecordset(dbOpenDynaset)
if rst.RecordCount = 0 then
DoCmd.OpenForm ("frmError"), acNormal
endif
Set Recordset = rst

Me.FilterOn = False
If RecordsetClone.EOF And RecordsetClone.BOF Then
DoCmd.OpenForm ("frmError"), acNormal
Me.FilterOn = True
End If
End Sub

Unfortunately, it is opening the frmError but everything on all forms
is blank. HELP!!

Thanks!

Sep 29 '06 #3

P: n/a
This worked beautifully! Thank you so much for your help :-)
paii, Ron wrote:
Try checking for rst.RecordCount = 0 after opening the record set. See code
below.

Also you need to close the recordset and set the object variables to Nothing
before returning from the Sub.

"Taylor" <mn*********@msn.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...
Hello,

This is driving me crazy, but I have a form with a filter button and
when it is selected if there are no results then it is supposed to open
a subform (error box). Below is my code:

Private Sub cmdFilter_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim strMsg As String

Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry")
qdf.Parameters(0) = Forms![frmMain]![txtZipCode]
Set rst = qdf.OpenRecordset(dbOpenDynaset)

if rst.RecordCount = 0 then
DoCmd.OpenForm ("frmError"), acNormal
endif
Set Recordset = rst

Me.FilterOn = False
If RecordsetClone.EOF And RecordsetClone.BOF Then
DoCmd.OpenForm ("frmError"), acNormal
Me.FilterOn = True
End If
End Sub

Unfortunately, it is opening the frmError but everything on all forms
is blank. HELP!!

Thanks!
Sep 29 '06 #4

P: n/a
"Taylor" <mn*********@msn.comwrote in
news:11**********************@k70g2000cwa.googlegr oups.com:
This is driving me crazy, but I have a form with a filter button
and when it is selected if there are no results then it is
supposed to open a subform (error box). Below is my code:

Private Sub cmdFilter_Click()
Dim db As Database
Dim rst As DAO.Recordset
Dim qdf As QueryDef
Dim strMsg As String

Set qdf = CurrentDb.QueryDefs("Get20Mile_SelQry")
qdf.Parameters(0) = Forms![frmMain]![txtZipCode]
Set rst = qdf.OpenRecordset(dbOpenDynaset)
Set Recordset = rst

Me.FilterOn = False
If RecordsetClone.EOF And RecordsetClone.BOF Then
DoCmd.OpenForm ("frmError"), acNormal
Me.FilterOn = True
End If
End Sub

Unfortunately, it is opening the frmError but everything on all
forms is blank. HELP!!
Why are you making this so incredibly complicated? Just test the
Recordcount of the RecordsetClone. If'it's zero, there aren't any
records, and you can turn off the filter. You can use the OnFilter
event to test this and cancel it.

Or, open your recordset and test its Recordcount.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
Sep 29 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.