Form Will Not Save Data Upon Closing

[Bob Bower]

Please, if you can help...

I have WIN XP Pro, Access 2002, and the subject MDB is in Access 2000
format. My knowledge and skill level with Access is mixed. I can create
forms, tables, queries and reports, but I know nothing about code. I let
Access decide. Since 2001 I committed the sin of using Access as a flat file
database. In December I normalized with the three tables below for the 2004
records. Now I’m as lost as an Easter Egg!
I have been trying to solve this problem since Feb 3 when I posted to the
group with (Records Do Not Show In "Committed" Form) as the subject. Until
then things were doing just fine with the database. I would add a new
prospective customer record in the “Master Contact” form and assign
“Prospect” as the category. When the prospective customer places an order, I
change the category to “04 Committed To Space”. I then open the “Committed
Exhibitors” form and enter the sales information required.

Things are slightly improved, but I am stuck. When I enter a new record now,
and assign the “04 Committed To Space” category it appears in the “Committed
Exhibitors” form as expected. When I enter data in any field relating to
Tbl#2 or Tbl#3, Access assigns an ID number for that table, and populates
the fields with the default values but will not save the data. (Although now
the PK numbers are different than the ContactID PK number because every time
I attempt to input data related to exhibit space or payment the counter goes
up). Yet, the form will accept a record that was entered prior to Feb 3 and
save it with newly entered data relating to Tbl#2 or Tbl#3.

I must have done something to mess up either the table relationships, or
joins in the queries, or both. I don’t know.
The database is structured as follows.
There are 3 tables.
Tbl#1 is contact information. PK ContactID, auto number.
Tbl#2 is exhibit space information. PK ExhibitID, auto number.
Tbl#3 is payment information. PK PaymentID, auto number.
Each table reflects unique information. All the information in each table
applies only to the subject of each table.

Relationships? There is only one customer to an exhibit order. There is only
one payment record per exhibit order. It seems to me that I would do fine
with one to one relationships. I do not have the Referential Integrity box
checked. It will allow me to do it between Tbl#1 and Tbl#2, but not between
Tbl#2 and Tbl#3.

Both forms are run from different queries. The queries use all three tables.

The SQL for the “Master Contact” form:
FROM [2004 Exhibitor Contact Data] LEFT JOIN ([2004 Exhibit Space Data] LEFT
JOIN [2004 Exhibitor Payment Data] ON [2004 Exhibit Space Data].ExhibitID =
[2004 Exhibitor Payment Data].PaymentID) ON [2004 Exhibitor Contact
Data].ContactID = [2004 Exhibit Space Data].ExhibitID
ORDER BY [2004 Exhibitor Contact Data].Company;

