Results 1 to 9 of 9
  1. #1
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114

    "Enter Parameter Value" box closes all forms when "cancel" is chosen

    I'm (very) new to Access. I created a query which filters the results based on a value the user enters and then I created a report based on that query (so it looks nice to the user). I entered a few basic macros so that when the report is opened the primary form (which contains a command button to open the report) closes and then opens the report. Then, when you're done with the report and close it, I used a macro to open the previous form. This creates a "loop" so that users have either the primary form or a report open. When one opens the other closes and when the other closes the one opens. Fairly basic stuff.

    Here's my problem: When I hit the "Cancel" button in the "Enter Parameter Value" box the report doesn't run and the primary form closes (because I used a macro to close it when the report is opened) leaving the user with a blank screen. I think that because the report didn't run there's a gap where the macros in the report can't execute, therefore, the primary form isn't opened per the report macro.

    Click image for larger version. 

Name:	Parameter Value.GIF 
Views:	12 
Size:	11.8 KB 
ID:	17168



    Ultimately, I need a solution (maybe some VBA) that opens the primary form when nothing else is open. Is that possible?

    Best Regards,

    Eddy Sincere

  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,822
    Why bother closing the form?
    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
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    Valid question. To be honest, I like the way it looks. The absence of one multiple forms makes a cleaner presentation and removes the possibility of errors from users. If it's a significant undertaking to make the primary form open then I'll just go with the option of keeping the primary form open all the time.

  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,822
    I have a Menu form that never closes. All other forms and reports open on top of the Menu form. User always has Menu form to return to.
    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
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    So... two comments dissing the idea. Should I assume that the VB is more complicated than I initially thought and not really worth the effort? If it is that's fine, I'll just leave the form open all the time.

    I found some code from another access database that looks like it might work. In the Properties sidebar, Event tab, On Close =Keep1Open([Form])

    FStartup is the primary form.

    Public Function Keep1Open(objMe As Object)
    On Error GoTo Err_Keep1Open
    'Purpose: Open the Switchboard if nothing else is visible.
    'Argument: The object being closed.
    'Usage: In the OnClose property of forms and reports:
    ' =Keep1Open([Form])
    ' =Keep1Open([Report])
    'Note: Replace "Switchboard" with the name of your switchboard form.
    Dim frm As Form 'an open form.
    Dim rpt As Report 'an open report.
    Dim bFound As Boolean 'Flag not to open the switchboard.

    'Any other visible forms?
    If Not bFound Then
    For Each frm In Forms
    If (frm.hwnd <> objMe.hwnd) And (frm.Visible) Then
    bFound = True
    Exit For
    End If
    Next
    End If

    'Any other visible reports?
    If Not bFound Then
    For Each rpt In Reports
    If (rpt.hwnd <> objMe.hwnd) And (rpt.Visible) Then
    bFound = True
    Exit For
    End If
    Next
    End If

    'If none found, open the switchboard.
    If Not bFound Then
    'DoCmd.OpenForm "FStartup"
    Forms!FStartup.Visible = True
    End If

    Exit_Keep1Open:
    Set frm = Nothing
    Set rpt = Nothing
    Exit Function

    Err_Keep1Open:
    If Err.Number = 2450& Then
    On Error Resume Next
    DoCmd.OpenForm "FStartup"
    Exit Function
    End If
    If Err.Number <> 2046& Then 'OpenForm is not available when closing database.
    'Call LogError(Err.Number, Err.Description, ".Keep1Open()")
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "Keep1Open()"
    End If
    End Function

    Thoughts?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Code behind form to open report, close itself, and reopen itself when report closes should be possible. I've never tried. Would have to review the code that fails to try to fix.

    That popup input prompt is in a query parameter? I never use dynamic parameterized queries.
    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.

  7. #7
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    An excellent summary. The code is inserted into each report so that when it closes the VB runs and opens the primary form. I think my problem comes from the report not executing if the "cancel" button is hit. Because it's aborted the VB never gets a chance to execute and I'm left with a blank screen.

    Yes, the popup is automatic with the type of query I'm using. The query is set up to search a database of documents and only return those records which have specific criteria. Before it (or the report that's attached to it) runs the popup box appears and you type the criteria. Then it runs and returns the result. Kinda cool while still being basic enough for a noob like me.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I just tested code I described in previous post and it works:

    Private Sub Command108_Click()
    DoCmd.Close
    DoCmd.OpenReport "Compensation", acViewPreview, , , acDialog
    DoCmd.OpenForm "Umpires"
    End Sub
    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.

  9. #9
    Eddy Sincere is offline Competent Performer
    Windows 7 64bit Access 2007
    Join Date
    Jul 2014
    Location
    Carson City, NV
    Posts
    114
    That works great. I appreciate the response, thanks!

    AS

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

Similar Threads

  1. Suppress "Error" message following "Cancel = True"
    By GraeagleBill in forum Programming
    Replies: 7
    Last Post: 03-23-2014, 05:40 PM
  2. "Enter Parameter Value" for in Access 2010
    By rooflesh in forum Access
    Replies: 5
    Last Post: 01-28-2012, 12:49 PM
  3. Replies: 1
    Last Post: 08-02-2011, 07:21 AM
  4. Replies: 7
    Last Post: 01-23-2011, 12:32 PM
  5. Replies: 4
    Last Post: 01-06-2011, 10:52 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