How Can I stop this message from being displayed? 1

[justme1310]

Hello everyone,

I have the following code that checks for instances when no matching records are found.

When it happens, in addition to getting my "no records found" msg, I also get this message "The Openform Action was cancelled".
Is there anyway to stop this msg from being displayed??

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
  MsgBox "Sorry!!!  No records found!"
    Cancel = True
Else
    CurRonc = ROncologist
    CurMonc = MOncologist
End If

End Sub

Many thanks in advance


Will

 

[SgtJarrow]

How are you "opening" the form? From a command button on another form? If so, you could use that button to do the check for existing records. If records are not found, provide the error message and don't call the open of this form. If records are found, call the open of this form and then you only need what is now in the Else part above in you open statement becuase you have already done that checking.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Crowley16]

the reason that you get a message is that the cancel event is counted as an error.

you can avoid this error completely by doing what mstrmage1768 suggested and move the record checking out of the form's event, or you could handle this error by ignoring it with error handling techniques...

--------------------
Procrastinate Now!

 

[justme1310]

Hi Robert,

Thanks for responding so quickly.

The form is based on a query that has an "enter parameter value" dialog box which asks for the record number.

The form itself is opened by clicking on a command button.

The following is the code in the on click event of the command button.

On Error GoTo Err_Command2_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmchangeOncologist"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

Where exactly should I put the code to check for existing records?


Many thanks


Will

 

[SgtJarrow]

Well, you first have to figure out how to check for existing records. Do you know how to check that? If not, what is the SQL for the query that feeds frmchangeOncologist?

Once you have that part solved, the above code would become something like:

On Error GoTo Err_Command2_Click

    ' all the record existance checking code goes here

    If NoRecords Then
        MsgBox "Sorry!!!  No records found!"
    Else
        Dim stDocName As String
        Dim stLinkCriteria As String

        stDocName = "frmchangeOncologist"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If

Exit_Command2_Click:
    Exit Sub

Err_Command2_Click:
    MsgBox Err.Description
    Resume Exit_Command2_Click
    
End Sub

And the Form_Open event of the frmchangeOncologist you originally posted would become:

Private Sub Form_Open(Cancel As Integer)

    CurRonc = ROncologist
    CurMonc = MOncologist

End Sub

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Crowley16]

have you tried to just have docmd.close instead of the cancel = true?

--------------------
Procrastinate Now!

 

[justme1310]

Robert,

To check for records I would use this code in the open form event

Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then
  MsgBox "Sorry!!!  No records found!"
    Cancel = True
End If

End Sub

The SQL for the query that the form is based upon is as folows:-

SELECT tblPerson.*, tblAtdocts.*
FROM tblPerson LEFT JOIN tblAtdocts ON tblPerson.FILENO = tblAtdocts.FILENO
WHERE (((tblPerson.FILENO)=[Please Enter Patient File No]));

Thanks

Will

 

[justme1310]

Hi Crowley,

I tried the DoCmd.Close as you suggested and it works great.


Thanks again Robert for your help

Thanks so much.
Will

 

[SgtJarrow]

Glad to hear Crowley helped you out

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer