table design or in query???

[Becky]

I am making a configurator. I have the main table as

tblConfigurator:ConfiguratorID, AuditID, SystemID,
ChamberAID, ChamberBID, GasPanelAID, GasPanelBID

The default value for all the IDs except ConfiguratorID is
0 and I stated that it isn't mandatory to have a field
value. I do have idexed set to yes but duplicate values is
OK.

The other six tables only have a primary key (above
mentioned) and then their fields.

I have a six page tabled form to save the data using a
query that uses * tblName to join all the tables together.

What happens is that it wants a value for each ID or it
will not save. Chamber B,C,D may not have any data.

What am I missing here?

Thanks,
Becky
..

 

[Cynthia]

If the ID's listed are used to link your tables to the
main table then they must have values.

For instance, it sounds as if your stating thar
the "primary key" in your Audit table is AuditID then in
order for it to have a RELATIONSHIP with the main table,
it must have the same value in the main table and the
Audit table.

 

[Steve Schapel]

Becky,

I'm not sure I really follow what you are trying to do here, but...
have you defined relationships between these tables in the
Relationships window, with Referential Integrity on? If so, I think
this will mean that you can't have a record in tblConfigurator without
corresponding records in all the related tables. Possible solutions
would be to remove the Referential Integrity requirement, or else put
a record with an ID of 0 in each of the one-side tables.

- Steve Schapel, Microsoft Access MVP

 

[Tim Ferguson]

The default value for all the IDs except ConfiguratorID is
0 and I stated that it isn't mandatory to have a field
value. I do have idexed set to yes but duplicate values is
OK.

This is one of the nasty, evil mistakes by the MS Access team, who
sometimes act as if they don't understand how RDBMSs work. Or else they
just like making work for MVPs.

In table design, you can simply rubout the DefaultValue, or enter Null if
you prefer. It is much safer to have an empty FK, than a zero value which
is the one that is almost guaranteed not to point to a valid record if the
target table has an Autonumber PK.

Failing that, you need to put something on the form that will force the
user to pick an existing, valid record for each FK before being allowed to
save the record. That seems to go against your statement that it is not
mandatory to have a field value, though. Leaving it set to 0 is going to
fail every time.

Hope that helps


Tim F