What to code for on Open Form & Emply Query result set

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that opens, requests user to enter two fields, on update of
last field, this form executes macro that performs the following:
Action = OpenForm
FormName = Fr_CR_U
View = Form
Where Condition =
([TST_FR_CASE_RECORDS]![CASE_NUM_YR]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_CASE_YR]
And
[TST_FR_CASE_RECORDS]![CASE_NUM]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_CASE_NUM])
Data Mode = Edit
Window Mode = Normal

Sometimes, if the user enters data that is Not-In-File, the form opens
totally empty.
I want to be able to issue MsgBox error condition back to user, send them
back to first form to try again rather than open empty form.

What do I have to code for on this condition?

Thanks
 
In the form's open event do something like:

Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
Else
Exit Sub
End If

Error_Handler:
MsgBox "No Records to show", vbOKOnly, "No Records"
Exit Sub

End Sub

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

RNUSZ@OKDPS said:
I have a form that opens, requests user to enter two fields, on update of
last field, this form executes macro that performs the following:
Action = OpenForm
FormName = Fr_CR_U
View = Form
Where Condition =
([TST_FR_CASE_RECORDS]![CASE_NUM_YR]=[Forms]![Fr_Search_By_Case]![unbtxt_SEA
RCH_CASE_YR]
And
Click to expand...
[TST_FR_CASE_RECORDS]![CASE_NUM]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_
CASE_NUM])
Data Mode = Edit
Window Mode = Normal

Sometimes, if the user enters data that is Not-In-File, the form opens
totally empty.
I want to be able to issue MsgBox error condition back to user, send them
back to first form to try again rather than open empty form.

What do I have to code for on this condition?

Thanks
Click to expand...
 
Arvin,

Thanks for responding. I had taken your suggestion and placed it in the
form that was being called after the macro with the select statement was
executed. Form Fr_Search_By_Case, asks user for record key values, user
enters it, passes control to macro that saves these values, plus passes these
values to the select statement.
Then Opens form fr_CR_U with records as subset. This code was inserted in
the On Open event of the form. The code catchs the empty condition, but...
doesn't stop as I wanted it to. Form Fr_CR_U also has a subform attached to
it named sbfr_CR_U, it attempts to count records in a function RECORDSINTABLE
and this function is what fails, due to no records to be counted. I just can
not seem to logically place the test for empty in the correct spot. I've
even placed it in the subform On-load event, and that doesn't stop the
function from failing in the subform.

I can include code if needed. I do appreciate all the help and support from
this site, without it, people like myself and others would never get their
projects done, and would never learn the correct ways of access/vba coding.

Thanks,



Arvin Meyer said:
In the form's open event do something like:

Sub Form_Open(Cancel As Integer)
On Error GoTo Error_Handler

If Me.RecordsetClone.RecordCount = 0 Then
Cancel = True
Else
Exit Sub
End If

Error_Handler:
MsgBox "No Records to show", vbOKOnly, "No Records"
Exit Sub

End Sub

--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

RNUSZ@OKDPS said:
I have a form that opens, requests user to enter two fields, on update of
last field, this form executes macro that performs the following:
Action = OpenForm
FormName = Fr_CR_U
View = Form
Where Condition =
([TST_FR_CASE_RECORDS]![CASE_NUM_YR]=[Forms]![Fr_Search_By_Case]![unbtxt_SEA
RCH_CASE_YR]
And
Click to expand...
[TST_FR_CASE_RECORDS]![CASE_NUM]=[Forms]![Fr_Search_By_Case]![unbtxt_SEARCH_
CASE_NUM])
Data Mode = Edit
Window Mode = Normal

Sometimes, if the user enters data that is Not-In-File, the form opens
totally empty.
I want to be able to issue MsgBox error condition back to user, send them
back to first form to try again rather than open empty form.

What do I have to code for on this condition?

Thanks
Click to expand...
Click to expand...
 
I can include code if needed. I do appreciate all the help and support from
this site, without it, people like myself and others would never get their
projects done, and would never learn the correct ways of access/vba
Click to expand...
coding.

Please post the code or email the zipped, compacted, db to me at:

arvinm at accessmvp dot com

Include a comment at the point where the code is giving you problems. BTW, I
will post the answer back here so that everyone will benefit from it.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top