Can you ignore warning on append query? 3

[Loktar]

Hello all,

I'm having a little problem that is really more of a annoyance. What I've done is create a table with a list of order numbers that needs to be kept up to date as new orders are entered into the database via a linked table from an excel spreadsheet. These order nums must be unique and data in corresponding columns must be kept intact.

I've written an append query that pulls the order numbers out of the linked table and appends them to the new table in the format I need, and will only insert new (unique) orders since the Order field has been set to only allow unique entries in the table properties.

However, when this query is run, it give the error/warning saying that, for example, only 4 of the 72 records can be appended due to the values having to be unique. This produces the results I want, but I'm wondering if there is a way for me to tell Access that I don't want to see this warning and just append the unique records. I'm calling this from VB code using DoCmd.RunSQL and I need it to be invisible to the user, not confusing them with all the info about how many records can or can't be appended. Is there anyway to bypass this?

Also, can you bypass the message that says:

You are about to append (#) row(s).
Once you click Yes this cannot be undone etc.

I've searched everywhere for an answer with no results... Thanks a ton in advance.

 

[DBAMJA]

All you need to do in your code is put a line in:
docmd.setwarnings false

then if you want to turn the warnings back on:
docmd.setwarnings true

That's it.



[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[RoyVidar]

Hi!

"surround" the runsql with:

[tt]docmd.setwarnings false
docmd.runsql "yoursql"
docmd.setwarnings true[/tt]

The last one is perhaps the most important... setting the warnings on again afterwards.

Roy-Vidar

 

[Loktar]

Thanks for the quick replies!

I knew it must have been something simple like this but I couldn't find the solution anywhere! I guess it was one of those times when the keywords I googled weren't the right ones...

Thanks again!

 

[nimarii]

i have a similar question:
i have a module that opens an excel spreadsheet, formats it, imports it, then closes it.

i dont want to save the spreadsheet after its been changed, how can i get the "do you want to save" message from appearing?

 

[RoyVidar]

I think this is quite differen't and there's a lot of assumptions here, but I'm quessing you're using some object variable holding the workbook (for instance wrk), and issue a .close on it. Try using the savechanges argument:

[tt]wrk.close false[/tt]

- if false, no saving, if true it's saved, omitted, the default message appears

Roy-Vidar

 

[nimarii]

thanks royvidar, it worked perfectly

 

[jimbo08]

Is there any way to access the "description" property of an Access query through VBA? The "desciption" I am referring to is the one available by right-clicking on the query name in the database objects window.

 

[PHV]

jimbo08, with some error trapping, you may try something like this:
CurrentDB.QueryDefs(3YourQueryName").Properties("Description")

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jimbo08]

PH:

Thank You! That worked great!

Jimbo08