guyuzB guyuzB - 2 years ago 69
SQL Question

Changing and removing the delete warning message of a query

I have 2 tables:

. Each record in
can have an unlimited number of related contacts. So, when I make a button to delete a client, It first deletes all related contacts, and then deletes the client itself.

Naturally, that makes it so when the button is pressed, I have to run through about 4 warning messages. I want to get rid of all these messages, and replace them with one.

Is this possible? Thank you.

Answer Source

As you can see, while RunSQL and OpenQuery start off simple, they end up causing you a lot of extra work. The OpenQuery command is really designed for displaying a SELECT or CROSSTAB query for the user to browse. For Action queries, use the Execute method to run saved queries and SQL strings:

  Dim dbs As DAO.Database
  Dim lngRowsAffected As Long
  Dim lngRowsDeleted As Long

  Set dbs = CurrentDb

  ' Execute runs both saved queries and SQL strings
  dbs.Execute cstrQueryName, dbFailOnError

  ' Get the number of rows affected by the Action query. You can
  ' display this to the user, store it in a table, or trigger an
  ' action if an unexpected # (e.g. 0 rows when you expect >0)
  lngRowsAffected = dbs.RecordsAffected
  dbs.Execute "DELETE FROM tblMyTable WHERE Bad", dbFailOnError

  lngRowsDeleted = dbs.RecordsAffected

You can avoid using SetWarnings because Execute doesn’t show warnings. As an added benefit, you can return the # of rows affected by the most recent action query. You can show this value to users, store it in a table, or use it to check for an unexpected result (e.g. 0 rows affected when you expect > 0).


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download