Windows 7 - Supress warning message in Append Query

Asked By ironwood9 via AccessMonster.com on 25-Feb-09 04:43 PM
I want to run an append query in code - when I run it normally (not in a
function), what happens is that because the table has a composite PK I get a
warning message saying that not all records will be updated.  That's ok, I
expect that - having the compos. PK helps to give me unique records.

But I don't know what line of code I can add if there is one to have it run
regardless - it doesn't even give me the "warning" message that I get when I
run it normal, not in a procedure.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200902/1




Gina Whipp replied on 25-Feb-09 05:34 PM
DoCmd.SetWarnings False  'Turns off message
Run you query here...
DoCmd.SetWarnings True    'Turns messages back on

--
Gina Whipp

II

http://www.regina-whipp.com/index_files/TipList.htm
ironwood9 via AccessMonster.com replied on 25-Feb-09 05:43 PM
Thanks - already tried that, did not help


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200902/1
Gina Whipp replied on 25-Feb-09 05:59 PM
Post the code you are using in the function...

--
Gina Whipp

II

http://www.regina-whipp.com/index_files/TipList.htm
ironwood9 via AccessMonster.com replied on 25-Feb-09 07:11 PM
Option Compare Database

'------------------------------------------------------------
Function mcrRunAppendQueries()

DoCmd.SetWarnings False

On Error GoTo mcrRunAppendQueries_Err


' Run Append_MSC (To Combined) table
CurrentDb.Execute "q_FIN_DLM_Misc_Append", dbFailOnError

' Run Append_LAT (To Combined) table
CurrentDb.Execute "q_FIN_DLM_LateFee_Append", dbFailOnError

' Run Append_PPV (To Combined) table
CurrentDb.Execute "q_FIN_DEU_PPV_Usage_Append", dbFailOnError

' Run Append_BDR (To Combined) table
CurrentDb.Execute "q_FIN_DMA_BadDebtReinstate_Append", dbFailOnError

' Run Append_TWO (To Combined) table
CurrentDb.Execute "q_FIN_DMA_WriteOff_Append", dbFailOnError

' Run Append transposed version of DBD table to combined table
CurrentDb.Execute "q_Append_DBD_BadDebtRecovery", dbFailOnError

' Run Append Tax  (To Combined) table
CurrentDb.Execute "q_Append_Tax_ToCombinedTable_09", dbFailOnError

' Run Append Main (To Combined) table
CurrentDb.Execute "q_Append_Main_ToCombinedTable_09", dbFailOnError


mcrRunAppendQueries_Exit:
Exit Function

mcrRunAppendQueries_Err:
MsgBox Error$
Resume mcrRunAppendQueries_Exit


DoCmd.SetWarnings true

End Function






--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200902/1
Gina Whipp replied on 25-Feb-09 09:08 PM
I think by adding dbFailOnError, you force the code to stop and give you an
error message.  In any event try the below:

Function mcrRunAppendQueries()

DoCmd.SetWarnings False
DoCmd.OpenQuery "q_FIN_DLM_Misc_Append"
DoCmd.OpenQuery "q_FIN_DLM_LateFee_Append"
DoCmd.OpenQuery "q_FIN_DEU_PPV_Usage_Append"
DoCmd.OpenQuery "q_FIN_DMA_BadDebtReinstate_Append"
DoCmd.OpenQuery "q_FIN_DMA_WriteOff_Append"
DoCmd... balance of queries
DoCmd.SetWarnings True
End Function

--
Gina Whipp

II

http://www.regina-whipp.com/index_files/TipList.htm
ironwood9 via AccessMonster.com replied on 26-Feb-09 10:01 AM
Gina,
Thanks - I think you're right - perhaps a better way would be to set up a
separate function, and call it - don't know why I didn't think of that -
separate function with no error handling / trapping

Thanks !
Steve


--
Message posted via http://www.accessmonster.com
Gina Whipp replied on 26-Feb-09 11:36 AM
Ironwood9,

It's the forest for the trees thing...  I sometimes over think things like
this also.

Your Welcome,
Gina Whipp

II

http://www.regina-whipp.com/index_files/TipList.htm
ironwood9 via AccessMonster.com replied on 26-Feb-09 12:59 PM
Yes, I tried that and it worked perfectly... sjr


--
Message posted via http://www.accessmonster.com
Gina Whipp replied on 26-Feb-09 01:26 PM
Thanks for the feedback!

--
Gina Whipp

II

http://www.regina-whipp.com/index_files/TipList.htm
aaron.kemp replied on 05-Mar-09 10:32 PM
I would ignore Gina, her answer is to use Jet, no matter how
applicable.

I'd just move to SQL Server and learn a real ETL tool like DTS or SSIS

-Aaron




s
20febe95@uwe...
like
ors
up