Results 1 to 12 of 12
  1. #1
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    51

    Error 3201

    Hi

    Does anyone know or have experience with error 3201?



    I have a pop up form to add new records, where the ParentCIN is the PK. In the ParentCIN field, BeforeUpdate does the DCount/lookup to see if the PK is already in use. I first created a record with ParentCIN 1001, and then I tried to add another record with unique value of 1001-2. It gave me the Error 3201. Is Access interpreting 1001 and 1001-2 as same value??

    greatly appreciated!
    Click image for larger version. 

Name:	Error 3201.PNG 
Views:	34 
Size:	33.6 KB 
ID:	34430

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    message is perfectly clear - you have set referential integrity between your customer table and whatever table is used by your form. Having done this, you are now trying to add a record which does not have a related table in your customer table. Referential integrity does not allow you to add child records without a matching parent (as you have here) nor allow you to delete a parent record which has child records (hence the ability to allow cascade delete)

  3. #3
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    51
    Quote Originally Posted by Ajax View Post
    message is perfectly clear - you have set referential integrity between your customer table and whatever table is used by your form. Having done this, you are now trying to add a record which does not have a related table in your customer table. Referential integrity does not allow you to add child records without a matching parent (as you have here) nor allow you to delete a parent record which has child records (hence the ability to allow cascade delete)
    But it doesn't make sense though....I added 1001 just fine and it does not have any child records. This Create form is supposed to create "ParentCin" and insert some standard child records. It works when I create new ParentCIN without hyphen.

    Is there something wrong with my codes?

    Private Sub CmdAddStandardFees_Click()
    On Error GoTo Err_CmdAddStandardFees_Click


    Dim sSQL As String
    If Me.Dirty Then Me.Dirty = False


    sSQL = "INSERT INTO Tbl_CustomerFeeSchedule (FeeCodeID, ParentCIN ) " _
    & "SELECT tbl_FeeTable.FeeCodeID," & [Forms]![frm_CreateParentCIN]![ParentCIN] & " AS ParentCIN " _
    & "FROM tbl_FeeTable;"
    Debug.Print sSQL
    CurrentDb.Execute sSQL, dbFailOnError
    DoCmd.Close acForm, Me.Name
    Forms!frm_ListOfParents.Requery
    Exit_CmdAddStandardFees_Click:
    Exit Sub

    Err_CmdAddStandardFees_Click:
    MsgBox "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Resume Exit_CmdAddStandardFees_Click
    End Sub

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    At the moment, cannot advise - need to see your relationship and table structure and the recordsource to the form. Also need to clarify names - your field names don't map (to me) to what is on the form,

    Best guess at the moment is that you may have entered a new parent code but have not yet saved it to the table

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Some tips/suggestions when posting to make things better for all who read:
    - use code tags for anything more than 2 or 3 lines (also will prevent forum from interjecting spaces at the 50 character point, even for one line)
    - state what line triggers the error. In your case, it could be at least 1 of 3 or more

    Even when this current problem is solved, I expect a new error triggered by your sql statement as ParentCIN appears to be text data and the form field reference isn't being wrapped in single quotes. Agree that more clarity or a db copy is required.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    @JennyL

    You are talking about HOW you have done something, but we don't understand the what (something) that you are trying to do.
    Perhaps you could describe to readers in plain English what you are trying to accomplish. And post a picture of your tables and relationships so readers can see your table/fields/design.

    The error message is clear, as others have indicated, but we don't know your tables, relationships, code etc.

  7. #7
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    51
    sorry all if I wasn't clear.

    I have three tables:

    1) Tbl_Customer, it has customer id, name, and a note field. Customer ID has to be a text field, limited to 10 digits because it potentially could have duplicate values, therefore I will have to train users to assign the number and add a -1 or A after the regular customer id value. For example 1001, 1001-1, or 1001-A.

    2) Tbl_FeeTable, it has a set of standard fees and their fee codes (about 386 of fees). This is the main fee table where any time I need to make a change to the fee code or fee description or standard fee amounts, this is where I will make the changes and it should automatically update the records to all parentcin with the fee record. Every new ParentCIN (Customer ID) I create should automatically gets all 386 fees added under that ParentCIN so that users don't have to manually add 386 fees manually. These are standard set of fees that everyone should get.

    3) Tbl_CustomerFeeSchedule, this is where Sales people will enter any discount amount for the fee and notes. Sales people won't be able to change any of the fields from the Tbl_FeeTable. I will lock all of the fields except Discount Amount (DiscAmt) and Notes fields.

    I created a data-entry form to add the parentCIN and also append all of the 386 standard fee records from the FeeTable. When user enter the ParentCIN field, the beforeupdate event will do a dcount to see if this number is already in use, if yes undo, if no then proceed to append the 386 standard fee codes from the fee table and add it to the Tbl_CustomerFeeSchedule table along with the ParentCIN in the ParentCIN field.

    The issue I am having is that it works when I am creating only numbers such as 1001, 1002, 1003. As soon as I start using the 1001-1, 1001A, then I get the error 3201. Access would create the 1001-1 ParentCIN record, but not append any child record from the Fee Table.
    Click image for larger version. 

Name:	Error 3201.PNG 
Views:	23 
Size:	9.3 KB 
ID:	34438

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Customer ID has to be a text field, limited to 10 digits because it potentially could have duplicate values
    That makes no sense. The picture doesn't show a "Customer ID" field, but after further reading I see that you've explained the discrepancy. However, that id field cannot be duplicated as it's a primary key. Did you post the wrong picture?

    I venture to say there is something wrong with this setup, or the logic behind it, as it would be very unusual to duplicate such id's by working around a pk constraint (by adding suffixes). A table of customers should contain unique ids for each customer. I presume you have no autonumber index for this, so if 2 distinct customers can be called Smith Hardware, or if it's due to multiple locations, then you have a basic design problem that needs to be corrected. Doing so might even negate the need for your attempt to duplicate the fee data.

    Will have to study the rest of your post in the meantime, but that's my take - you have a design problem.

    EDIT: it should automatically update the records to all parentcin with the fee record
    I may be wrong here, but if everyone is charged the same fees, then I see no reason to link this table to customers. The fact that you cannot put that many fields into one table (and it isn't a normalized approach anyway) would need a table of 386 records per customer in order to maintain normalization. Since all are subjected to the same fees, I don't see a need to related them to anyone. What you would need is a table of customer discount rates (CustDiscRate) if it's static, or a way to handle future exceptions where a customer is exempt from all fees. So for the current fee setup, only if certain customers don't pay all fees would I see a need for such a relationship.

    Maybe someone who is more sales oriented than I will disagree. My background is maintenance, QS and environmental.

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Very strange problem- Access is evaluating the expression 1001-2 as one-thousand-one minus 2 and using that as the key which is 999 and does not exist. Thus your error.
    So modify the code to treat ParentCIN as a string:
    & "SELECT tbl_FeeTable.FeeCodeID,'" & [Forms]![frm_CreateParentCIN]![ParentCIN] & "'" & " AS ParentCIN " _

    This works in the example DB (with ParentCIN modified as string) that I provided in a previous thread. Your current DB is set up differently so I don't know how or if the popup's ParentCIN is bound to the tables.

    Also, in tbl_CustomerFeeSchedule, you've added the foreign key pointing to tbl_FeeTable. That will give you the same error 3201 as there's no provision to add that key to tbl_CustomerFeeSchedule that I can see.
    Last edited by davegri; 06-14-2018 at 09:30 PM. Reason: observations

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Access is evaluating the expression 1001-2 as one-thousand-one minus 2
    have seen this before, but it was an issue with field names with minus signs.
    But I'm curious; how could you know this is the issue when no db was posted and the OP said this field was text data type (post 7)?
    Your suggestion supports the point I raised in post 5 - which basically went ignored.
    Your current DB is set up differently
    Now I'm just plain confused. What db? I see no db posted in any part of this thread, nor a link to a related thread that contains said db. You can't tell what the data types are by looking at the relationship pictures either. Kinda looks like OP sent you a db in a pm.

  11. #11
    JennyL is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jan 2017
    Posts
    51
    Thank you all so so much!!!

    I took Micron's advice on the design problem and re-did the PK fields and relationship. I created new PK fields and set them to auto-number, with old PK fields as index (no duplicate) rule and everything works fine now!

    I guess my big problem was that I was stuck on my old process and couldn't think outside of the box with the table design. Feeling much better about the data integrity now with the new design.

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Micron, JennyL did PM me with some specific questions. I developed a simple DB with form/subform setup and my ParentCIN was numeric. Didn't know about the non-numeric characters at that point. I PMed the INSERT code to her. After that Jen came back to the forum, no more PMs. I knew her DB was not like mine because of the PK data type change.

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

Similar Threads

  1. Replies: 5
    Last Post: 04-02-2017, 04:55 PM
  2. Error Handeling for a 3201 and 3022 Error
    By starlancer805 in forum Access
    Replies: 3
    Last Post: 03-11-2015, 12:46 PM
  3. Replies: 3
    Last Post: 01-23-2014, 07:49 AM
  4. Error 3201 on Form / Subform
    By jerem in forum Programming
    Replies: 2
    Last Post: 12-01-2012, 12:34 AM
  5. Replies: 0
    Last Post: 07-16-2012, 05:42 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