Solved Error 3022 Upon Running Append Query in VBA

[HillTJ]

All,

I get error 3022..... when I run my append code on the on-click event of the button on 'frmPurchaseOrders'. When I comment out the dbfailonError of line qdf.execute dbfailonError all works fine. I've rebuilt the two table of concern & added some error code to help me understand what the error means. Don't know what to do from here. Done some reading, but nothing quite helps my case. Appreciate your help.

 

[June7]

Exactly what message does this error generate?

What purpose does tblInventoryDetails serve? Isn't this data already in tblPO_Line_Item?

 

[HillTJ]

June7, tblPO_Line_Item is used to record the 'many' items that can be added to the Purchase Order. I then 'Post' the individual line items (After ticking them as complete) to 'tblInventoryDetails'. In 'tblInventoryDetails I then add optional unique stuff like batch numbers, expiry dates. I intend then to adapt Allen Browns "Stock on Hand Code" to determine stock at any time, so I'm trying to mirror his format a bit. Still got to make forms for both Invoicing & stocktake.
If there's a better way, then I'm only too keen to learn. But, the main issue seems to be my problem with the dbFailonError line in the on click code of the button. Thanks for asking.

 

[June7]

For my initial test, I deleted all records from tblInventoryDetails. I clicked the PostInventory button and 12 records created in tblInventoryDetails.

So, what should I do to recreate the issue?

Side Note: misspelled Received as Recieved in QuantityRecieved

 

[HillTJ]

June7, i had not tried deleting all records from tblinventorydetails although i did copy & recreate the table. Have a look at the code behind the button the code worked for me until i added the dbfailonerror after the execute line. The error was something about a duplicate index. I'll run the routine and screenshot the error message. Try adding a new line, tick it and then post, then enter another. I'll delete all records from tblinventorydetails and try too. My idea is to progressively receive parts by ticking the appropriate box, then when all parts are receipted, i'll disable further editing. I'm open to any suggestions. Appreciate it.

 

[HillTJ]

June7, all works perfect till I add the dbFailonError bit. I tried to trap error 3022 as a way around it but I don't think this is correct. Upon entering a line, posting it, then entering a second line, the error fires. I tried deleting all records from tblInventorydetails, but it made no difference. Cheers

 

[The_Doc_Man]

Error 3022 is a "Key violation" - meaning you are trying to duplicate a value in a table that has a uniqueness constraint on it OR you are trying to insert a dependent value for which the corresponding independent value - as defined by a formal relationship - doesn't exist (dependency constraint, also called "Relational Integrity"). This often happens with JOIN queries being used not only to find things but to update things.

That "dbFailOnError" code is telling you that the query triggering the error is doing one of the two things I just named above.

I looked at the button-click event of frmPurchaseOrders:btnNew and there is no dbFailOnError in that code. But I figured it would generate a Form_Current event so I looked there. Again, didn't see a dbFailOnError. I'm not going to search through that entire DB. Please consider in the future when loading a large app that you need to give us a better road-map of how to get to the problem area. Give us discrete steps to perform so we can see what you are talking about. (No, please don't take this as an ANGRY response. Just frustrated because I can't help you like I would want to help.)

 

[bastanu]

Please see attached, changed the append query to leave out the lineids already "posted".
Cheers,

 

[June7]

Don't think I've ever bothered with dbFailonError. If field is set to not allow duplicates and using CurrentDb.Execute, duplicate entries will not be allowed. However, user will not be notified. Do they care to be?

 

[HillTJ]

The_Doc_Man, sorry but I understand where you're coming from. The 'offending' code resides under the button of 'frmPurchaseOrders' I added in dbfailonerror because I thought it was necessary. This seemed to cause my problems. With what Vlad has done, I no longer get the 3022 dbfailonerror generated, but there appears to be a problem in posting additional lines?

 

[The_Doc_Man]

OK, looked at wrong button code.

You've got a query there that makes me wonder about a value. If you put a breakpoint on the

qdf.Execute dbFailOnError

line in your BttnPost_Click routine, then try to post something, it will break BEFORE it tries to execute anything. Now open the Immediate window and issue this command:

Debug.Print forms!frmPurchaseOrders!POID

Following the chain of your queries, that is the only thing that doesn't come from a table.

 

[HillTJ]

The_Doc_Man, Bastanu has made a mod to the query & now I don't fire the dbFailOnError upon 'posting' which was my original issue. See his post with the modified database. So that's an improvement. Now, if I add a line, tick it and post it, it works but when I add another line that line doesn't post. You know, all this occurred when I started experimenting with the dbFailOnError. I thought it was the 'proper' thing to do, but (I think) it's caused dramas.

 

