Dynamic requery of underlying data

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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…

Reply | Quote

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.

Reply | Quote

Thanks, Charlotte! I think your ideas have great merit, and that’s probably the route I’ll go.

Reply | Quote