Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Unanswered: Trigger an event on closing of a query

    Is there a way to trigger an event when a query closes? I want to have a form become visible (i.e. MyForm.visible = true) when a specific query is closed.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How are you viewing the query?
    How are you closing the query?
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm using the DoCmd.OpenQuery "QueryName", acNormal, acEdit but I can change this. I don't want to make the query a form though as I have many, many different queries. What I'm doing is making the form which has the criteria for the query invisible and I want to set the form visible again when the query closes. I guess I could make a Menu item but I wanted to avoid that and see if there's was an easy way to make the form visible again when the query closes. The criteria form is a popup, modal form so if I left it visible, it stays on top of the query which I don't want. I had looked into this once before but ended up setting a button on my MainFormBackGround (which is always open and maximized behind the scenes). I'd look to somehow set it so when the OnClose of the query (if possible) makes my criteria form visible again.
    Last edited by pkstormy; 06-27-07 at 12:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you thought about creating a datasheet form instead of opening the query directly? It'll look just like you want (I think) and you can assign events just like a regular form
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yeah - I've got a lot of queries though and wanted to avoid creating a datasheet or any kind of form (I suppose I can create a "container" datasheet form and populate the recordsource.) I have over 50 queries though. This was going to be my choice if there wasn't an easy way to put code in for closing a query (which I don't think is possible.) It's either a form or menu item. The problem with a Datasheet form is that the fields won't be the same for each query (and I don't want to create 50 or so DataSheet forms.) The MainFormBackGround has worked good for me in the past as it is a maximized form which is always open in the background (and has a button to re-opent the Main Menu form). When the query closes, this form becomes visible. I was hoping to come up with something different though (and I still need to somehow close the invisible criteria form when the query is closed.)
    Last edited by pkstormy; 06-27-07 at 12:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Why are we letting our users see (and modify!) raw data anyhoo?
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The queries are snapshots (no editing is allowed.)
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    This one stumped me for a good twenty minutes. But I think I might have a better solution. I have never used it, but I tried this based on the help:

    Code:
    Application.SysCmd(acSysCmdGetObjectState,acServerView,"vwContainerCheckList")
    This is in an ADP file but should work for queries in an MDB. When the view (query) is open this returns a 1. If not it returns a 0. You could add a Timer event in your criteria form and check every second or so to see if the query has been closed or not.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If they are snapshots....
    Why not have a big form with one big listbox which you can populate and customize "on the fly". You can change the rowsource programatically and the number of columns etc etc etc. You could use one form for all the queries (in theory).
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Paul

    I never got to implement it but I had a similar requirement at my last gig. The "solution" (untried and untested) I came up with was a little similar to George BUT -
    The container form has a query as a subform (you can do that I believe). The columns are then created for you - no need to account for all 50 queries. Just pass the query name as an arguement (or something) and the container sets the source object to the correct query. Everything else is handled for you.

    HTH. Let me know if it works too - I might nick the code
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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