referential integrity causing problems on only 1 table???

[misscrf]

My db is an inventory of printers in use at our company. It is to tell us where they are, and what maintenance is done on them over time.

I have the following tables:


tblIP*
tblLocation*
tblMaintenance
tblMaintenanceType*
tblMake*
tblModel*
tblOffice*
tblPageCount
tblPrinter

The ones with stars are "type" tables. All possible IP's are in the tblIP, models in tblModel, office's in tblOffice etc.

Maintenance types are just like "fuser replacement", "toner replacement", "turned on computer" (IT joke)

anyway, On my main form. If I open the form and click the button I made to go to the previous record or next, I am fine.

If I choose an office, or start a record, I get a message that I have to have a record for IP chosen, that it can't leave this new record until it has an IP because of referential integrity.

If I choose an IP and nothing else, I can leave the new record. If I don't choose IP, but choose everything else, I cannot leave the record. AHHH!!!

The thing is, all my combos on the main form are (as explained above) FK's to lookup tables, and all set up the same: all with referential integrity, 1 - many, cascade updates and deletes.

This 1 table is the only one acting this way.

I am including a print screen of my form, as a visual sometimes helps.

If anyone can help me to fix this, I would really appreciate it. I have never had this problem before.

Thanks.

 

[godofhell]

Your integrety is set in such a way that everything requires an IP address, that is why it will not let you move on. When you select the IP it creats a blank record and lets you move on. You can write code so that a hidden IPID control is assigned a value when the record on the form changes that creates the blank record for you.

Private Sub Form_BeforeInsert(Cancel As Integer)
IPID.value = "Something"
End Sub

 

[Tim L]

Check the table that the form uses for its' recordSource, the IP field may have its' "Required" property set to Yes. If so, and if it is the case that you sometimes have equipment does not have an IP and need to have records that do not have IP addresses, then you will need to set Required=No.

There is a problem here though, in that one purpose assigned to the IP field may have been to uniquely identify the records and provide connections to other tables. This is a design problem and although it may have been valid before it appears that you need to store records without IPs, so the design will need to change.

If this is the case, you will need to create a new primary key field (autonumber). You will then need to change the links in all the related tables to the new key field (they should be Number, Long Integer). You should be able to make the transition easier with a few update queries. You will also need to change all affected relationships. Do this before creating any new records without IPs.

HTH

Tim

 

[Pat Hartman]

Access automatically sets the default to 0 for numeric fields even if they are foreign keys. Make sure you delete these defaults because they will cause a problem when the field is not required.

 

[misscrf]

Thank you all for your replies. I know we can figure out what is going on here. It just doesnt make any sense.


I have my database documented here:
Tables, relationships, forms and all.

I checked everything you all suggested.

It is not required, there is no 0 default ( and the field is text in its own table), and it is set up EXACTLY like the 4 other lookup fields to the main table. Their relationships are the same, the only thing I changed for testing was to not index it. And that didnt help.

Is there anything else that can be causing this?

One of the replies...

If this is the case, you will need to create a new primary key field (autonumber). You will then need to change the links in all the related tables to the new key field (they should be Number, Long Integer). You should be able to make the transition easier with a few update queries. You will also need to change all affected relationships. Do this before creating any new records without IPs.

Can you help me to understand what I need to do to do this? and why it will help, lol. I make a new pk (after I break the relationship.) change the links to the new key. What is are the update queries for? The IP value in the looked-up printer table? Im not sure how to do that. Also, there aren't many records in here,as I just created this and am still testing. If I have to start again with records, that will not be a big deal. Better to solve the problem!

Well, thank you all for your help. If anyone can help me to fix this, I would really appreciate it. I will try making a new pk for the IP table and see if I can get that to fix things.

Thanks!

 

[Tim L]

misscrf,

my suggestion should have helped if the condition that I asked you to checked for existed. However, according to your database properties that is not so, so the advice may not (or may still) help.

To understand more cleary what I was talking about you would need to be familiar with database relationships; do a search both in here and the Access help (and this is available on line), meantime I'll try a bit of an explantion...

Relationships help to control the database structure by linking records in different tables. For instance, if you were creating a database to record tenants details in a number of blocks of flats you might have three tables, tblBlocks, tblFlats, tblTenants. Each record in tblBlocks would have many records in the flats table associated with it and each flat record might have multiple tenants associated with it (over a period of time). These are both one-to-many relationships, the most common.

For your database I had to guess at some of the properties because you had not by then posted the properties, so I guessed that perhaps the IP field was being used in a relationship and if there was no data in the field it would cause problems. This does not appear to be the case and I can't think of anything else at the moment (nor, unfortunately, do I have the time until next week (working weekend)).

Here's hoping that someone has time to go through the properties listing and will spot the problem. (Incidentally, how did you produce the listing?)

Regards,

Tim

 

[misscrf]

Tim, thank you so much for your help. I ended up figuring it out.

For some stupid reason, I put a default value on the form control of the IP field. Once I took it out, it worked fine.

I'm such an idiot!

The html file for my db was made by an add in.
I can't seem to find where I got it from.

This is the info that comes up when I run it:
MDB doc 1.2 John Barnett

It is really great. I run it whenever I want to go over all my tables queries, make sure all my forms and reports are consistent.
Really saves time in getting a good handle on what Im working with.

Thank you for trying to help.