Alert Message for a Make Table Query

N

Nyla K

How do I set up a customize Alert Message to let end-users know that the
query they are about to run will delete an existing table with records in it?
I know that there's a default message for the Make Table Query, but I don't
want the end-users to have the option to delete the table IF there are
records in it. (I also want a customized message that makes sense to the
end-user.) If there are no records in the table, then the system can
continue running the query without the alert message.

In summary, if there are records in the table, a customized alert message
will pop up. If there are no records, continue running the query without
popping an alert message.

Thanks for any assistance you can provide.

Nyla
 
J

John Vinson

In summary, if there are records in the table, a customized alert message
will pop up. If there are no records, continue running the query without
popping an alert message.
Click to expand...

How about something like:

If DCount("*", [TableName]) > 0 Then
MsgBox "Your message here"
Else
<delete the table and run the make-table>
End If

One concern: Why a make-table? In my experience they are VERY rarely
truly necessary. You can base a Form, a Report, an export, even
another query on a Select Query without the overhead (and database
bloat) of creating a new table.

John W. Vinson[MVP]
 
N

Nyla K

Thanks for the tip, John.

The reason why I'm doing the make-table query is because I use that table to
update couple fields in the main table. After doing the update, then I use
the delete query. With the date range selection and department criteria to
pull certain records, I can't just use the main table as the record source to
update the two fields. Do you have an alternative solution? I'm open to any
suggestions.

Thanks,
Nyla

John Vinson said:
In summary, if there are records in the table, a customized alert message
will pop up. If there are no records, continue running the query without
popping an alert message.
Click to expand...

How about something like:

If DCount("*", [TableName]) > 0 Then
MsgBox "Your message here"
Else
<delete the table and run the make-table>
End If

One concern: Why a make-table? In my experience they are VERY rarely
truly necessary. You can base a Form, a Report, an export, even
another query on a Select Query without the overhead (and database
bloat) of creating a new table.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

Thanks for the tip, John.

The reason why I'm doing the make-table query is because I use that table to
update couple fields in the main table. After doing the update, then I use
the delete query. With the date range selection and department criteria to
pull certain records, I can't just use the main table as the record source to
update the two fields. Do you have an alternative solution? I'm open to any
suggestions.
Click to expand...

If you're doing an update query, just do an update query! I don't
understand how a make-table gets you anything at all. At the VERY
worst you can update to a (slow and inefficient but it works) DLookUp
expression.

WHat are you trying to update, and what are you trying to update it
to? What do you mean by a "record source" in this context - Forms and
Reports have recordsources, but queries don't!

John W. Vinson[MVP]
 
Top