[AccessD] Open a form and find a record

Canceling Open Action Of Form - If No Record
===================================

    As a generic solution, insertion of code block given at (A) below, in Open event of the target form, will ensure that if there is no record (in its record source), the form will refuse to open and display a custom message instead.

    This solution is universally applicable, whatever the filtering criteria in the command used for opening the form. It is however desirable to precede such a command by the following statement, in order to prevent error message (2501 - The OpenForm action was cancelled).
    On Error Resume Next

    It is observed from the original post (by Susan), that the command for closing the menu form has been placed before that for opening the target form. It has to be the other way around. Suggested revised code for click event of appropriate command button (CmdOpen) on the menu form, is given at (B) below.

A.D.Tejpal
--------------

(A) Code in target form (frmProjects)
==================================
Private Sub Form_Open(Cancel As Integer)
    With Me.RecordsetClone
        If .BOF And .EOF Then
            MsgBox "You are not the QA on any projects"
            Cancel = 1
        End If
    End With
End Sub
==================================

(B) Code in Menu Form
==================================
Private Sub CmdOpen_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
    
    If Len(Nz(UsersName, "")) = 0 Then
        Exit Sub
    End If
    
    stDocName = "frmProjects"
    stLinkCriteria = "[QAFName]=" &  _
                                "'" & Me![UsersName] & "'"
    
    On Error Resume Next
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    On Error GoTo 0

    DoCmd.Close acForm, Me.Name
    
End Sub
==================================

  ----- Original Message ----- 
  From: Gustav Brock 
  To: Access Developers discussion and problem solving 
  Sent: Tuesday, October 19, 2004 14:35
  Subject: Re: [AccessD] Open a form and find a record


  Hi Donald and Susan

  It's an old and persistent rumour that DLookup is always slow. Quite often it is not. So, Susan, give it a try - it's extremely simple and in a minute your problem will be solved and you can move on to the more funny tasks.

  By the way, the code could be simplified even further:

    strCriteria ="[QAFName]='" & Me![UsersName] & "'"
    If IsNull(Dlookup("[ProjectID]", "tblProjects", strCriteria)) Then
      MsgBox "You are not the QA on any projects."
    Else
      ' Open form ...
    End If

  /gustav


  > Well, I don't have to worry much about portability issues, but I'd be interested in the opinions of folks better versed than I in the area of optimization.  
  I was under the impression that a simple one-time call to a domain function in a case like this is no big deal in terms of speed. 
  > (I suppose it depends on the size of the table.)  I do avoid using them in iterative processes, however.  

  << SNIP >>

  ----- Original Message ----- 
  From: Klos, Susan 
  To: 'AccessD at databaseadvisors.com' 
  Sent: Monday, October 18, 2004 23:31
  Subject: [AccessD] Open a form and find a record


  I am using the following code to open a from the menu form and filter the records so that the user sees only their records.

      Dim stDocName As String
      Dim stLinkCriteria As String    

      stDocName = "frmProjects"
      stLinkCriteria = "[QAFName]=" & "'" & Me![UsersName] & "'"    

      DoCmd.Close 'Closes the menu form.
      DoCmd.OpenForm stDocName, , , stLinkCriteria 

  The problem is that sometimes the user's name will not  be found on the form in the [QAFName] text box.  This would simply mean that the user is not a "Quality Assurance" person on anybody's project.  
  When this happens I don't want the form to open.  I want a message box to pop up that says "You are not the QA on any projects".  On clicking ok on the message box the menu
  opens back up.   

  Any help would be greatly appreciated. 

  Susan Klos
  Senior Database Analyst
  Evaluation and Reporting
  Florida Department of Education
  850-245-0708 
  sc 205-0708