I submitted a post some time ago to look at solutions to report query errors
In the example below i have a query "qry_Append_LTD" which is an append query. Essentually I want the code below it to report an error if the reord cannot be appended. However, in practice the query appends but the error says it doesn't

Any ideas

'Append the PAF to the syste
DoCmd.OpenQuery "qry_Append_LTD", acNormal, acEdi

On Error GoTo ErrorLabe
DoCmd.SetWarnings Fals
DoCmd.OpenQuery "qry_Append_LTD", acNormal, acEdi
CurrentDb.Execute "qry_Append_LTD", dbFailOnErro
MsgBox "PAF has been saved successfully
ExitLabel
DoCmd.SetWarnings Tru
Exit Functio

ErrorLabel
MsgBox "The record could not be added.
Resume ExitLabel

Bruce,

You've opened the same query twice. That's where the error is coming from.
This is all you need.

'Append the PAF to the system
On Error Resume Next
CurrentDb.Execute "qry_Append_LTD", dbFailOnError
If Err <> 0 Then
MsgBox "The record could not be added."
Else
MsgBox "PAF has been saved successfully"
End If

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"Bruce" <(E-Mail Removed)> wrote in message
news:BF928939-DF70-4437-A257-(E-Mail Removed)...
> I submitted a post some time ago to look at solutions to report query
errors.
> In the example below i have a query "qry_Append_LTD" which is an append
query. Essentually I want the code below it to report an error if the reord
cannot be appended. However, in practice the query appends but the error
says it doesn't.
>
> Any ideas?
>
>
> 'Append the PAF to the system
> DoCmd.OpenQuery "qry_Append_LTD", acNormal, acEdit
>
> On Error GoTo ErrorLabel
> DoCmd.SetWarnings False
> DoCmd.OpenQuery "qry_Append_LTD", acNormal, acEdit
> CurrentDb.Execute "qry_Append_LTD", dbFailOnError
> MsgBox "PAF has been saved successfully"
> ExitLabel:
> DoCmd.SetWarnings True
> Exit Function
>
> ErrorLabel:
> MsgBox "The record could not be added."
> Resume ExitLabel

Thanks graham. This is part of where I am confused

I have tried your code and it does not work in my situation. There are no records in the destination table but still it wont appent with the execute command

When is use DoCmd.OpenQuery "qry_Append_LTD", acNormal, acEdit it will appeand

What is the difference? Note I tried these separately

Bruce,

Can you show me the query?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"Bruce" <(E-Mail Removed)> wrote in message
news:78A40E4C-CF51-4554-A5AC-(E-Mail Removed)...
> Thanks graham. This is part of where I am confused.
>
> I have tried your code and it does not work in my situation. There are no
records in the destination table but still it wont appent with the execute
command.
>
> When is use DoCmd.OpenQuery "qry_Append_LTD", acNormal, acEdit it will
appeand.
>
> What is the difference? Note I tried these separately

Hi Graham
Appreciate your help. Ive just had a thought. Is it becase I have parameters from my form. If so, how do I get round this

Bruc

