Running Update Macro disables switchboard

[Smilin Jack]

The update macros empty and refill related tables of data. The macros work
well, but when finished the switchboard buttons do not work. My work-around
is cheesy -I included an exit command at the end so the user has to restart
the database. How can I either re-enable the switchboard or keep it from
becoming disabled by the macro?
I am not a novice at this, but I am ceratinly no expert, either.

 

[Steve Schapel]

Smilin Jack,

Can you please give a rundown of the macro, showing all Actions and
significant Arguments? Thanks. Also, what event is the macro run from?
Is it via the Click event of a command button? If so, is the command
button on the Switchboard form?

 

[Smilin Jack]

Steve -

The macro begins with 'setwarnings' to 'no,' and echo turned off. It then
deletes several reports & 1 query using the 'delete object' command.
Next, it runs a saved import, bringing the replacement data into new tables
in the database. The import also includes replacement reports and the
replacement query.
It then runs several sets (4) of the following:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
OpenQuery [Append Query] (this is from the imported data)
Close

Next, it runs 4 deletes:
DeleteObject [Imported Tables]
Lastly, a message box:
MsgBox ['Updates Complete']
As mentioned in my first message, I placed a final line in ['Exit'] to close
the database, as a work around.

I had originally placed command buttons for these macros (2 - not exactly
the same, but similar - both causing the same error) on the switchboard, but
the system completely locked up after running them. I settled for leaving
them in the navigation pane as a double click. That way I can at least close
the database properly - the only issue is that none of the switchboard
commands work. After re-opening the database, everything works as it should,
and the updates are in place.

~SJ

 

[Steve Schapel]

Smilin Jack,

Do you have an action at the end of the macro to reset Echo on?

By the way, not related to your problem I think, but these actions:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
… could be replaced by a single OpenQuery action to run a Delete Query.

What are the reports and query that you are deleing and importing? Are
the imported objects different from the deleted ones? Just curious, but
can't imagine why you would be doing this.

 

[Smilin Jack]

Steve -

Wow...great questions. I've always worked through this stuff myself - never
had anyone around to ask for help!

No - I did not think to reset the echo back to on. I will do that.
I did the extra steps in the macro to empty the table because it was faster
for me to put together at the time. I will change that to a delete query -
makes a lot more sense.

