CANCELED PROCEDURE

[mmmbl]

In one of the send email procedures i use a single query to send cost
information to my vendors. This query is named "qry_COST INFORMATION" but
during the process it will get renamed to the vendor number for that
particular vendor.The procedure is working perfectly until some cancels in
between the process. When you rerun the process you will get the error an
error message that "qry_COST INFORMATION" cannot be found. This is because it
was not renamed back to the original name due to cancellation. How can i
rename the query back to the original name when the user cancel the
procedure? Here is the part where it gets rename

If IsNull(rstObjects![supplier_email]) Or rstObjects![supplier_email] = " "
Then
strEmail = rstObjects![broker_rep_email]
Else
strEmail = rstObjects![supplier_email]
End If

If IsNull(rstObjects![broker_rep_email]) Then
strEmail = rstObjects![supplier_email]
Else
strEmail = rstObjects![supplier_email] & ";" &
rstObjects![broker_rep_email]
End If


DoCmd.Rename newName, acQuery, oldName
oldName = rstObjects![vnd_num] - this is to
temporarily store the vendor number that was just sent and this

thank you

 

[John W. Vinson]

In one of the send email procedures i use a single query to send cost
information to my vendors. This query is named "qry_COST INFORMATION" but
during the process it will get renamed to the vendor number for that
particular vendor.

Rather than renaming the query, why not just make a copy of it?

 

[David H]

For that matter, why are you sending 'query' as opposed to an Access report?
What does the query contain and why can't it be in the form of a report?

 

[mmmbl]

The vendor sends the file back in the same format with any updates on their
costs and deals. The XLS format is then save on the network and the database
goes and finds any new spreadsheets posted on the lan and process the cost
changes by creating a data file for upload. The report can't be used because
it would be hard to extract the data from it.

I cannot make copy of it because I have over 1000 vendors on the list.

I need to find a method to rename the query back when the user cancels the
procedure.

 

[John W. Vinson]

I need to find a method to rename the query back when the user cancels the
procedure.

Again:

Why not use TWO QUERIES?

The first query would be the "design master" if that's what you want to call
it. You would make a COPY of the query under the new name, with any needed
changes.

You would export from the copy, and then delete it.

Your design master copy would be untouched and would be available for the next
use.