INSERT INTO tbl_PAF ( CustomerID, ProductID, Price, DealID, StartDate, EndDate, PAFID
SELECT [Forms]![frm_PAF_Maintenence]![Combo_Customer] AS CustomerID, qry_ListPrice.ProductID, qry_ListPrice.LTD_Temp, [Forms]![frm_PAF_Maintenence]![Combo_DealType] AS DealID, [Forms]![frm_PAF_Maintenence]![txt_StartDate] AS StartDate, [Forms]![frm_PAF_Maintenence]![txt_EndDate] AS EndDate, [Forms]![frm_PAF_Maintenence]![txt_PafID] AS PafI
FROM qry_ListPric
WHERE (((qry_ListPrice.LTD_Temp) Is Not Null))

Bruce,

<<Is it becase I have parameters from my form>>
As long as the form is open and the query is going to a Jet database, then
no, this isn't the problem.

The query seems OK, but it references two other queries, qry_ListPrice and
qry_ListPrice. Can you show me those too?

Also, can you try this please (humour me):

'Append the PAF to the system
Dim db As Database

Set db = CurrentDb

On Error Resume Next

db.Execute "qry_Append_LTD", dbFailOnError
If Err <> 0 Then
MsgBox "The record could not be added."
Else
MsgBox "PAF has been saved successfully"
End If

Set db = Nothing

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"Bruce" <(E-Mail Removed)> wrote in message
news:3EE2CEF9-0626-4DCD-8B51-(E-Mail Removed)...
> Hi Graham,
> Appreciate your help. Ive just had a thought. Is it becase I have
parameters from my form. If so, how do I get round this?
>
> Bruce
>
> INSERT INTO tbl_PAF ( CustomerID, ProductID, Price, DealID, StartDate,
EndDate, PAFID )
> SELECT [Forms]![frm_PAF_Maintenence]![Combo_Customer] AS CustomerID,
qry_ListPrice.ProductID, qry_ListPrice.LTD_Temp,
[Forms]![frm_PAF_Maintenence]![Combo_DealType] AS DealID,
[Forms]![frm_PAF_Maintenence]![txt_StartDate] AS StartDate,
[Forms]![frm_PAF_Maintenence]![txt_EndDate] AS EndDate,
[Forms]![frm_PAF_Maintenence]![txt_PafID] AS PafID
> FROM qry_ListPrice
> WHERE (((qry_ListPrice.LTD_Temp) Is Not Null));
>

Graham
There is only one query behind this, qry_Listprice. See belo

If there is no problem here I will point out I am running Access2000 SP3 and have ADo2.1 and DAO 3.6 as references.

Bruc

SELECT [tbl_Channel].[ChannelID], [tbl_ProductHeader].[ProductID], [tbl_ProductHeader].[Desciption], [tbl_Pricing].[ListPrice], [tbl_Pricing].[LTD_Temp], [tbl_Pricing].[STD_Temp], [tbl_Category].[CategoryID], [tbl_Pricing].[StartDate], [tbl_Pricing].[EndDate
FROM (tbl_Category INNER JOIN tbl_ProductHeader ON [tbl_Category].[Category]=[tbl_ProductHeader].[Category]) INNER JOIN (tbl_Channel INNER JOIN tbl_Pricing ON [tbl_Channel].[Channel]=[tbl_Pricing].[Channel]) ON [tbl_ProductHeader].[ProductID]=[tbl_Pricing].[ProductID
WHERE ((([tbl_Channel].[ChannelID])=[Forms]![frm_PAF_Maintenence]![Combo_Channel]) And (([tbl_Category].[CategoryID])=[Forms]![frm_PAF_Maintenence]![Combo_Category]) And (StartDate<=CLng(Format(Date(),"yyyymmdd")) And EndDate>=CLng(Format(Date(),"yyyymmdd"))))

Bruce,

I can't see anything wrong here either. What error message are you getting
when your code hits CurrentDb.Execute?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"Bruce" <(E-Mail Removed)> wrote in message
news:459455EA-448C-4940-BC7E-(E-Mail Removed)...
> Graham,
> There is only one query behind this, qry_Listprice. See below
>
> If there is no problem here I will point out I am running Access2000 SP3
and have ADo2.1 and DAO 3.6 as references.
>
> Bruce
>
>
> SELECT [tbl_Channel].[ChannelID], [tbl_ProductHeader].[ProductID],
[tbl_ProductHeader].[Desciption], [tbl_Pricing].[ListPrice],
[tbl_Pricing].[LTD_Temp], [tbl_Pricing].[STD_Temp],
[tbl_Category].[CategoryID], [tbl_Pricing].[StartDate],
[tbl_Pricing].[EndDate]
> FROM (tbl_Category INNER JOIN tbl_ProductHeader ON
[tbl_Category].[Category]=[tbl_ProductHeader].[Category]) INNER JOIN
(tbl_Channel INNER JOIN tbl_Pricing ON
[tbl_Channel].[Channel]=[tbl_Pricing].[Channel]) ON
[tbl_ProductHeader].[ProductID]=[tbl_Pricing].[ProductID]
> WHERE
((([tbl_Channel].[ChannelID])=[Forms]![frm_PAF_Maintenence]![Combo_Channel])
And
(([tbl_Category].[CategoryID])=[Forms]![frm_PAF_Maintenence]![Combo_Category
]) And (StartDate<=CLng(Format(Date(),"yyyymmdd")) And
EndDate>=CLng(Format(Date(),"yyyymmdd"))));
>

Bruce,

No error code. When I step through the following the if statement is true
i.e. "The record could not be added.

when i run "qry_Append_LTD" by open query rather than execute its ok. I am totally puzzled...

CurrentDb.Execute "qry_Append_LTD", dbFailOnErro
MsgBox (Err
If Err <> 0 The
MsgBox "The record could not be added.
Els
MsgBox "PAF has been saved successfully
End I

Bruce,

If the IF statement is true, then there MUST be an error. Change your code
as follows, and let me know what the result is:
CurrentDb.Execute "qry_Append_LTD", dbFailOnError
MsgBox (Err)
If Err <> 0 Then
MsgBox Err.Number & vbCrlf & Err.Description
Else
MsgBox "PAF has been saved successfully"
End If

If this doesn't show anything useful, it might be time to send me the
database so I can take a look. I'm working in the dark here, because I don't
have your table structure, data, and queries to test.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyT...764559036.html


"Bruce" <(E-Mail Removed)> wrote in message
news:3DBD106B-8981-4AFB-8053-(E-Mail Removed)...
> Bruce,
>
> No error code. When I step through the following the if statement is true.
> i.e. "The record could not be added."
>
> when i run "qry_Append_LTD" by open query rather than execute its ok. I am
totally puzzled....
>
> CurrentDb.Execute "qry_Append_LTD", dbFailOnError
> MsgBox (Err)
> If Err <> 0 Then
> MsgBox "The record could not be added."
> Else
> MsgBox "PAF has been saved successfully"
> End If
>