asked on

Catch an error on OpenForm event

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

Rey Obrero (Capricorn1)

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

ASKER CERTIFIED SOLUTION

Randy Downs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

anthonytr

ASKER

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.

anthonytr

ASKER

That's what I was Looking for - great. Forgot about using DCount

anthonytr

ASKER

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

Rey Obrero (Capricorn1)

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