Need to ignore Microsoft Access can't append all the records in the append query

[DNASok]

I have a form that on open runds 5ea Queries. These queries are used to append needed data to a table where I have 2ea fields set as primary keys to ensure the combination of the two (2) are not added multiple times.

I want it to run this way as it supports and works perfectly for the other tasks I am using this set-up for. However, I would like to be able to not have the "Microsoft Access can't append all the records in the append query" error pop-up for all five (5) append queries.

I have the "Confirm" "Record changes", "Document deletions", and "Action queries" unchecked within my "Client Settings" with no change.

The code I am using on my form open is:

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenQuery "DOItemQuery1"
DoCmd.OpenQuery "DOItemQuery2"
DoCmd.OpenQuery "DOItemQuery3"
DoCmd.OpenQuery "DOItemQuery4"
DoCmd.OpenQuery "DOItemQuery5"
End Sub

Is there a code which I can place within mine to ignore the error for all of the append queries?

Thank you,

 

[jdraw]

Can you look at one of your queries in design view, select SQL view, copy the query sql and post it?

Once readers get a look at what your query is trying to do, they may have some options.

I think Access is trying to help you.

 

[DNASok]

Can you look at one of your queries in design view, select SQL view, copy the query sql and post it?

Once readers get a look at what your query is trying to do, they may have some options.

I think Access is trying to help you.

Here is the SQL:
INSERT INTO [DelieveryOrders] (Item, OrderNumber, CLIN, Customer, Email, [Contract Date] )
SELECT Orders.Item1, Orders.[Order Number1], Orders.[CLIN Number1], Orders.[Customer Name], Orders.[Customer Email], Orders.[Cont Date1]
FROM Orders
WHERE (((Orders.Item1) Is Not Null) AND ((Orders.[Order Number1]) Is Not Null) AND ((Orders.[CLIN Number1]) Is Not Null) AND ((Orders.[Customer Name]) Is Not Null))
ORDER BY Orders.[Order Number1];

The remaining 4ea queries are identical, but with 2/3/4/5 replacing the 1 in the code above.

The reason I am doing this is:
1. A customer places an order with up to 5ea items
2. These queries find what was ordered on each line, then places them on a single table.
- I used to simply clear this query after it was no longer needed, and it was rerun automatically every time the data is required.
- However, I now need this data to remain constant (not cleared) to allow a single source for follow-on data to be added
3. The Order Number and CLIN Number are set as primary keys to ensure duplicates of them are not made when the queries are run

I have attempted to change the way the database is built as I identified a way to get around having item 1/2/3/4/5 on the same line, and it also allowed for more than 5ea to be entered (eventhough this will never occur). This works perfect, but causes other issues for searching and updating orders due to the way they are processed and grouped.

 

[CJ_London]

You can just suppress the messages

Private Sub Form_Open(Cancel As Integer)
[COLOR=red]DoCmd.SetWarnings False[/COLOR]
DoCmd.OpenQuery "DOItemQuery1"
DoCmd.OpenQuery "DOItemQuery2"
DoCmd.OpenQuery "DOItemQuery3"
DoCmd.OpenQuery "DOItemQuery4"
DoCmd.OpenQuery "DOItemQuery5"
[COLOR=red]DoCmd.SetWarnings True[/COLOR]
End Sub

 

[DNASok]

You can just suppress the messages

Private Sub Form_Open(Cancel As Integer)
[COLOR=red]DoCmd.SetWarnings False[/COLOR]
DoCmd.OpenQuery "DOItemQuery1"
DoCmd.OpenQuery "DOItemQuery2"
DoCmd.OpenQuery "DOItemQuery3"
DoCmd.OpenQuery "DOItemQuery4"
DoCmd.OpenQuery "DOItemQuery5"
[COLOR=red]DoCmd.SetWarnings True[/COLOR]
End Sub

That worked...I cannot thank you enough!

 

[jdraw]

Getting rid of the message is one thing, but isn't there some underlying reason why

"Microsoft Access can't append all the records in the append query"

.

If you turn the messages off, you won't see the error, but seems there is an error that should be addressed.

 

[CJ_London]

yes - duplicates, which the poster wants to exclude

Agree the queries could be rewritten to check the existence before updating but at the time, did not have the query!

 

[gemma-the-husky]

i agree with jdraw.

suppressing error messages is all very well, but if you end up with incorrect data, you will be even more at sea.

managing data correctly is a most important part of the developers job.

if all the rows are identical in all cases, then you have true duplicates. in which case you could set the append query to "unique values" - which will correctly select distinct rows, without needing to suppress errors, if they are not 100% identical, then suppressing errors will discard some rows at random, and you would not be aware of that at all.