Requerying underlying make table query

[Guest]

I have a ‘make table’ query that creates a ‘top 10’ record list on a form.
How can I allow the user to ‘requery’ the underlying query from within the
form to create a new list from the underlying query?. I can make it work from
outside the form, but whenever I try from within, I get ‘the database could
not lock the table as it is in use by another person or process’. I have
tried a range of different approaches, but none seem to work. I have spent a
great deal of time looking up various error messages, online forums, etc, to
the point where I am just dazed and confused, and just cannot get this thing
to work. Am I expecting something that Access cannot do?

Yours Sincerely

Desperate…

 

[John Vinson]

I have a ‘make table’ query that creates a ‘top 10’ record list on a form.

Why? Given that you could just display the results of a top 10 Select
Query on a form, on a report, export it, etc., what's the point of all
the overhead of a new Table?

How can I allow the user to ‘requery’ the underlying query from within the
form to create a new list from the underlying query?.

Are you trying to create a new table as the basis of the form itself?
Please describe this query in more detail - how are you running it
(post the code) and what does it do (post the SQL view of the query)?

I can make it work from
outside the form, but whenever I try from within, I get ‘the database could
not lock the table as it is in use by another person or process’.

That other person is YOU - it sounds like you have the form open bound
to the same table you're trying to modify. I think you're likely
stumbling over your own feet!

Am I expecting something that Access cannot do?

More likely you're just trying to go all the way around the barn to do
something that can be done much more easily than you think.


John W. Vinson[MVP]

 

[Guest]

John,

thanks for responding to my problem.

The reason for the 'make table' is because I need the list of top 10 to be
static, until the user decides to request another top 10. I realise that I
have foolishly ommited a vital element in my first posting. the top 10 is a
'random' list taken from a feeder list of top 500. the 'top 10' element is
not so important (not that it changes your underlying point about directly
linking a query to a form.)

I realise that I could simply interrogate the query for the form data, but
that would mean a different list from the master list each time, due to the
randomizer, hence the need for a make table first; in order to feed the form.
I also realise that this 'make table' element essentialy divorces the
underlying query from the form, but wondered if there is a better way of
accomplishing this task such that the user has 'in form' control.

FYI: The form is on a tab page on a mster form.

regards

Eric

 

[John Vinson]

John,

thanks for responding to my problem.

The reason for the 'make table' is because I need the list of top 10 to be
static, until the user decides to request another top 10. I realise that I
have foolishly ommited a vital element in my first posting. the top 10 is a
'random' list taken from a feeder list of top 500. the 'top 10' element is
not so important (not that it changes your underlying point about directly
linking a query to a form.)

I realise that I could simply interrogate the query for the form data, but
that would mean a different list from the master list each time, due to the
randomizer, hence the need for a make table first; in order to feed the form.
I also realise that this 'make table' element essentialy divorces the
underlying query from the form, but wondered if there is a better way of
accomplishing this task such that the user has 'in form' control.

FYI: The form is on a tab page on a mster form.

Please post your code and indicate where the error occurs.

The fact that the subform is on a tab page is irrelevant to this
problem, I'm pretty sure. I suspect that you're trying to run a
make-table query on a table that's already open (on the subform) - no
can do!

John W. Vinson[MVP]

 

[Guest]

Hello John,

Yes, trying to run the make table from the Open Table is exactly what i was
trying to do. I realise now that it can't be done. Is their an alternitive
whereby the user can request a new query, instead of the query automatically
updating everytime the form is opened?. The reason for needing a static
(query) list is because the 10 records represent a quiz test form, and only
when the user is finished (or bored...) does he require a new 10 record set
from the master list.

 

[John Vinson]

Hello John,

Yes, trying to run the make table from the Open Table is exactly what i was
trying to do. I realise now that it can't be done. Is their an alternitive
whereby the user can request a new query, instead of the query automatically
updating everytime the form is opened?. The reason for needing a static
(query) list is because the 10 records represent a quiz test form, and only
when the user is finished (or bored...) does he require a new 10 record set
from the master list.

What you might want to do instead of creating a new table is to have a
(static, local to that user) ActiveQuestions table. You could run a
Delete query to empty it, and then - instead of a MakeTable - run an
Append query to insert ten rows into it.

You may need to close the Form in order to do so, but I don't think
so. It will in any case be easier to manage than a MakeTable query,
since you won't need to worry about deleting a table and breaking all
the forms and reports which use that table!

John W. Vinson[MVP]

 

[Guest]

What you might want to do instead of creating a new table is to have a
(static, local to that user) ActiveQuestions table. You could run a
Delete query to empty it, and then - instead of a MakeTable - run an
Append query to insert ten rows into it.

You may need to close the Form in order to do so, but I don't think
so. It will in any case be easier to manage than a MakeTable query,
since you won't need to worry about deleting a table and breaking all
the forms and reports which use that table!

John W. Vinson[MVP]

John,

That sounds like a very good idea. Though it is essential that I don't have
to close the form, are there any pointers as to the best approach for not
closing the form. Should I use code or macros?

Meanwhile, I'll give it a try and report back to you.

Thanks for understanding my problem.

 

[Guest]

John,

That sounds like a very good idea. Though it is essential that I don't have
to close the form, are there any pointers as to the best approach for not
closing the form. Should I use code or macros?

Meanwhile, I'll give it a try and report back to you.

Thanks for understanding my problem.

hello Again John,

I tried the method you suggested, but Access still tells me that the 'Table
is already opened exclusively, or is already open through the user interface
and cannot be manipulated programmatically'.

the water's beggining to rise, the situation is getting desperate...

gulp...

 

[John Vinson]

hello Again John,

I tried the method you suggested, but Access still tells me that the 'Table
is already opened exclusively, or is already open through the user interface
and cannot be manipulated programmatically'.