The reports have products & pricing and are sent out monthly - about a week
prior to the month they are effective. In short, the report gets the pricing
data as follows:
Products table:
Product info (20+ fields); price field (= regular price - 1 field); "sale"
price (= this month's special price - 4 fields); retail price ( = (price or
sale price) + retail margin - 1 field)
Regular prices don't change that often, but sale prices go on and off. Since
we learn of the sale prices well in advance, I set 4 fields for that so I
could enter them as they come in, without changing the current pricing.
[first field handles Jan/May/Sept., second field handles Feb/June/Oct, etc.
That way I can enter May sale prices after January 31st, and so on.

I delete the old query as the new one will ask for a different 'sale price'
field.
I delete the old report because the new one will refer to the new query pus,
it has the new month in the title.

Again, those are just quick fixes, and probably not very efficient. They
have filled-in for my lack of expertise at the moment.

I can explain some of that further if that's a bit fuzzy.

~SJ

Smilin Jack,

Do you have an action at the end of the macro to reset Echo on?

By the way, not related to your problem I think, but these actions:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
… could be replaced by a single OpenQuery action to run a Delete Query.

What are the reports and query that you are deleing and importing? Are
the imported objects different from the deleted ones? Just curious, but
can't imagine why you would be doing this.

--
Steve Schapel, Microsoft Access MVP

Steve -

The macro begins with 'setwarnings' to 'no,' and echo turned off. It then
deletes several reports & 1 query using the 'delete object' command.
Next, it runs a saved import, bringing the replacement data into new tables
in the database. The import also includes replacement reports and the
replacement query.
It then runs several sets (4) of the following:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
OpenQuery [Append Query] (this is from the imported data)
Close

Next, it runs 4 deletes:
DeleteObject [Imported Tables]
Lastly, a message box:
MsgBox ['Updates Complete']
As mentioned in my first message, I placed a final line in ['Exit'] to close
the database, as a work around.

I had originally placed command buttons for these macros (2 - not exactly
the same, but similar - both causing the same error) on the switchboard, but
the system completely locked up after running them. I settled for leaving
them in the navigation pane as a double click. That way I can at least close
the database properly - the only issue is that none of the switchboard
commands work. After re-opening the database, everything works as it should,
and the updates are in place.

 

[Steve Schapel]

Smilin Jack,

Ok, I understand. And you are right - it is a fairly inefficient (and
unnecessarily complicated) way of going about it. The key to fixing it
would be to split the Sale Prices out into a separate table, with each
item entered as a separate *record*. So for example you would need
fields like this:
ProductID (or ProductCode or whatever it is to relate this tabel to the
Products table)
NameOfMonth
SpecialPrice

Hope you get the idea.

Then you can just make one query for your report, with criteria
referenced for example to a criteria entry on a form, and the report is
based on this query, so you should never need to touch the design of the
query or the report, and you should never need to delete or replace them.

 

[Smilin Jack]

Turning the echo back on at the end of the script returned the same result.
The switchboard still does not function until I close & reopen the database.

Steve -

Wow...great questions. I've always worked through this stuff myself - never
had anyone around to ask for help!

No - I did not think to reset the echo back to on. I will do that.
I did the extra steps in the macro to empty the table because it was faster
for me to put together at the time. I will change that to a delete query -
makes a lot more sense.

The reports have products & pricing and are sent out monthly - about a week
prior to the month they are effective. In short, the report gets the pricing
data as follows:
Products table:
Product info (20+ fields); price field (= regular price - 1 field); "sale"
price (= this month's special price - 4 fields); retail price ( = (price or
sale price) + retail margin - 1 field)
Regular prices don't change that often, but sale prices go on and off. Since
we learn of the sale prices well in advance, I set 4 fields for that so I
could enter them as they come in, without changing the current pricing.
[first field handles Jan/May/Sept., second field handles Feb/June/Oct, etc.
That way I can enter May sale prices after January 31st, and so on.

I delete the old query as the new one will ask for a different 'sale price'
field.
I delete the old report because the new one will refer to the new query pus,
it has the new month in the title.

Again, those are just quick fixes, and probably not very efficient. They
have filled-in for my lack of expertise at the moment.

I can explain some of that further if that's a bit fuzzy.

~SJ

Smilin Jack,

Do you have an action at the end of the macro to reset Echo on?

By the way, not related to your problem I think, but these actions:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
… could be replaced by a single OpenQuery action to run a Delete Query.

What are the reports and query that you are deleing and importing? Are
the imported objects different from the deleted ones? Just curious, but
can't imagine why you would be doing this.

--
Steve Schapel, Microsoft Access MVP

Steve -

The macro begins with 'setwarnings' to 'no,' and echo turned off. It then
deletes several reports & 1 query using the 'delete object' command.
Next, it runs a saved import, bringing the replacement data into new tables
in the database. The import also includes replacement reports and the
replacement query.
It then runs several sets (4) of the following:
OpenTable
RunCommand [SelectAllRecords]
RunCommand [Delete]
OpenQuery [Append Query] (this is from the imported data)
Close

Next, it runs 4 deletes:
DeleteObject [Imported Tables]
Lastly, a message box:
MsgBox ['Updates Complete']
As mentioned in my first message, I placed a final line in ['Exit'] to close
the database, as a work around.

I had originally placed command buttons for these macros (2 - not exactly
the same, but similar - both causing the same error) on the switchboard, but
the system completely locked up after running them. I settled for leaving
them in the navigation pane as a double click. That way I can at least close
the database properly - the only issue is that none of the switchboard
commands work. After re-opening the database, everything works as it should,
and the updates are in place.

 

[Steve Schapel]

Smilin Jack,

I don't know the explanation for this behaviour. If you like, I can
quickly test it for you if you would be able to zip up the .accdb file
and email it to me. Use this email address: steves AT mvps DOT org