Solved

Catch an error on OpenForm event

Posted on 2014-04-13
6
469 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 119

Expert Comment

by:Rey Obrero
Comment Utility
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
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Closing Comment

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

Author Comment

by:anthonytr
Comment Utility
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 119

Expert Comment

by:Rey Obrero
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A short article about problems I had with the new location API and permissions in Marshmallow
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.
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now