Append query, only appends 5 of 59 records.

[Perfac]

First open the form “f02SalesInvoices”.
Opening the “Append Query” y1CostOfGoodsSold in Design View.
It should indicate that it want to append 59 records to table yCostOfGoodsSold.
Double Clicking on the Append query, it appends all 59 records, if the table was clear of records.
On the f02SalesInvoices form under the “Close event” a Module is called on, when the invoice is closed.
But then only 5 records are appended.


The solution may be obvious, but after some time on it, I didnt find the solution yet.

[Welshgasman]

Why not just show the code within code tags, instead of forcing us to download, unzip and open a db each time?
That should be only be needed when all else fails.

Most times just seeing the code is suffcient to correct the issue.

DB too late for me anyway.

[Micron]

You need to step through your code and watch what it is doing rather than wasting your time taking stabs at a problem. The cause is obvious when you do that. The reason, not so much to me since I don't really know what is supposed to happen. Something calls this function: Function QuantityAllocated and it runs 5x so it seems to create 5 records. I just haven't got far enough into it yet to know what calls it.

EDIT - seems very odd that upon form closing you delete table records, populate table with 1's and 0's then delete that, then run query (which appends no records).

[Perfac]

Thank you
I try to comprehend why the Append query displays the 59 records, and then appends only 5. As said, when I double click on the Append query it Appends the 59 records as I would like it to do when the Sales Invoice is closed.

I respect everybody's precious time, and appreciate any help or advice. Don't spent time on the other detail, just why it doesn't append all 59 records on closing the Sales Invoice form.

[Perfac]

Thank you Mr Gasman. One person advice me to always attach a DB and others thinks different. I think it should not take you 15 seconds to open the little DB and get to the point. Smile.

[Welshgasman]

Thank you Mr Gasman. One person advice me to always attach a DB and others thinks different. I think it should not take you 15 seconds to open the little DB and get to the point. Smile.

No, as I menioned, version is to late for me to see the query. So I just wasted a little more than 15 seconds trying to see what the issue might be. I will know better next time, if there is a next time.

[Micron]

I try to comprehend why the Append query displays the 59 records, and then appends only 5.

Me too but after an hour of going down the rabbit hole I finally figured out what is calling Function QuantityAllocated(Pop As Long, PRun As Long, Sop As Long, SRun As Long) As Long
The call is buried umpteen queries upstream in Q08zBatchAllocation which actually causes your function to run about 88 times to produce 5 records. Sorry to have to tell you that your db design is too convoluted to follow so hopefully you can fix what you've done.

[Perfac]

Thank you for your time.
Some 4 years ago, a good man on this forum sent me a well set up example how to get the FIFO reports on Inventory into my app. I pulled it over to my system exactly as he had it, and it worked well for these years. Of course I may have 100 backup copies of my app. The append query is set up at exactly the same way in my newest version. I do not know yet what I have changed to cause the problem. You are right about the function, but I still don't find sense why the 59 records append if I double click on the Append Query, but not when the for activates it.

[Micron]

I think the answer is because you used the close event - this means the form has unloaded. When you run the query from the nav pane and the form is open, it is loaded thus 59 records. Something the query needs isn't available in the close event and it's affecting the result. Move AppendCogs.yCostOfGoodsSold call to the top of the form unload event (which you'll have to create). I think this would be a good resource for you:
https://support.microsoft.com/en-us/...7-ce86553682f9