Solved

Catch an error on OpenForm event

Posted on 2014-04-13
6
474 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
  • 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 29

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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