Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dynamic requery of underlying data

    I have a subform whose control source is a query, that query being based on a table called tbl_Winners. What I'd like to do is have a button on the main form that fires a make table query that replaces tbl_Winners. The problem here is that the subform has the table locked...

    Is there an elegant way around this, or am I going about providing that functionality, i.e. insta-refresh of data at the click of a button, in an improper way?
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic requery of underlying data

    Why do you want to do a make-table instead of just appending a new record? As long as the subform is open, you have a lock on the table. Futhermore, make-table queries are a huge source of bloat in any database.

    Maybe if you explain what you're trying to accomplish, there is an alternative.
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic requery of underlying data

    Here's the scoop. My company has fewer parking spaces than employees, and we must conduct a random pick for the lucky few that get to park close each week. The flow that I have at present is this:

    A table with employee's names and ID#.
    A make table query that uses the Rnd() function against the employee's ID and top values to get a list of winners,(tbl_Winners).
    A bit of code that adds a autonumber field to tbl_Winners.
    A select query that joins tbl_Winners with a list of parking space #'s, joined autonumber to Parking#.

    The form is based on that last select query. As I mentioned before, on that form I'd like to have a "Get new winners" button that launches the flow above and the form refreshes. The problem, of course, is that tbl_Winners is locked by the select query which is the record source of the form.

    So, why the make table query in the first place? For HR purposes, they need a static data set they can refer to for present winners (as you might expect, people are pretty grumpy about the prospect of not being able to park close), but also because I need a table that I can add an autonumber field to so I can easily assign a parking spot.

    Does this clarify or muddy the idea of what I'm trying to do? As we're talking about a very small db, I could also post it...
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Dynamic requery of underlying data

    I would still make tbl_Winners a permanent table and add a datefield of some sort. Then you can do your topvalues query on the employees table and append the results to tbl_Winners. Use the button to run the append query and filter for the most recent batch. T

    hat would give you a permanent record of the winners, which should make HR happy and even allow you to refine the rules more so that you could exclude winners in the previous time period from the topvalues for the next group.
    Charlotte

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic requery of underlying data

    Thanks, Charlotte! <img src=/S/smile.gif border=0 alt=smile width=15 height=15> I think your ideas have great merit, and that's probably the route I'll go.
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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