[theDBguy]

The_Doc_Man, Bastanu has made a mod to the query & now I don't fire the dbFailOnError upon 'posting' which was my original issue. See his post with the modified database. So that's an improvement. Now, if I add a line, tick it and post it, it works but when I add another line that line doesn't post. You know, all this occurred when I started experimenting with the dbFailOnError. I thought it was the 'proper' thing to do, but (I think) it's caused dramas.

Hi. I know you're not asking me; but if you were, in my humble opinion, using dbFailOnError IS the right thing to do.

 

[The_Doc_Man]

My viewpoint is that something is dramatically wrong. The error that is triggered when you enable dbFailOnError is TELLING you there is an error. The real goal would be to trace down and fix the error so that even WITH the dbFailOnError declaration, your code is quiet. Removing the option is sort of like saying - yeah, I know it hurts, but suck it up. You don't know what is happening with your db project if you turn off the thing that says you have a problem. Because NOW you have a quiet problem and will never know WHEN it worked vs. when it didn't.

So despite us not seeing specifically what is wrong, you need to figure it out. The error says you are either causing a duplicate value where one is not allowed or you are trying to insert a value without laying proper groundwork.

 

[The_Doc_Man]

OK, took another look. Your qryPO_LineItem selects ONE FIELD from tblPurchaseOrders (supplier ID)- which if you were ONLY doing a lookup, would be OK. But you use that query to write information as well. Since your SELECT includes an entry for tblPurchaseOrders, it is possible that you are trying to make an entry in your purchase order table when you make a line item entry. But you don't want that. You only want to make a line-item entry in one place. TECHNICALLY, this appears to be a normalization error since the raw data entry has everything it needs to look up the supplier ID in any reports. It is not only possible but typical that you would use different queries for forms vs. reports because you don't really care what fields are in the report. You weren't going to update them anyway. But in a form, you really want to narrow down the scope of the query.

Does this make sense? Look at it. You are going all around the horn trying to find line items based on supplier ID but perhaps a subquery would be just as effective in narrowing down the field. If you are not familiar with it, look up "sub-query" as a topic. It might be what you want. Your query is complex enough that it is hard to read so I'm having issues trying to determine what you are REALLY asking. (Could be old, bad eyes, too...)

 

[HillTJ]

The_Doc_Man, thanks. I'll have a careful look, but am out of my depth with what seemed so simple. Will do a bit more research. Out of comfort zone...you bet!

 

[HillTJ]

So, this morning I deleted all previous records from both the 'posting' table & the 'posted' table. Then I re-entered test data & posted it. All worked as planned (including the dbFailOnError bit which previously caused the 3022 error) ! I'll continue to test. Haven't seen the 'keyviolation' error since. Cheers to all whom assisted. The project continues.....

 

[Isaac]

Hi. I know you're not asking me; but if you were, in my humble opinion, using dbFailOnError IS the right thing to do.

I agree.

Sure, an argument could be made that leaving it off allows you to silently insert "just" those records which didn't violate a key or index, and maybe that's what you wanted to do, so it seems "perfect".

However, it seems like a bad habit. It will also cover up any OTHER types of errors that occur with just SOME records, like if you try to insert the word "spaghetti" to a Number column........and keep inserting the other records.
This means if I did this, my code would keep running every day - with me never realizing that some records weren't being inserted.

Overall, it's pretty reminiscent of On Error Resume Next.
IMHO, never use a sweeping generalization in order to catch just "one" scenario. It's dangerous. Instead, take the time you need in order to accomodate just the one specific scenario you want to allow, rather than shortcutting "I'll just allow 100% of all problems in order not to be bothered with knowing this one problem occurred"....scary stuff.

Imagine if the guy who programs bank systems for debit card transactions used "On Error Resume Next" .....
(But if he ever does, I need to know - I have some shopping to do!)

 

[HillTJ]

Isaac, in essence, your post pretty much summarised the reason why i persevered with a solution. I was not happy in not trapping potential errors properly, especially if the cause of the error may have indicated a more significant issue. Mission accomplished!.

If there's one thing i've learn't, it's that along with keeping peoples data comes a lot of responsibility. Bank databases etc 'blow me away'. I have one database application i wrote several years ago, and well before i really considered the 'implications' . It's used for 8 hrs per day every day and totally relied upon. I never really considered it finished. To date, no major issues. It still works great but i worry!!.

 

[The_Doc_Man]

It still works great but i worry!!.

Well OF COURSE you worry what people are doing to "your baby" because you are a proud papa. (Yes, some of us get that way about our code.)