The SQL on the “Committed Exhibitors” form:
FROM [2004 Exhibitor Contact Data] LEFT JOIN ([2004 Exhibit Space Data] LEFT
JOIN [2004 Exhibitor Payment Data] ON [2004 Exhibit Space Data].ExhibitID =
[2004 Exhibitor Payment Data].PaymentID) ON [2004 Exhibitor Contact
Data].ContactID = [2004 Exhibit Space Data].ExhibitID
WHERE ((([2004 Exhibitor Contact Data].[04 Categroy])="04 Committed To
Space"));

Where do I go from here? What should I look for?

Thanks in advance,

Bob Bower

 

[Ken Snell]

My first reaction is that you don't have the proper values in the foreign
keys in tables 2 and 3 so that the records are properly "associated" to the
parent. A foreign key (one that is the link to the parent table) cannot be
an autonumber field because it's impossible to keep it synchronized with the
parent table's primary key value.

Quite honestly, I've read through your description a number of times and
cannot "visualize" the setup adequately in my head.

I don't know what the significance of February 3 may be, but it may just be
when the foreign keys' values got out of synchronization.

It will help us if you can post details about the fields in the tables and
how the tables are supposed to relate to each other.
--
Ken Snell
<MS ACCESS MVP>

Please, if you can help...

I have WIN XP Pro, Access 2002, and the subject MDB is in Access 2000
format. My knowledge and skill level with Access is mixed. I can create
forms, tables, queries and reports, but I know nothing about code. I let
Access decide. Since 2001 I committed the sin of using Access as a flat file
database. In December I normalized with the three tables below for the 2004
records. Now I'm as lost as an Easter Egg!
I have been trying to solve this problem since Feb 3 when I posted to the
group with (Records Do Not Show In "Committed" Form) as the subject. Until
then things were doing just fine with the database. I would add a new
prospective customer record in the "Master Contact" form and assign
"Prospect" as the category. When the prospective customer places an order, I
change the category to "04 Committed To Space". I then open the "Committed
Exhibitors" form and enter the sales information required.

Things are slightly improved, but I am stuck. When I enter a new record now,
and assign the "04 Committed To Space" category it appears in the "Committed
Exhibitors" form as expected. When I enter data in any field relating to
Tbl#2 or Tbl#3, Access assigns an ID number for that table, and populates
the fields with the default values but will not save the data. (Although now
the PK numbers are different than the ContactID PK number because every time
I attempt to input data related to exhibit space or payment the counter goes
up). Yet, the form will accept a record that was entered prior to Feb 3 and
save it with newly entered data relating to Tbl#2 or Tbl#3.

I must have done something to mess up either the table relationships, or
joins in the queries, or both. I don't know.
The database is structured as follows.
There are 3 tables.
Tbl#1 is contact information. PK ContactID, auto number.
Tbl#2 is exhibit space information. PK ExhibitID, auto number.
Tbl#3 is payment information. PK PaymentID, auto number.
Each table reflects unique information. All the information in each table
applies only to the subject of each table.

Relationships? There is only one customer to an exhibit order. There is only
one payment record per exhibit order. It seems to me that I would do fine
with one to one relationships. I do not have the Referential Integrity box
checked. It will allow me to do it between Tbl#1 and Tbl#2, but not between
Tbl#2 and Tbl#3.

Both forms are run from different queries. The queries use all three tables.

The SQL for the "Master Contact" form:
FROM [2004 Exhibitor Contact Data] LEFT JOIN ([2004 Exhibit Space Data] LEFT
JOIN [2004 Exhibitor Payment Data] ON [2004 Exhibit Space Data].ExhibitID =
[2004 Exhibitor Payment Data].PaymentID) ON [2004 Exhibitor Contact
Data].ContactID = [2004 Exhibit Space Data].ExhibitID
ORDER BY [2004 Exhibitor Contact Data].Company;

The SQL on the "Committed Exhibitors" form:
FROM [2004 Exhibitor Contact Data] LEFT JOIN ([2004 Exhibit Space Data] LEFT
JOIN [2004 Exhibitor Payment Data] ON [2004 Exhibit Space Data].ExhibitID =
[2004 Exhibitor Payment Data].PaymentID) ON [2004 Exhibitor Contact
Data].ContactID = [2004 Exhibit Space Data].ExhibitID
WHERE ((([2004 Exhibitor Contact Data].[04 Categroy])="04 Committed To
Space"));

Where do I go from here? What should I look for?

Thanks in advance,

Bob Bower

 

[Bob Bower]

Thank you very much for the help.

February 3 is just the day things went nuts. No other significance.

You are right. The foreign keys in tables 2 and 3 are autonumber fields. So
that is something I must change. Should I delete the records in the tables
that do not have foreign key numbers that sync with the master table? I can
easily input them again after we fix this problem.



The database is used to track, sort, and report information surrounding a
trade show. The tables relate to each other as a three step process.

First there is simply contact information such as name, address, phone etc.
That is Tbl#1.

Of the 1200 records in tbl#1, around 275 become exhibitors and order space
in the show. The space is inventory. This is Tbl#2.

Once the space inventory is ordered, there are deposits, payments and other
data having to do with money to track for each exhibitor. This is Tbl#3.



Field details? You must mean for the purposes of serving as foreign keys.

In Tbl#1 there is only ContactID, an autonumber field, "long integer",
values "Incremental", indexed, no duplicates.

In Tbl#2 there is "ExhibitID" as the PK and it is autonumber, "long integer"
, values "Incremental", indexed, no duplicates. There is also "ContactID",
and it is a number field, "long integer", indexed, duplicates OK.

In Tbl# 3 there is "PaymentID", as the PK and it is autonumber, "long
integer", values "Incremental", indexed, no duplicates. There is also
"ContactID" and it is a number field, "long integer", indexed, duplicates
OK.



Again, I very much appreciate your help. I hope this is the sort of
information you can use to advise me on what to do about my troubles.



Bob Bower

 

[Ken Snell]

Answers/comments inline....


--
Ken Snell
<MS ACCESS MVP>

Thank you very much for the help.

February 3 is just the day things went nuts. No other significance.

You are right. The foreign keys in tables 2 and 3 are autonumber fields. So
that is something I must change. Should I delete the records in the tables
that do not have foreign key numbers that sync with the master table? I can
easily input them again after we fix this problem.

You will need to add a new field to these tables: format it as Long
Integer, no default value, and name it similar/the same as the primary key
in the other tables. Then remove the primary key status from the autonumber
field in these tables. For each record in tables 2 and 3, put the correct
"linking" value in the new field that matches to the value in the parent
table. Then delete the autonumber fields from tables 2 and 3. Then set a new
primary key for the table. You shouldn't have to delete the entire record.

The database is used to track, sort, and report information surrounding a
trade show. The tables relate to each other as a three step process.

First there is simply contact information such as name, address, phone etc.
That is Tbl#1.

Of the 1200 records in tbl#1, around 275 become exhibitors and order space
in the show. The space is inventory. This is Tbl#2.

Once the space inventory is ordered, there are deposits, payments and other
data having to do with money to track for each exhibitor. This is Tbl#3.



Field details? You must mean for the purposes of serving as foreign keys.

In Tbl#1 there is only ContactID, an autonumber field, "long integer",
values "Incremental", indexed, no duplicates.

In Tbl#2 there is "ExhibitID" as the PK and it is autonumber, "long integer"
, values "Incremental", indexed, no duplicates. There is also "ContactID",
and it is a number field, "long integer", indexed, duplicates OK.

In Tbl# 3 there is "PaymentID", as the PK and it is autonumber, "long
integer", values "Incremental", indexed, no duplicates. There is also
"ContactID" and it is a number field, "long integer", indexed, duplicates
OK.

Let me see if I'm understanding correctly (I'm going to suggest some table
structures as part of my understanding; note that these suggestions are not
"thought all the way through" and might need some tweaking!):

You have contacts (people who may want to be part of an exhibition):
tblContacts table
ContactID (primary key -- autonumber is ok)
ContactFirstName
ContactLastName
ContactAddress
ContactPhone
(etc.)

You have exhibits:
tblExhibits table
ExhibitID (primary key -- autonumber is ok)
ExhibitTypeID (foreign key -- make it Numeric, Long
Integer)
ExhibitName
ExhibitDate
ExhibitLocation
(etc.)

You have different types of exhibits:
tblExhibitTypes table
ExhibitTypeID (primary key -- autonumber is ok)
ExhibitTypeName
(etc.)

You have a list of the inventory spaces available for each ExhbitTypeID:
tblInventory table
InventoryID (primary key -- autonumber is ok)
ExhibitTypeID (foreign key -- make it Numeric, Long
Integer)
InventorySpaceSize
(etc.)

You have contacts who are participating in an exhibit (a contact may have
more than one "space"):
tblExhibitors table
ExhibitID (composite primary key with ContactID and
InventoryID -- make it Numeric, Long Integer)
ContactID (composite primary key with ExhibitID and
InventoryID -- make it Numeric, Long Integer)
InventoryID (composite primary key with ExhibitID and
ContactID -- make it Numeric, Long Integer)
(etc.)

You record when a contact who's participating in an exhibit has paid
some/all of what's owed (this setup allows for a contact to make partial
payments towards the obligation and each payment is recorded in a separate
record; you get the total paid by summing the payments for each
ContactID/InventoryID/ExhibitID combination):
tblPayments table
ContactID (composite primary key with InventoryID,
ExhibitID, and PaymentID -- make it Numeric, Long Integer)
InventoryID (composite primary key with ContactID,
ExhibitID, and PaymentID -- make it Numeric, Long Integer)
ExhibitID (composite primary key with ContactID,
InventoryID, and PaymentID -- make it Numeric, Long Integer)
PaymentID (composite primary key with ContactID,
InventoryID, and ExhibitID -- autonumber is ok)
PaymentAmount
PaymentDate
(etc.)

The above table suggestions are not meant to be 100% the best, but rather
intended to give you some ideas on what you might look at doing. If you use
indices in tables, you can forgo some composite primary keys and just use
one field as a primary key; for example, the tblPayments table could be done
this way if you create an index on ContactID, InventoryID, and ExhibitID
fields that requires the combination to be unique:
tblPayments table
ContactID (indexed with InventoryID and ExhibitID -- make
it Numeric, Long Integer)
InventoryID (indexed with ContactID and ExhibitID -- make
it Numeric, Long Integer)
ExhibitID (indexed with ContactID and InventoryID -- make
it Numeric, Long Integer)
PaymentID (primary key -- autonumber is ok)
PaymentAmount
PaymentDate
(etc.)


Post back with questions/comments after you've had a chance to think about
this.

 

[Bob Bower]

Great, thanks!
I am looking forward to the guidance. The most frustrating part of this
dilemma is that I know it is something I caused, and hopefully a relatively
simple fix. Well, at least I hope it is.

I will stay tuned...

Bob Bower
MS ACCESS Klutz

 

[Ken Snell]

? not sure what you're expecting next....I provided comments interspersed in
the quoting of your post.....

 

[Bob Bower]

Sorry. I will digest the information.

Klutz indeed.... Doh!

Bob Bower

 

[Ken Snell]

Sorry. I will digest the information.

Klutz indeed.... Doh!

< grin >

 

[Bob Bower]

You will need to add a new field to these tables: format it as Long
Integer, no default value, and name it similar/the same as the primary key
in the other tables. Then remove the primary key status from the autonumber
field in these tables. For each record in tables 2 and 3, put the correct
"linking" value in the new field that matches to the value in the parent
table. Then delete the autonumber fields from tables 2 and 3. Then set a new
primary key for the table. You shouldn't have to delete the entire record.

I have added the fields to the tables. ContactID is now CustomerID,
ExhibitID is now SpaceID, PaymentID is now PayID. The records have been
updated via copy/paste in the tables and all the numbers now match. Auto
number fields have been deleted in Tbl#2 and Tbl#3. New PK in Tbl#2 is
SpaceID, new PK in Tbl#3 is PayID. They are number, long integer, no default
value, indexed duplicates OK.

Next step questions:
Should I delete the autonumber field in Tbl#1? If not, I imagine I should
re-establish it as PK as it was before. If so, should I set CustomerID as
PK, Autonumber?

How should I set the table relationships? Which fields to which? One to one?
One to many? What should each table have?

With no advice I would link ContactID (or CustomerID if ContactID is
deleted) from Tbl#1 to SpaceID in Tbl#2 one to one. I would then set to no
referential integrity, join choice #1. I would then link ContactID from
Tbl#2 to PayID in Tbl#3, one to many, referential integrity on, and both
cascades, join choice #3.

I am, however, in no hurry to march off into Klutz Land. From the looks of
things in the forms the field additions and putting the correct values in
the fields shoud work. I've not tested it with a new record yet. I think the
relationships should be first. Your advice matters.
I'm holding off on the suggestions below pending the outcome of the
above....

Thanks,

Bob Bower

 

[Ken Snell]

I implied the relationships in the suggested table structure by using the
same name for the fields in different tables. ContactID in tblContacts table
is related to ContactsID in tblExhibitors, for example.

But, it appears that you are retaining the three-table structure that you
first posted, so the relationships that I'd suggested are not going to be
directly applicable.

In general, the related fields are those where the field is describing the
same thing in both fields. As I understand your table structure, there is
not necessarily an obvious and direct relationship between CustomerID and
SpaceID, although it appears that you are trying to use them as such.
Without seeing all the data and the full table setups, it's not advisable
for me to try to say that this setup is going to work the way you intend.
But it may.

Step back and take up pencil and paper, and draw out the tables' structures
as you now have them. Draw lines between the fields that are "the same" in
two tables. See if the structure will allow you to look up and relate the
records in tables 2 and 3 to records in tables 1 and 2, for example. That is
the best test for determining if your setup will likely serve your purposes.
(Database design always starts with pencil and paper, where you identify the
tables, the fields, the data, the relationships, etc. Then you move on to
queries for how to display and find the data, forms for how to enter and
view the data, reports for how to publish and display the data, etc.)

As my favorite chemistry textbooks always used to say at the end of a
particular challenging problem: "The details of how to solve this problem
are left to the reader." Post back with your findings!

 

[Bob Bower]

Thank you for causing me to think.
I ask for your patience, much the same as one would hope to receive from a
professor with mastery, for a willing and able, but uneducated student. I
will study your suggestions and return to the group and you with questions
of clarification and gratitude for the learning experience.
Don't give up on me...

Bob Bower
"MS Access Klutz"

 

[Ken Snell]

Thank you for causing me to think.
I ask for your patience, much the same as one would hope to receive from a
professor with mastery, for a willing and able, but uneducated student. I
will study your suggestions and return to the group and you with questions
of clarification and gratitude for the learning experience.
Don't give up on me...

I won't, if you won't .... ;-)

 

[Bob Bower]

Good. Thanks...

Still working on it...

Bob Bower