Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DoCmd.OpenForm problem (2002)

    I'm using DoCmd.OpenForm with an sQL WHERE condition in order to open my form.
    e.g. DoCmd.OpenForm "General Form", , , "BorrowerFullName Like '%" & lname & "%' AND Zip Like '" & ZIP & "%'"
    If the criteria are not met, the form opens anyway in Add mode. Instead, I want to inform the user that no records were found for his criteria, and the form should not open. How can I do this?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DoCmd.OpenForm problem (2002)

    Method 1.

    Use the On Open event of the form to check if there are records, and cancel if there aren't.

    Private Sub Form_Open(Cancel As Integer)
    If Me.RecordsetClone.RecordCount = 0 Then
    MsgBox "There are no records to display", vbInformation
    Cancel = True
    End If
    End Sub

    Note 1: This means that you can't use the form to add a first record to an empty table.
    Note 2: If there are no records, DoCmd.OpenForm will raise error 2501 (Action canceled by user), so you need to handle that.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Method 2.

    Check if there are records before issuing DoCmd.OpenForm:

    Dim strWhere As String
    strWhere = "BorrowerFullName Like '%" & lname & "%' AND Zip Like '" & ZIP & "%'"
    If DCount("*", "name_of_table_or_query", strWhere) = 0 Then
    MsgBox "There are no records to display", vbInformation
    Else
    DoCmd.OpenForm "General Form", , , strWhere
    End If

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DoCmd.OpenForm problem (2002)

    Hans, thank you so much! Your method 2 worked beautifully - and you taught me about the DCount method in the bargain. Method 1 was not an option because I need to use the same form to add new records on occasion. Thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •