Results 1 to 12 of 12
  1. #1
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Append query Error


    To overcome lagging and halting of our system it works to use multiple append queries. For all documents we use we created a "Generate" form which generates one invoice at a time and then append it to an "Edit" table and sub table which stores any volume of documents with no calculations. The attached image tells a lot about the compilation of our tables. All "main tables" and "sub Tables" have to be cascaded if there is use to delete a record and its lines in the sub table. We currently have an error "Cant append because of Key violations". I've read on forums and followed what was said, no luck yet. We don't append to autonumber fields, so it can not duplicate. All other fields in tables are not set to "Required", or indexed. We solved it a few times, but are a bit stuck tight now.Click image for larger version. 

Name:	RelationShips.png 
Views:	20 
Size:	91.4 KB 
ID:	35254

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Where is query or code to analyze? Or provide db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Very frustrating not to manage upload of my DB. Will sent query and code.

  4. #4
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    Click image for larger version. 

Name:	01AppendQuery.png 
Views:	14 
Size:	79.0 KB 
ID:	35255Click image for larger version. 

Name:	02AppendQuery.png 
Views:	14 
Size:	102.3 KB 
ID:	35256Click image for larger version. 

Name:	03AppendQuery.png 
Views:	14 
Size:	94.3 KB 
ID:	35257

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Copy/paste SQL statements into post, not images. I see nothing wrong with SQL. Really need db to analyze this issue.

    Why duplicate data?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618

    Append Query Error

    It seems I got to upload it. Goto form f02RctEdit. Click on Add. Then add again. Enter any info, then click on save and yes. The error will appear.
    Attached Files Attached Files

  7. #7
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    We set up this DB with mainly 5 documents each with a main form and sub form. We had it at a point where they all appended. The changes since then mainly included enforcing referential integrity. I guess the problem has to be there?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    A form is for data entry to a single table. Which table is the main form for and which is subform for?

    There is seldom a need to include the related tables in RecordSource for form, report yes. If you do, probably should not be INNER JOIN. Suggest you simplify.

    Why is BatchType03 textbox on top of CmbEnt_ID18 combobox?

    Why are the relationships shown in post not in db?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    In South Africa 07H40 now, so good morning. You say a form is for data entry to a single table?. All our forms are connected to queries which feeds multiple tables each. If you look at our naming the "Main Tables" and their "Sub Tables" have the exact same name, except the "Sub Tables" names end on Sub. Our DB is over 100 MB after Zipped and with no transactions posted. I had to open a new Access file and copy enough objects to stay under 50 k to get it to you. Therefore the relationships was deleted which I wasn't aware of immediately. I will see if I can reconnect some joins as in the live system and send it again. Of course the fire should be where the smoke is, and in post I try to tell where I see smoke. Thanks.

  10. #10
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    BatchType03 textbox is on top of CmbEnt_ID18. You may see in the VBA on the form, those two fields depend on the selection in TrnTpe_ID03. One will be visible whilst the other not. You are a VIP and any small advice from you may be worth a lot to us. I am a qualified accountant with limited programming experience and my son (21) has two years college after school in programming. We are getting better every day and we enjoy Access. I was the owner of three businesses where 1000 people worked and have some obsession to create the perfect accounting system. I am telling the following so if I have something that you can advice to improve it may be worth a lot.
    - From the start we were taught that expressions slow the system and can halt it.
    - Our admin process on the current DB support a retail business which sells many products.
    - The chosen price strategy is to have a "Business Policy table" that stores a "Sales Markup on Cost" rate.
    - "Inventory table" is set up with a field where "SalesPriceAdjustment" is stored.
    - This kind of "horrible mess" is needed in more places. Clever thinking and a smart program like Access can do it.
    - Many things may cause a horrible mess like I would be happy if our system can handle history well.
    - If the user for some reason want to create an invoice 2 years ago and maybe there was a discount running for some time on the specific product, our history should be stored that the date causes the invoice to be correct.
    - Currently we use mainly 5 documents to process all transactions. Every document has a Generate form as you may see. There is a Combined transactions table to "Combine all transactions".
    -This Generate form gets appended to the "Edit Tables" with exactly the same info. The edit Tables does not allow additions since it has no calculations. No lagging or halting of our system. Generate tables stores only one transaction which is deleted after it is appended.
    -Is the thinking good when I say that on our "Edit Forms" we will eventually use VBA to do the calculations. Currently if the user wants to adjust a document already posted it is done manually.
    -When we started we were taught to use Union query and we did, but now the thinking is that a "Combined transactions table" is better so we did that.
    -Because expressions cause lagging it brought hassles, but in some instances expressions have to be used when joining can not be done.

    Thank you.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Don't bother setting Relationships and providing new db.

    So the form names imply the tables to be edited are t02RctEdit and t02RctEditSub. This means should be able to set the RecordSource directly to tables and the controls will still show bound to fields of those tables. This is not the case. My advice is still to simplify the form RecordSource. One form to one table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Perfac's Avatar
    Perfac is offline Expert
    Windows 7 64bit Access 2016
    Join Date
    May 2016
    Location
    Centurion Pretoria
    Posts
    618
    I tested the new Access file I sent to you and it is frustrating not to know why it works now. I am copying the objects over group by group and it looks like it works at the moment. I forced referential integrity, and didn't change anything. The only difference is our DB is split, and The new one not?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Append query error
    By lonely in forum Queries
    Replies: 7
    Last Post: 08-24-2015, 08:57 AM
  2. append query error
    By kallm in forum Programming
    Replies: 0
    Last Post: 02-26-2012, 09:35 AM
  3. Append query error trapping
    By tpcervelo in forum Programming
    Replies: 4
    Last Post: 12-22-2011, 10:57 AM
  4. Append Query Error
    By lupis in forum Queries
    Replies: 1
    Last Post: 06-18-2010, 02:10 AM
  5. append query error
    By shashigk in forum Queries
    Replies: 2
    Last Post: 09-22-2009, 07:17 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums