help! error msg, can't create new records

[Sadie Hewgill]

I'm not sure if this is the cause of my troubles, but I have my suspicions: I deleted relationships so I could change the type of my primary key field from autonumber, to number, and then back to autonumber again, so it would reset the autonumber count from one. Whatever the cause, it has messed up my forms so that when I make a new entry I get an error message: database engine cannot find a record in child table with key matching field in parent table. The relationship is one to one, and the field it is referring to is the one that I changed with auto numbers. I don't know how to fix this beyond starting the forms from scratch and I really don't want to do that! I don't care about my data yet though, it is just for testing.
Please help!

 

[boblarson]

1. You can't change a field from number to autonumber if it has ever had any data in it.

2. You should not care what the autonumber is. If you do then you are using it for the wrong reasons.

You might not have the correct table setup now. Can you post a copy of your database (with bogus data) so we can take a look and see what might be the problem?

 

[ChipperT]

Most likely when you deleted the relationships and messed around with your autonumber column you invalidated the data in all of your foreign keys in other tables, so that the relationships you now have (if any) are pure garbage. If that is the case, then you may have to figure out how to dump the data in the affected tables, recreate those tables from scratch and reload the data.

This is why an autonumber column should ONLY be used for primary keys and never, ever messed with.

 

[Sadie Hewgill]

Here's my database. All the data in it is bogus right now, so that doesn't matter. I'm really starting to think I should have never used autonumbers, but they seemed to work so nicely. They automatically appeared on the form when I'm entering new data, giving me the next part number, and they would be completely unique and never duplicated. I looked into using Dmax, but I couldn't get it to do everything I wanted it to and thought I could get away with autonumbers. Here's what I had before:

Private Sub Part_Number_Enter()
Dim LMax As Integer
If IsNull(Me.Part_Number) Then

LMax = DMax("[Part Number]", "[Part Numbers]") + 1 Me![Part Number] = LMax

End If
' Update Part Numbers with new PartNumber CurrentDb.Execute " UPDATE [Part Numbers] SET [Part Number]=" & LMax, dbFailOnError

End Sub

Thank you so much!

 

[Sadie Hewgill]

sorry, i should've given you some direction to the main problem in my db. If you open the form called Fasteners_mm Entry Form and try to enter a new record you'll see what the problem is. I am now working with numbers and Dmax for the field "Part Numbers" instead of autonumbers, but still the same problem is happening.

 

[boblarson]

You are working with Access 2010, right? (I can't open the file using 2007 so I'm assuming 2010 with some 2010 features used.)

I will have to wait until I get home to look at it so if anyone else can open it up and look that would be good.

 

[The_Doc_Man]

I can't open the DB, but I remember another thread with you as the originator. Sadie, if you are trying to force autonumbers into your system then they cannot have any meaning. Nor can you have any expectation of how they will run.

To save what data you can, compact and repair. Make a backup. THEN start mucking about with the records. Further, if you had turned on enforcement of relational integrity, you would have been prevented from deleting records in the wrong order. You can delete records that have relationships. You just have to delete the child records first. That you were able to delete the parent records tells me you have hosed up your database. When you come on the forum and ask for advice, you are certainly free to ignore it - but if you get several people that tell you the same thing and you ignore them all, you do so at your peril. And you just experienced some of that peril.

 

[Sadie Hewgill]

I have seen the light and stopped using autonumbers. I did initially take the advice to get rid of the autonumbers, and switched to numbers, but then I switched back because it seemed they worked better. So now that swap has messed up my database, and I'm quite ready to get rid of the autonumbers, infact I am doing this while saving a backup of what I had. I should've know to take the advice of more experienced people, but alas, I'm learning from my mistakes instead.
I really don't know how to fix it now though. I have been using relational integrity to the highest level the whole way through except for the moments when I deleted relationships in order to change to auto numbers, but then I recreated those relationships. That's the only reason I can think of why the parent child relationships aren't functioning like they used to. So obviously I have some serious cleaning up to do... where should I start? Oh, and how do I compress and repair my db?

And I am having trouble uploading my database. The attachment manager says it is 4 MB, but my computer says it's more like 4000KB. So the zip folder is the only way I can do it. And I'm using 2007, so it must be having trouble coming to you. I tried compressing it a different way, so maybe it will open for you??? I know there's probably not much you can do without seeing it. I'm starting to think the best approach is to start over and transfer some of my forms and tables if possible. The data doesn't matter, it's just bogus data.

 

[boblarson]

Just an FYI for you. Autonumbers are GOOD for primary keys. In fact after looking at your database, I see nothing to keep you from using autonumbers if you ONLY use them as your primary keys for each table. You do not need to use PartNumber as the primary key. In fact, it is better that you do NOT. Part numbers can end up with one object being different but having the same part number. Or the scheme may change at some point and then you are up a creek without a paddle.

Users should not be concerned, nor should they even see, the autonumbers you use for the primary keys. You can display the part number just fine for them and you can use validation to ensure that the part numbers flow like you want and do not duplicate. But I believe you are asking for trouble to try to use PartNumber as a primary key and then manually maintain it as well.

I think you misunderstand what the primary key is. It is simply a mechanism for maintaining the relationship between related items and maintaining referential integrity. That is something that the user not even be aware of. It is something that I feel, and many others do (although not everyone), that it is best to let the system handle the system stuff and you concentrate on the rest.

That is my OPINION.

 

[Sadie Hewgill]

I will keep the fastener_mm ID and other ID numbers as autonumbers since they have no meaning. Should these be my primary keys or should part number be my primary key? I don't ever want there to be a duplicate of a part number regardless of the type. They should be completely unique, but the part numbers do have meaning so in the new version of the db attached to my previous post, I am using numbers for part numbers instead of autonumber. Do you have any idea how to fix my data entry problem? I have a feeling it stems from deleted and restored relationships, but I can't figure out how to satisfy it.

 

[Rabbie]

And I am having trouble uploading my database. The attachment manager says it is 4 MB, but my computer says it's more like 4000KB.

4MB is 4000KB.

 

[Sadie Hewgill]

Oh, I should've known that.

 

[ChipperT]

I will keep the fastener_mm ID and other ID numbers as autonumbers since they have no meaning. Should these be my primary keys or should part number be my primary key? I don't ever want there to be a duplicate of a part number regardless of the type. They should be completely unique, but the part numbers do have meaning so in the new version of the db attached to my previous post, I am using numbers for part numbers instead of autonumber. Do you have any idea how to fix my data entry problem? I have a feeling it stems from deleted and restored relationships, but I can't figure out how to satisfy it.

There is no reason why a primary key can't have "meaning" but a primary key MUST:
1. Always be unique
2. Not be changed on a record... EVER
3. May not be deleted from the table unless all child records that refer to that key (via Foreign Key to Primary Key) are deleted first.

A primary key has two major functions usually. First, it uniquely identifies that record in the database and second, allows a way to easily index a table for faster locating of records.

I think the only way you are going to straighten out your problem in your data entry will be to:

1. Establish a new primary key in the tables.
2. Go through each table and identify all records that have a foreign key to another table's primary key. Delete all data in those foreign key columns.
3. Re-establish the relationship from the foreign key column back to the primary key column.
4. Update the foreign key columns so that each of the foreign keys relate back to the real primary key.

A LOT of work and hopefully that will fix it (assuming that is your real problem - that when you deleted your primary key and broke your relationships, you screwed it all up that way).