Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Catch an error on OpenForm event

Posted on 2014-04-13
6
Medium Priority
?
480 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 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

721 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