Solved

Catch an error on OpenForm event

Posted on 2014-04-13
6
478 Views
Last Modified: 2014-04-13
Hi,

The following code will be assigned to a Ribbon button in Ms Access, however, I would like to stop the form opening if there is no such record.  Currently if the user enters a Reference which doesn't exist or click "OK" or "Close" the form open up blank.

Function OpenBatch() As String
strBatch = InputBox("Please enter a Batch Reference ", "Batch Reference")
strWhere = "[batch_reference]=" & Chr(34) & strBatch & Chr(34)
DoCmd.OpenForm "frm_batches", WhereCondition:=strWhere
End Function

Open in new window

0
Comment
Question by:anthonytr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39997386
try this in the open event of the form "frm_batches"

private sub form_open(cancel as integer)

if me.recordset.eof then
     cancel=true
end if

end sub
0
 
LVL 30

Accepted Solution

by:
Randy Downs earned 500 total points
ID: 39997398
Maybe you can adapt this ..

It's checking for the existence of 2 fields in a record. If it doesn't exits it will add the form. In your case, you want to open the form if it does exist so you could just use DCount >0

Private Sub lngzClassID_AfterUpdate()
   Dim critieria As String
   Dim fn As String
   
   critieria = "lngzShowDate=" & Me.lngzShowID & " AND lngzClassID=" & Me.lngzClassID
   
   If DCount("*", "tblResults", [critieria]) = 0 Then
      fn = "frmResultsSection" & Me.lngzClassID.Column(4)
      DoCmd.OpenForm fn, , , , acFormAdd, acDialog, 2
   End If
End Sub

Open in new window

0
 

Author Comment

by:anthonytr
ID: 39997399
That will work, but what happens if I want to open the same form and add a new record?  I need to somehow catch the error (or lack of data) in my code, before opening the form.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:anthonytr
ID: 39997409
That's what I  was Looking for - great.  Forgot about using DCount
0
 

Author Comment

by:anthonytr
ID: 39997410
Here is my final code:

Function OpenBatch() As String
Dim strWhere As String
Dim fn As String
   
   strBatch = InputBox("Please enter a Batch Reference ", "Batch Reference")
   strWhere = "[batch_reference]=" & Chr(34) & strBatch & Chr(34)
   
   If DCount("*", "tbl_Batches", [strWhere]) > 0 Then
      DoCmd.OpenForm "frm_batches", WhereCondition:=strWhere
      Else
      MsgBox "Could not find the Batch Reference.  Please check you have entered it correctly", vbOKOnly
   End If


End Function

Open in new window

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39997415
private sub form_open(cancel as integer)

if me.recordset.eof then

if msgbox("Record not found, do you still want to open the form?",vbyesno)=vbno then

     cancel=true

end if
end if

end sub
0

Featured Post

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn different types of Android Layout and some basics of an Android App.
Make the most of your online learning experience.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.
Starting up a Project

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question