Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Catch an error on OpenForm event

Posted on 2014-04-13
6
Medium Priority
?
481 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

Industry Leaders: 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!

Question has a verified solution.

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

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Screencast - Getting to Know the Pipeline

886 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