Removal of confirmation message (2003)

Topic

New Reply

For some reason when l have unchecked (i.e not ticked) the

Reply | Quote

Replies

Try unticking “Confirm document deletions”.

The message you have is about the deletion of a table, not the running of an action query. An action query affects records in tables, or makes new tables.

Reply | Quote

I tried this but still it prompted me for confirmation message, any other suggestions as to how l could eradicate the confirmation message?

Reply | Quote

I have just tested this and see what the problem is.

You are running a make table query, which wants to recreate a table that already exists.

The only way I could get rid of the message was to explicitly delete the table myself before running the query.

DoCmd.DeleteObject acTable, strtablename
DoCmd.OpenQuery “strQueryName”, acViewNormal

Reply | Quote

I have modified you coding with the querties and tables l am using, see below:

table is: tbl AVTS_final_output
query is: qry create tbl AVTS final output

DoCmd.DeleteObject acTable strtbl AVTS_final_output DoCmd.OpenQuery “str qry create tbl AVTS final output”, acviewNormal

I now have a few questions:

1. Will the above line of coding work, (i.e is it valid?)

2. Is this coding called from a macro , if so how do l put it in the macro?
Or do l call it from a command button, if l call it from a command button where does it go in the coding?

Reply | Quote

[indent]

---

DoCmd.DeleteObject acTable strtbl AVTS_final_output DoCmd.OpenQuery “str qry create tbl AVTS final output”, acviewNormal

---

[/indent]
This needs to be two separate lines
For the names of the two things referred to (table and query) you either assign those names to variables, or include the name directly in the commnad with ” ” marks around them. So I suggest:

DoCmd.DeleteObject acTable “tbl AVTS_final_output”
DoCmd.OpenQuery “qry create tbl AVTS final output”, acviewNormal

This looks OK to me.

To use this code, create a command button. (if the command button wizard starts to run click cancel.)
Display the properties sheet and give the button a caption (the word you see on the button) and find the Click Event.

On the properties sheet click the three dots that appear when you click your mouse in the On Click line.
this will take you to the VBA Editor.

Put the after the the line that starts

Private Sub….
put it here

End Sub

Reply | Quote

I tried this but got an error, see attachment.
Any ideas as to the cause of this error?

Reply | Quote

Try putting a comma after acTable

Reply | Quote

I still appear to be getting an error message. Attached is the database l am using and the error l am getting.

Any help is appreciated. If you click on the button

Reply | Quote

The openquery is bombing out, the query name is incorrect.

Reply | Quote

That works great , thanks.
What l now require is how to integrate the coding so that it is combined into the

Reply | Quote

I’m feeling lazy this morning. where do you wish to put the 2 lines of code? Before or after the RunMacro code?

sorry about this but GET RID OF MACROS !!!!!

Reply | Quote

You can convert the existing macro to VBA , then paste the new code in before the existing two lines.
.At the database window, go to Macros, select your macro then go to Tools..Macros..convert Macros to VB
When it is finished you will find a mOdule called “converted Macro..”

Copy this code from it:
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJRennDesktopAccess stuffAVTS.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry remove duplicates tbl AVTS”
DoCmd.OpenQuery “qry create tbl AVTS no duplicates”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry create tbl AVTS no duplicates”

and paste it into your existing code where you want it. I thinjk it goes before the existing lines, but I am not sure.

Reply | Quote

The code appears to be working fine , but for some reason the message is still appearing which l want to remove by using this code, see attachment.
It therefore appears there is something wrong with the coding, any help would be appreciated.

Private Sub Command40_Click()
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJBloggsDesktopAccess stufftestdata.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry remove duplicates tbl AVTS”
DoCmd.OpenQuery “qry create tbl AVTS_data_no_duplicates”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry create tbl AVTS_data_no_duplicates”
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal
End Sub

Reply | Quote

Enclose the code you have like:
DoCmd.SetWarnings False

DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry remove duplicates tbl AVTS”
DoCmd.OpenQuery “qry create tbl AVTS_data_no_duplicates”, acViewNormal, acEdit
DoCmd.Close acQuery, “qry create tbl AVTS_data_no_duplicates”
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal

DoCmd.SetWarnings True

Reply | Quote

I did not really read the code I gave you before, but I now see that you are creating the new table twice, by running this line two times:

DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal

The second time we have a line to delete the table first. The first time you run it you don’t have this line in front of it.

Also you don’t need to close action queries. When you open them they just run. So I think you can simplify this to:

Private Sub Command40_Click()
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJBloggsDesktopAccess stufftestdata.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal
End Sub

Reply | Quote

I still appear to be having problems with the coding.

Result 1 is the output from running the query for the first time. (Is it possible to close the datasheet view in VBA?) and return to the main menu automatically?
Result 2 is the output from running the query for the second time.
I still am getting an error when l try and run the coding for a seconf time
I fully understand why the error is being caused as there is no table, but l need to run the create tbl query in order to create it first!
Is there any way to resolve this issue?

See attachments for results.

Reply | Quote

I am not sure that I understand.

If the table does not already exist when you try to delete it you will get an error.

You could just put in: “On Error Resume Next ” as a line before the delete table line.
Or you create a proper error handler, and use
if err. number = 7874 then
Resume Next
else
continue with normal error handling.

If your queries are action queries, then they should not open in datasheet view. what is causing the datasheet view to open?

Reply | Quote

This seems quite complex to do.
Can you explain how l could put an on “On Error Resume Next ” routine or creating a proper error handling routine.
As my VBA coding is not all that good, could you tell me as to how this is done or what coding l need.

Reply | Quote

The help files that come with Access are full of examples that you should study. It is unreasonable to expect others to write each line of code for you, particularly when we are all volunteers. We’re all glad to help, but you have to take the initiative yourself to learn the product. If you search this forum, you’ll find lots of recommendations on books and other sites that can help you learn the basics of Access. You can make good use of those resources and of all the examples of code already in the Lounge database.

Reply | Quote

You just write in the words I gave you.

Private Sub Command40_Click()
DoCmd.TransferText acImportDelim, “”, “tbl AVTS_data”, “C:Documents and SettingsJBloggsDesktopAccess stufftestdata.txt”, False, “”
DoCmd.OpenQuery “qry remove duplicates tbl AVTS”, acViewNormal, acEdit
On Error Resume Next
DoCmd.DeleteObject acTable, “tbl AVTS_data_no_duplicates”
DoCmd.OpenQuery “qry create tbl AVTS_no_duplicates”, acViewNormal
End Sub

Reply | Quote

Thanks for that advice.

Reply | Quote