Catch errors on append query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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/WileyTitle/productCd-0764559036.html


Bruce said:
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
Click to expand...
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.
 
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/WileyTitle/productCd-0764559036.html


Bruce said:
Hi Graham,
Appreciate your help. Ive just had a thought. Is it becase I have
Click to expand...
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,
Click to expand...
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
 
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/WileyTitle/productCd-0764559036.html


Bruce said:
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
Click to expand...
and have ADo2.1 and DAO 3.6 as references.
Bruce


SELECT [tbl_Channel].[ChannelID], [tbl_ProductHeader].[ProductID],
Click to expand...
[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
Click to expand...
[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
Click to expand...
((([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/WileyTitle/productCd-0764559036.html
 
Hi Graham

I appreciate your help on this matter. I copied the code in and it says Run time error 3061, too few parameters

At this point, rather than waste to much more of your time, I would like to send you copy of the datbase if you have a little time to look at it. At the minute ts only a model with some test data

What email should I send it to

Bruce
 
Bruce,

gseach
@
REMOVE THIS LINE
pacificdb
com
au

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia


Bruce said:
Hi Graham,

I appreciate your help on this matter. I copied the code in and it says
Click to expand...
Run time error 3061, too few parameters.
At this point, rather than waste to much more of your time, I would like
Click to expand...
to send you copy of the datbase if you have a little time to look at it. At
the minute ts only a model with some test data.
 
Bruce,

OK, both [qry_Append_LTD] and [frm_PAF_Maintenence] are corrupt!

I'd recommend re-creating both from scratch. To demonstrate the fact that
they are corrupt, follow this procedure:

1. Open [qry_Append_LTD] in design view, and remove the "Is Not Null"
criteria for LTD_Temp.

2. From the Query menu, select [Select Query].

3. Open [frm_PAF_Maintenence] in design view.

4. Select [txt_StartDate], and remove "Short Date" from its Format
property.

5. Select [txt_EndDate], and remove "Short Date" from its Format property.
Also remove the ValidationRule and ValidationText.

6. Open [frm_PAF_Maintenence] in normal view (without saving it).

7. Set the following values:

Deal = LTD
Business = RT
Category = Bun
Customer = CustomerA
PAF ID = <anything you like>
Start Date = "aaa"
End Date = "bbb"

8. Run [qry_Append_LTD]. You should see a start date and end date of "aaa"
and "bbb", respectively.

9. Change [txt_StartDate] = 1/1/2000, and change [txt_EndDate] = 2/1/2000.

10. Re-run [qry_Append_LTD]. You should see a start date and end date of
1/1/2000 and 2/1/2000, respectively.

10. Open [frm_PAF_Maintenence] in design view, and re-instate "Short Date"
and the validation stuff.

11. Re-run [qry_Append_LTD].

12. Save the form and the query, then when you re-open them both, you'll see
the problem has returned.


This clearly demonstrates that the problem can be resolved, but not using
the existing form/query.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top