Supress warning message in Append Query

[ironwood9 via AccessMonster.com]

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.

 

[Gina Whipp]

DoCmd.SetWarnings False 'Turns off message
Run you query here...
DoCmd.SetWarnings True 'Turns messages back on

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

 

[ironwood9 via AccessMonster.com]

Thanks - already tried that, didn't help

DoCmd.SetWarnings False 'Turns off message
Run you query here...
DoCmd.SetWarnings True 'Turns messages back on

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

[quoted text clipped - 7 lines]

I
run it normal, not in a procedure.

 

[Gina Whipp]

Post the code you are using in the function...

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Thanks - already tried that, didn't help

DoCmd.SetWarnings False 'Turns off message
Run you query here...
DoCmd.SetWarnings True 'Turns messages back on

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

[quoted text clipped - 7 lines]

I
run it normal, not in a procedure.

 

[ironwood9 via AccessMonster.com]

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

Post the code you are using in the function...

Thanks - already tried that, didn't help

[quoted text clipped - 8 lines]

 

[Gina Whipp]

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

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

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

Post the code you are using in the function...

Thanks - already tried that, didn't help

[quoted text clipped - 8 lines]

I
run it normal, not in a procedure.

 

[ironwood9 via AccessMonster.com]

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

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

Option Compare Database

[quoted text clipped - 47 lines]

 

[Gina Whipp]

Ironwood9,

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

Your Welcome,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

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

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

Option Compare Database

[quoted text clipped - 47 lines]

I
run it normal, not in a procedure.

 

[ironwood9 via AccessMonster.com]

Yes, I tried that and it worked perfectly... sjr

Ironwood9,

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

Your Welcome,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
Thanks - I think you're right - perhaps a better way would be to set up a

[quoted text clipped - 25 lines]

 

[Gina Whipp]

Thanks for the feedback!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Yes, I tried that and it worked perfectly... sjr

Ironwood9,

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

Your Welcome,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Gina,
Thanks - I think you're right - perhaps a better way would be to set up
a

[quoted text clipped - 25 lines]

I
run it normal, not in a procedure.

 

[aaron.kempf]

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

Thanks for the feedback!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

Yes, I tried that and it worked perfectly... sjr

Ironwood9,
It's the forest for the trees thing...  I sometimes over think thingslike
this also.
Your Welcome,
Gina Whipp
"I feel I have been denied critical, need to know, information!" - Tremors
II
http://www.regina-whipp.com/index_files/TipList.htm
Gina,
Thanks - I think you're right - perhaps a better way would be to set up
a
[quoted text clipped - 25 lines]
I
run it normal, not in a procedure.