the water's beggining to rise, the situation is getting desperate...

Please post your code. It's possible to do this right, and evidently
you're doing it wrong - but since we cannot see WHAT you're doing...

John W. Vinson[MVP]

 

[John Vinson]

John,

I have the following code on a button on the form.

Private Sub Command20_Click()
DoCmd.OpenQuery "Final_Top10_Points_A"
End Sub

ANd you gt the error when you click the button?

Evidently the query (which I cannot see from here)
Final_Top10_Points_A is attempting to modify data in a recordset which
is already open on your form (which I also cannot see from here).

You MUST - no options, sorry! - either close the Form or change its
Recordsource to blank in order to run a query which will modify the
records which the form is displaying.

John W. Vinson[MVP]

 

[Gary Walter]

Apologies for butting in...

I have a situation similar to your problem
where I have used the following method
(maybe it will help)

I have a table "tblTextbookOrderWorksheet"

A form is based on a query into this table "qryTOW"

I made a copy of "tblTextbookOrderWorksheet"
and called it "tblTOWBlank"

I went through design of "tblTOWBlank" and allowed
all fields to be "blank" then set it up with one "blank"
record (one inconsequential field had a value).

I then made a copy of "qryTOW" calling it "qryTOWBlank"
and made it use "tblTOWBlank" instead of "tblTOW"

So, on this form I have a command button where the user
can get a new Term, i.e., fill "tblTextbookOrderWorksheet"
with data from a different Term.

Private Sub cmdProcessAnotherTerm_Click()
On Error GoTo Err_cmdProcessAnotherTerm_Click
Dim strDocName As String

'unlink recordsource
Me.RecordSource = "qryTOWBlank"
Me.Requery

'open modal form to get data for new Term
strDocName = "frmProcessAnotherTerm"
DoCmd.OpenForm stDocName, acNormal, , , , acDialog

'relink to new data now in "tblTextbookOrderWorksheet"
Me.RecordSource = "qryTOW"

Me.Requery

Exit_cmdProcessAnotherTerm_Click:
Exit Sub

Err_cmdProcessAnotherTerm_Click:
MsgBox Err.Description
Resume Exit_cmdProcessAnotherTerm_Click

End Sub

I've left out a lot that was irrelevant to your situation
(like recordsources of subforms and various controls
and other error checking). And I know you will not
need to open another modal form to change contents
of table.

At a minimum, you could replace from above code

'open modal form to get data for new Term
strDocName = "frmProcessAnotherTerm"
DoCmd.OpenForm stDocName, acNormal, , , , acDialog

with executing your make table query

DoCmd.RunSQL "Final_Top10_Points_A"

As John pointed out, typically one would change
your query to an append query, then empty table,
then run append query. If the name of your table
was "t1" and the append query was "qryappend"
(and you have set reference to DAO in References)

CurrentDb.Execute "DELETE * FROM t1", dbFailOnError
CurrentDb.Execute "qryappend", dbFailOnError

ANd you gt the error when you click the button?

Evidently the query (which I cannot see from here)
Final_Top10_Points_A is attempting to modify data in a recordset which
is already open on your form (which I also cannot see from here).

You MUST - no options, sorry! - either close the Form or change its
Recordsource to blank in order to run a query which will modify the
records which the form is displaying.

John W. Vinson[MVP]
John,

I'm slightly confused. I believe i have done what you suggested, which was
to clear out (delete) the host query, then make an append query. You said
you
don't think i will have to close the form in order to do so, but now you
seem
100% definate that I will have to close the form.

Here is the mechanics of the queries/process.

The table 'Final Top10A' is what I use to populate the form

Delete Top 10 Points A
(This is the Query that clears the table 'Final Top10A'.

DELETE [Final Top10A].Run_No, [Final Top10A].Point_Quiz_ID, [Final
Top10A].Run_point_Venue_A, [Final Top10A].Run_point_Address_A
FROM [Final Top10A];

Final_Top10_Points_A
(This is the Query that populates the table 'Final Top10A'.)

INSERT INTO [Final Top10A] ( Run_No, Point_Quiz_ID, Run_point_Venue_A,
Run_point_Address_A )
SELECT [Top 10 Points generator].Run_No, [Top 10 Points
generator].Point_Quiz_ID, [Top 10 Points generator].Run_point_Venue_A,
[Top
10 Points generator].Run_point_Address_A
FROM [Top 10 Points generator];


If it is definately the case that I cannot do this while the form is open;
then is there another process that I can use to enable me to update a
table
based form with the contents of a query based table, based on a random
selection of records taken from a master table.

regards

Eric

 

[Gary Walter]

Thanks for your input on this problem. I am not a greatly experienced
user,
so I will have to understand your feedback before trying to accomplish
what
you have. Can you shed some more light on this part of the process please.

I went through design of "tblTOWBlank" and allowed
all fields to be "blank" then set it up with one "blank"
record (one inconsequential field had a value).

You made a copy of your table and maybe named it

"tblsomethingBlank"

In the Database Window, if you select "Tables"
from the list of Objects on the left, you should
click on that table in the right pane.

Then click on the "Design" icon.

You should get a window with all the Field Names
for your table. For each field, click to left of name
to select the field, then in "General" properties at
bottom of window, set

Required = No

if field is type text, also set

Allow Zero Length = Yes

Close window and answer yes to save changes.

Open table and make sure you delete all records.

Then, add one record where you set some
inconsequential field to something (like a text
field to a SPACE or two).

Because this "blank" table has one record,
when you temporarily change form's recordsource
to query of blank table, all the controls on the
form are "happy," i.e., they won't show something
like "#Name" or "#Error"

If table had had no records, then form would have
gone completely blank which confuses users.