Macro fails from button, runs when selected directly

[chris seiter]

I have a very small Access 2007 database with two tables, three queries, one
form, one macro, and one report. On the form is a button to run the macro.
The macro closes the form, runs a make table query, runs a SQL add column
query, then opens the form back up. The form's main information is pulled
from the other query, but contains linked data from the remade table. When I
run the macro by double-clicking on the macro itself, it runs correctly.
When I run it from the button, it fails on the make table, saying the
database is locked because the table is in use. I've tried deleting the
table before making it, I've tried putting a pause in the macro so it would
run a little slower, but nothing is working. The button was created using
the wizard, so nothing is wrong on that end. Any ideas?

 

[Ken Snell \(MVP\)]

If you have any form, report, or query open that is using that table (the
one you want to remake using the maketable query), you'll get that error. If
the form from which you're running the macro is using that table, that
likely is the source of the error message. The form must close entirely
before the maketable can "use" the table.

Any particular reason you're remaking the table each time? Tell us more
about what you're doing, and we may be able to suggest alternative ways to
accomplish the task.

 

[chris seiter]

It's for our softball team to keep track of the league's win-loss, and it
automatically fills the tournament bracket at the end of the year. The table
that get's remade is based on a query that sorts the win-loss table
decrementally(?). The second query adds a column that is auto-incrimentd so
that the first team in the table, which has the most wins, is #1, and the
last team, the one with the least, is #11. I didn't want to use the primary
key for this, 'cause I was always taught never use the primary key for
anything else but unique records. The bracket form uses this newly created
table to decide which teams are playing each other, the team ranked#11 plays
#6 and so on. As the season progresses, the rankings change, so that's why I
am remaking the table each week. The database has gotten a little larger,
with one more table to save who wins the tournament games, in case the
program is closed, a the update queries that save them to the table. I'm
using this as a self-study project so I can get to know access a little
better than the books can show.

The macro already closes the form it is being used from, plus the two tables
just in case I have them open, and it still doesn't fire off from the button.
It tells me the table is open when it runs the maketable, but the three
steps before it are what closes the form and tables. It's not top secret by
any means, so I can put the .mdb file somewhere if you need me to.

 

[Ken Snell \(MVP\)]

Post the database at a website and provide the URL here in this thread...
I'll take a look as soon as I have a moment.

 

[chris seiter]

www.squall.nu/seiter/database

It's gotten a little bigger since I added some more functionality, but the
issue is still the same. The button is going to be on the Win/Loss form so
that it will update the standings. If you change the win/loss records and
run the "update standings" macro directly from the navigation pane, it works.
I might have made some changes to to to try to get it to work, though. The
button on the bracket form works fine.

 

[Ken Snell \(MVP\)]

The file that you posted is corrupt, and I cannot open it.