Extraneous Database Entries

[silva]

I am having an odd difficulty with a database. When you use a particular form
to create a new record, it throws up an error message saying that a record
needs to be created in such and such table first. Well, in the field I'm
typing in, it'll create the record in that table (we'll call it Table #1). I
found that it's creating records in a related table (Table #2) before any
data is being entered into the subform for that table. After reviewing the
tables, I found that for the one valid test entry I made in the subform, it
created four empty records attached to the main table. Well, empty except for
the autonumber field and the linked field. For example:

Table #2
Field 1 Field 2 Field 3 Field 4 Field 5
name 00001 empty empty empty
name 00002 empty empty empty
name 00003 data data data
name 00004 empty empty empty
name 00005 empty empty empty

Is there anything that could be creating these empty entries as soon as I
enter a character to create a new record in Table #1? I checked the event
scripts for the fields I start in, and they're all AfterUpdate fields, so
nothing should happen until after I move to a new field.

 

[Jerry Whittle]

Look in the Relationships window. Hopefully these two tables are joined there
with referiential integrity enabled. Which is the parent table and which is
the child (related) table?

 

[Ken Sheridan]

It sounds like the referencing table (Table #2) is referencing a non-
key column of the referenced table (Table #1). Table #2 should
include a foreign key column which references the primary key (or a
candidate key at least) column of Table #1.

If the referenced column in Table #1 is not indexed uniquely, either
by virtue of being its primary key or otherwise, then it can contain
multiple rows with the same value in the referenced column.
Consequently, when you insert a row in the referencing table via the
subform it will reference all rows with matching values in the
referenced column in the referenced table. This would explain the
insertion of multiple rows into the referencing table.

In effect this would constitute a many-to-many relationship type, but
such a relationship type is not modelled by directly referencing one
table from the other. Its modelled by means of a third table with two
foreign key columns, each referencing the primary key of one of the
other two tables, e.g. a many-to-many relationship between Orders and
Products would be modelled by an OrderDetails table with foreign keys
OrderID and ProductID (and non-key columns such as Quantity). OrderID
and ProductID in combination form the composite primary key of the
table. A subform in the Orders table would be based on the
OrderDetails table (see Northwind for an example).

So if you really do have a many-to-many relationship type you'll need
to create an extra table to model this in the above way. If not, and
the relationship type is one-to-many you need to examine the columns
on which relationship between the tables is currently based.

Ken Sheridan
Stafford, England

 

[silva]

I should have mentioned in my "sample" table, that in all cases, "name" is an
identical value for each record. Don't know if that makes a difference.

Look in the Relationships window. Hopefully these two tables are joined there
with referiential integrity enabled. Which is the parent table and which is
the child (related) table?

Jerry--
Yes, referential integrity is enforced. I make sure of that with all
databases I create. Table #1 is the parent, Table #2 is the child.

It sounds like the referencing table (Table #2) is referencing a non-
key column of the referenced table (Table #1). Table #2 should
include a foreign key column which references the primary key (or a
candidate key at least) column of Table #1.

If the referenced column in Table #1 is not indexed uniquely, either
by virtue of being its primary key or otherwise, then it can contain
multiple rows with the same value in the referenced column.
Consequently, when you insert a row in the referencing table via the
subform it will reference all rows with matching values in the
referenced column in the referenced table. This would explain the
insertion of multiple rows into the referencing table.

In effect this would constitute a many-to-many relationship type, but
such a relationship type is not modelled by directly referencing one
table from the other. Its modelled by means of a third table with two
foreign key columns, each referencing the primary key of one of the
other two tables, e.g. a many-to-many relationship between Orders and
Products would be modelled by an OrderDetails table with foreign keys
OrderID and ProductID (and non-key columns such as Quantity). OrderID
and ProductID in combination form the composite primary key of the
table. A subform in the Orders table would be based on the
OrderDetails table (see Northwind for an example).

So if you really do have a many-to-many relationship type you'll need
to create an extra table to model this in the above way. If not, and
the relationship type is one-to-many you need to examine the columns
on which relationship between the tables is currently based.

Ken Sheridan
Stafford, England

Ken--
I double checked, it is a one-to-many relationship, not a many-to-many (I
make it a point to avoid a many-to-many relationship). The child table, #2,
is linked to the primary key of #1 (which is an autonumber). I am not using a
compound key for either table. There is a third table, #3, and it is linked
to the primary key of #2, but there are no problems at all with this table.
Only Table #2 has this issue. As it is, I have the form developed in such a
way that unless a specific checkbox is set to TRUE, data can't be entered
into Table #3.

 

[Ken Sheridan]

What are the LinkMasterFields and LinkChildFields properties of the
subform control? These should mirror the relationship, i.e. be the
primary/foreign key columns of the two tables, so the former would be
the name of the autonumber column of table #1 and the latter the
corresponding foreign key column in table #2 which references it.

If Field1 is not the foreign key column in table #2, but there are non-
key Field1 columns in both tables and these are used to link the
parent ands subform then, while the relationship between the tables is
one-to-many the 'relationship' between the parent form and subform
would in effect be many-to-many. This would explain the behaviour.
In fact, if that's the case, the Field1 column should only be in Table
#1 and not in Table #2 at all. If you want it to show repeatedly in
the subform you would base the subform on a query which joins the two
tables on the key columns and return the Field1 column from Table #1
in the query.

It would make life easier for us if you cited the real table and
column names here, as that way we can get a better feel for what's
actually being modelled and will be more likely to spot the cause of
the problem. As you see, I've had to do a lot of second guessing
based on what scenarios would explain the errant behaviour.

Ken Sheridan
Stafford, England

 

[silva]

Sorry, didn't think of that. This is what it looks like:
Guest_Name
Guest_ID_Number (autonumber & primary key)
Address_1
Address_2
City
State
ZIP
Phone
E-mail
Guest_ID (linked foreign key)
Event_Number (autonumber & primary key)
Contact_Type
Visit_Date
Location
And there are quite a few more fields based on the contact type, but trying
to tie them all in with seperate tables seemed excessively daunting. Listing
them all would take some time.

When looking at the properties, Guest_ID_Number is property listed as
LinkMasterField and Guest_ID is properly listed as LinkChildField.

Part of the problem is that there are multiple subforms attached to the main
form (in tabbed pages) for each type of guest contact we receive. But there
shouldn't be anything being created in the child table until data is entered
into a subform, but it's creating entries when data is being entered into the
parent form for a new record. What confuses me is that it didn't do this in
the beginning but just in the last month or so it started doing it and
throwing out an error.

 

[ken]

I think that your problem lies with the underlying logical model, i.e.
how the real world entity type and the relationships between them are
modelled by tables and relationships. Each event can be attended by
more than one guest presumably (it would be a lousy part otherwise!).
Conversely each guest might attend one or more events; note the use of
the word 'might'; they don't necessarily do so but its inherent in the
model that they might.

So what you have is a many-to-many relationship between Guests and
Events. The guests entity type is modelled fine by your Guest_Info
table (there are some normalization issues with it but they but they
are not relevant to this problem). The events entity type is not
correctly modelled by your Event_Info table, however. The Event_Info
table should have columns which represent the attributes of each
event, its event number, date, venue. These attributes are those
specific to the event per se, not to a guest's attendance at it. In
the jargon each non-key column is functionally dependent on the key of
the table, Event_Number, which means that for a known event number we
know the one value for each of the date, venue attributes etc. The
event number does not determine the Guest_ID as there will be more
than one per event, and the same Guest_ID may relate to more than one
event. So the Event_Info table should have no Guest_ID column, or any
other columns which are attributes of a guest's attendance at the
event, not of the event per se.

A many-to-many relationship type is modelled by a third table
Event_Guests say, with two foreign key columns Guest_ID and
Event_Number, each referencing the primary key of the Guest_Info and
Event_Info tables respectively. In combination these two columns form
the composite primary key of the Event_Guests table. If drwan nout
diagramatically the model looks like this:

Guest_Info----<Event_Guests>----Event_Info

As you can see, the many-to-many relationship has been resolved into
two one-to-many relationships, which is how such relationships are
modelled in a relational database.

The subform should be based on the Event_Guests table. You can have
the subform either in an events parent form or in a guests parent
form, one for assigning guests to events, the other for assigning
events to a guest. In the first case you'd include a combo box bound
to the Guest_ID column, in the other one bound to the Event_Number
column. In either case the value would be hidden of course and the
control would show the guest or event name from the referenced table.

Ken Sheridan
Stafford, England

 

[silva]

Perhaps I should clarify. These aren't actual events which people attend. The
term event was used loosely to refer to an event for someone making customer
contact with our central office by a comment card, e-mail, whatnot. Each
"event" specified is a comment card and such. My apologies for the confusion.

 

[ken]

The same principles apply. Each contact represents a many-to-many
relationship between a customer and a contact type, so is modelled as
customers and contact types tables with a customer contacts table
modelling the relationship by referencing the primary keys of the two
main entity types, customers and contact types. The contact type
table has columns representing the attributes of each contact type,
comment card, e-mail etc, and the customer contacts table has, in
addition to the two foreign key columns, columns representing the
attributes of each contact event, e.g. the date of the contact.

Ken Sheridan
Stafford, England

 

[silva]

You have missed the point of my problem. It is not a many-to-many, and the
only thing that could even make it construed as such is whatever text has
been entered into the Contact_Type field. But this has absolutely nothing to
do with the problem anyway.

The problem is that the moment you enter text to create a record in the
parent table using the form, it automatically creates four empty records in
the child table and pops up an error stating that the record in the parent
table does not yet exist. I need to know why it would create empty records in
a table that isn't yet having data entered into it.

 

[silva]

I think I discovered the issue. For the subforms I have onCurrent event
coding. It was originally just to determine if there were records in the
subform to properly enable and disable navigation and function buttons. Based
on the data presented, added extra code to update the fields in the form
based on the data from the record (as in radio buttons or checkboxes linked
to data fields in the child table). It would appear that maybe I need to nest
these functions in a conditional statement that checks to see if there are
any records to actually read. I'm guessing that it's creating the blank
records based on the onCurrent event coding, creating them as it runs through
the conditional statements. In one instance, it happens to be a long sequence
of case selects (linked to a series of 60 radio buttons mapped to yes/no
fields). A new record in the parent table would have no related records in
the child table, so is there a way within the subform (mapped to the child
table) to check if there are linked records before executing the update code?

 

[silva]

Nevermind. I found the problem. It was in the onCurrent event coding for the
subforms. I moved an "End If" and the problem completely disappeared.