referential integrity only stuck on 1 relationship? 1

[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.

The thing is, all my combos on the main form are 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.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Trevil]

Hi,

Can you add a new printer from this form? If so, I assume you either have a form recordsource specified, or manage additions via code. If you have a recordsource specified, then you click on 'Office' and you get your error when trying to move to another record, I think the problem is one of two things.

a. Somehow your database has messed up the content of the IP field for that record , or more likely,
b. The combo box for IP has more than one column and the 'bound' column is not what it should be.

Has this problem always existed, or did it start after some change was made to the form?


35+ years of 'progress' -- can't we all just go wire boards again?

 

[misscrf]

1) I can add a new printer, if I complete the IP field. anything else can be left blank, and I can move to other fields, the record stays, if I dont fill out IP, I cant add the printer.

2) The form's record source is tblprinter. It has a lookup field to the ones above with a star. As the form shows - the 5 combos. 4 of them work as should, only the IP doesn't. They are all set up the same.

Here is a link to my database, documented with tables, relationships, forms, etc.

Thank you for helping me to troubleshoot this. It is so wierd!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Trevil]

I assume that when this form is opened, there are already a bunch of records and you can navigate around using the next and prior buttons. I assume your combo combo boxes are bound to the recordset (as I didn't see any code), then as you navigate, the values in those boxes change.

So when you say you can add a new printer, I assume all fields are then set to empty. Since your referential integrity links the printer table to all of the others, but the fields in the printer table are not required, you can save a printer without specifying anything (except for the IP field).

My earlier thought was the 'BoundColumn' property on the IP combo box had the incorrect column, but that should ALWAYS fail. Since this seems to be a small database, have you tried opening a blank database and importing all objects into it, then testing again?

35+ years of 'progress' -- can't we all just go wire boards again?

 

[misscrf]

you are right about ur 2 paragraphs there.

and, oh....

I figured it out!

For some stupid reason, I put a default value on the form control for the IP. As soon as I emptied the default value out, it works fine.

I'm such an idiot!

Thank you so much for your help. It made me look around and check everywhere.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[Trevil]

Ahhh, glad you figured it out. And, don't be to hard on yourself -- I've met lots of people that have FELT like an idiot, but only two who really were!

35+ years of 'progress' -- can't we all just go wire boards again?

 

[misscrf]

your so kind

misscrf

It is never too late to become what you could have been ~ George Eliot