Results 1 to 5 of 5
  1. #1
    KWarzala is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Sep 2009
    Posts
    35

    Cancel open form if WHERE clause criteria doesn't exist

    I have a command button that prompts the user to enter a report number to filter the form for that specific record:

    DoCmd.OpenForm "frmReport",,,"ReportNumber = [Please Enter Report Number]"



    This works fine, but if the user types a report number in that doesn't exist, it still opens the form but shows a blank record... how do i get the event to cancel and show a message box letting the person know that the number they typed in isn't a valid report number?

    Thanks for any help!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Simplest is to have them select report number in a restricted combobox, meaning they can only select from the items in list. Then reference the combobox as filter parameter.

    DoCmd.OpenForm "frmReport", , , "ReportNumber = " & Me.comboboxname

    Is report number a text or number data type.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    KWarzala is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Sep 2009
    Posts
    35
    It's a text field, but there are thousands of reports for them to choose from

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Combobox has AutoExpand property. As they type in combobox it will match item in list. If they type wrong, no match.

    Text type needs apostrophe delimiters:

    DoCmd.OpenForm "frmReport", , , "ReportNumber = '" & Me.comboboxname & "'"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    KWarzala is offline Advanced Beginner
    Windows XP Access 2010 64bit
    Join Date
    Sep 2009
    Posts
    35
    In case anyone else is trying to do this, i got it to work the way i wanted:

    I created a form (frmReportLookup) with an unbound text box (txtReportNumberLookup) and an OK button (cmdOK)

    cmdOK_Click()
    If Not ISNull(Me.txtReportNumberLookup) Then
    If DCount("ReportNumber","tblReports","[ReportNumber]= '" & Me.txtReportNumberLookup & "'")>0 Then
    DoCmd.OpenForm "Reports",,,"[ReportNumber]='" & Me.txtReportNumberLookup & "'"
    Else
    MsgBox "Not a valid report Number"
    End If
    End If
    End Sub

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calling for parameter that doesn't exist anymore
    By rankhornjp in forum Programming
    Replies: 3
    Last Post: 12-22-2011, 02:14 PM
  2. VBA to create PDF and folder if doesn't exist!
    By crxftw in forum Programming
    Replies: 2
    Last Post: 08-08-2011, 08:53 AM
  3. Help on where clause of open form
    By Madmax in forum Access
    Replies: 5
    Last Post: 06-23-2011, 11:19 AM
  4. Append if record doesn't exist
    By Lorlai in forum Queries
    Replies: 1
    Last Post: 06-14-2011, 06:38 PM
  5. Form doesn't exist
    By Back2Basics in forum Access
    Replies: 1
    Last Post: 02-01-2010, 11:39 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums