Full Version: Problem with Subform
UtterAccess Forums > Microsoft® Access > Access Forms
ghodder
Hi... I am trying to build a data entry form with a subform and I keep getting the error:
"The changes you are requesting would create duplicate values in the index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index or redefine the index to permit duplicate entries and try again."
The main form has several fields for claim information and its keys are... ***, and Claim#. The sub form has ***, and Claim number and then a field call Reason identifed.
I am trying to allow the user to select multiple reasons for flagging a claim, without duplicating records in the main table, or adding multiple fields such as reason 1, reason 2..... etc.
I have tried building table relationships, and have tried deleting relationships. It works the same in both instances. I am a novice at working in MsAccess (if you cannot tell)... so any help you can give me would be great.
Thanks,
Geo
ghodder
FYI... I built a very simple model of this to test the problem... and as long as the fields I am linking to in the main table are not primary keys... this works fine... i am getting the error due to the key definition.
quest4
Welcome to UA. Let's start at the botton and try working our way up. First in the relationships, is the referential integerty checked? How about the cascading Updates and deletes? Use the Tools Pull down mwnu select relationships, right mouse the link select edit, make sure all three checkboxes are checked. I will assume, which is always dangerous, you have a one-to-many relationship. Next open the main form and check the child / master links and make sure that they are correct. I noticed the use Claim#, I hope you aren't because # is a special character and can cause trouble. hth.
ghodder
Thanks for the welcome... and your response
k... I checked all the checkboxes and the relationship lines now give me a symbol that looks like an infinity sign. However I still have them set as outer joins, because the new table does not have any data in it, and it will not be required to have an entry for every entry in the other table.
Oprobably won't be able to remove the "#" from the Claim# field... this is an existing DB and that field is used in countless forms, queries, reports etc...
The referential integrity change, and insuring the child links are correct... did not resolve the issue. I still get the same error.
The weird part is that on the main form when I type in a value for ***... it populates the subform value for *** (As it should)... but when I type in the claim# field, it does not populate the subform claim field.... from that point forward any attempts to click on the subform gives me the error from the intiail post.
ghodder
One more thing I noticed... if I enter the claim# first, then the *** number.... the subform populates correctly and pulls any "reasons" I had in the sub-table.
owever... I still get the error if I click on the sub-form.
Also... I changed my relationship link to a regular join... and it functions the same.
quest4
Good morning, the infinity sign is an infinity sign. It represents many, there are three basic relationships you can work with, one-to-one, one-to-many and many-to-many, which I hope you never have to work with. The infinity sign is the many side of the relationship. Next you should make it a rule for yourself that in the future you don'r use any specialcharacters or blanks in your field names or any other data objects. Can I now assume that you have procedures that populate the subform? If so could you post the event? hth.
ghodder
No... I did not write any procedures for the form. Just the child link and master link fields.
quest4
No queries either, I take it. Do both work the same? Just trying to get a grip on what I am dealing with. Would it be possible to post a strip down version of the dbase? That would let me play with it then and I might spot the bug.
ghodder
Sure... see if this helps
quest4
OK, I have looked at this and I am trying to understand this. One table have two PKeys, not good. So what controls the dbase? ClainNo? ***? Why is the *** txtbox not a combo? What is the purpose of the subform, is it just to tie Reason ID to the current claim? Asuggestion in the future, all table should have a Pkey, even if it is only autonumber, no special characters, like /?&*#$- I think the is most of the list, and last but not least no blanks or spaces in any name. So before I go onward, please answer the first question.
ghodder
Ok.. well first off the db was designed by an inhouse guy that does not have a systems background. I do, but I have to work with what they have... so I apologize for the crazyness of the screen.
The screen itself is used by a department to document and track high dollar claims from our mainframe system. The claim is added to this database for any combination of 6 reasons which is what I am trying to add.
We are a healthcare insurance provider that offers 18 different healthplans, each healthplan has a unigue *** number, and within each *** number claim numbers are unique. We cannot key solely on *** because it is not specific enough, and we cannot key solely on claim number because they are only unique within an ***. Make sense?
One option I can do to fix this (which is an option I hate) is to add 6 check boxes instead of a seperate table of reasons. This option is not nearly as flexible and will be a pain to maintain... (as i am sure you are aware)
Also... I didn't design this, but I am assuming he is keying on both *** and claim to ensure the same claim is not added to the DB more than once.
quest4
I understand, been there, won the tee-shirt and worn the hat. The beauty of the PKey is the it is unique, no dups. So in thinking about this I really think that the ClaimNo should be the PKey and the data object which controls everything. If this is just pulling in old, existing data, then make the ClaimNo into a txtbox, else if this is a new ClaimNo then automatically assign it. Just a few thoguhts for you to punder,but the decision is totally yours, but think about it because I think we are going to have to get rid of one of those two PKeys in that table. Let me know and we shall go from there.
ghodder
hmm.. unfortunatly I don't think claimno by itself will work as a key... I'd have to have no keys and allow dups, or need both to keep the data unique. (or write modules to check for existance of a claim number before/after updates)
The data for this table is populated by an extract from our mainframe, no new claim numbers are generated within access.
I guess I may have to go with the check box solution.. yuck! : )
thanks for your help... good to know a clevelander can help a pittsburgher the week before the browns-steelers play! ; )
quest4
In the table, when a data item is a PKey, look at the bottom of the table, No Dups is automatically turned on, that is why I chose it. As long as it is the PKey, ClaimNo, there is no way you can dup it, it is unique, that is why it is called the Primary Key. Now, you could use a DLookUp statement to check and see if that ClaimNo already exists. I would you under stand what I am suggesting. As for the chkboxes, I heard there is a way to run it through a pivot table to alter it to what you need, you might want to look into that line of thought. Remember it just a game, entertainers on a stage, though over paid, but still entertainers. LOL, hth.
ghodder
WAY overpaid entertainers! : )
understand what you are saying about the pkey not allowing dups... but that is not my issue. and is not a characteristic of my data... My problem is that there could be a claim 123 for *** 1, and one for *** 2, 3 etc... and they would each be a seperate and distinct claim, needing its own record in this table Now if I concatenated the two together into one field.. that would give me a unique single field identifier....
I think I will just go the check box route... thanks for your help.
quest4
I suspect that you are the victim of poor dbase design. I inherited that once myself. I got lucky the powers to be want it to do way more than it was designed to do, so they told me to start from scratch, and at the beginning of the month, I get the pleasure of killing that rottenly designed thing and replace it with my dbase. Sorry I could n't be of more help. LOL. hth.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.