Can display records already present, but can't add new

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello -

I have a database structure with a lot of one to many , one-to-one tables. I
am trying to bring all of that info together into one form for input.

The form, as built, has subforms to display 3 one-to-many relationships. It
has one entry each for 5 one-to-one tables. I get an error saying "The
Microsoft Jet Database Engine cannot find a record in table [one-to-one table
#3] with key matching field [ID]."

I thought the whole point of those relationships was when I brought them
together in the form, the ID would trickle down to the associated tables. As
I said before, if I manually enter records in the tables, the form displays
them perfectly.

I'm just trying to avoid having to insert redundant "ID" codes into the form
to force each table to see which record it relates to in the main table. I
have not used any VBA, b/c I don't know it.

Any help would be appreciated.

Keith
 
Keith,

Regarding... "I thought the whole point of those relationships was when
I brought them together in the form, the ID would trickle down to the
associated tables", no this is not how it works at all. Defining
Relationships allows you to enforce Referential Integrity, which means
ensuring that you can't have a record in a "child" table without a
corresponding record in the "parent" table. But Relationships don't
ever cause any data to trickle anywhere. You have to make the trickle
happen. In a form/subform scenario, this is normally achieved by the
correct setting of the subform's Link Master Fields and Link Child
Fields properties, and then entering a record in the main form before
entering in the subform.

Hope that is of some help. It is not clear how you have these "5
one-to-one tables" relating to each other and relating to the forms, so
I can't be more definitive. But I would say that generally 5 one-to-one
tables is a very unusual design, and perhaps it may be worthwhile
reviewing this structure.
 
Thanks for the reply, Steve. I'll see what combining all of those one-to-one
tables into one larger table gets me. I assume that's what you were implying
by the unusual design comment.

Upon further inspection, the problem is coming up when my central table
creates a record and the autonumber increments. The other tables that are
related (particularly the one-to-one tables) have a foreign key that is blank
so the form doesn't know how to piece them together. I suppose the only way
to ensure this would be to write some code to ensure that anytime a new
record is begun in my primary table the foreign keys automatically generate
the same number in their ID fields.

I'll consolidate tables and see what happens.
Thanks!
Keith

Steve Schapel said:
Keith,

Regarding... "I thought the whole point of those relationships was when
I brought them together in the form, the ID would trickle down to the
associated tables", no this is not how it works at all. Defining
Relationships allows you to enforce Referential Integrity, which means
ensuring that you can't have a record in a "child" table without a
corresponding record in the "parent" table. But Relationships don't
ever cause any data to trickle anywhere. You have to make the trickle
happen. In a form/subform scenario, this is normally achieved by the
correct setting of the subform's Link Master Fields and Link Child
Fields properties, and then entering a record in the main form before
entering in the subform.

Hope that is of some help. It is not clear how you have these "5
one-to-one tables" relating to each other and relating to the forms, so
I can't be more definitive. But I would say that generally 5 one-to-one
tables is a very unusual design, and perhaps it may be worthwhile
reviewing this structure.

--
Steve Schapel, Microsoft Access MVP


Keith said:
Hello -

I have a database structure with a lot of one to many , one-to-one tables. I
am trying to bring all of that info together into one form for input.

The form, as built, has subforms to display 3 one-to-many relationships. It
has one entry each for 5 one-to-one tables. I get an error saying "The
Microsoft Jet Database Engine cannot find a record in table [one-to-one table
#3] with key matching field [ID]."

I thought the whole point of those relationships was when I brought them
together in the form, the ID would trickle down to the associated tables. As
I said before, if I manually enter records in the tables, the form displays
them perfectly.

I'm just trying to avoid having to insert redundant "ID" codes into the form
to force each table to see which record it relates to in the main table. I
have not used any VBA, b/c I don't know it.

Any help would be appreciated.

Keith
Click to expand...
Click to expand...
 
On Mon, 14 Mar 2005 07:47:03 -0800, "Keith McCarron" <Keith
Hello -

I have a database structure with a lot of one to many , one-to-one tables. I
am trying to bring all of that info together into one form for input.

The form, as built, has subforms to display 3 one-to-many relationships. It
has one entry each for 5 one-to-one tables. I get an error saying "The
Microsoft Jet Database Engine cannot find a record in table [one-to-one table
#3] with key matching field [ID]."

I thought the whole point of those relationships was when I brought them
together in the form, the ID would trickle down to the associated tables. As
I said before, if I manually enter records in the tables, the form displays
them perfectly.

I'm just trying to avoid having to insert redundant "ID" codes into the form
to force each table to see which record it relates to in the main table. I
have not used any VBA, b/c I don't know it.
Click to expand...

One to one relationships are VERY rare. Are you intentionally using
subclassing/superclassing, or using one to one to enforce field level
security (the only good reasons to use one to one)?

I think the error is because one to one relationships are NOT
symmetrical: they still have a parent-child relationship, just like a
one to many. The parent table should be the recordsource of the main
form, and each "child" table should be entered using a subform, with
the linking field as the master/child link field property of the
subform. Do you perhaps have a relationship established with table #3
as a parent table? If so, you won't be able to add anything to that
child table until you have populated table #3 - just having a
relationship from Table #1 to Table #3 will NOT automagically create a
record in Table #3.

John W. Vinson[MVP]
 
A nuance I was not aware of. Apparently I only know enough to be dangerous! I
appreciate your response, John. It confirms and adds more detail to what I
took away from Steve's post. I have begun consolidating tables and will
recreate the forms/subforms. If the diagnosis is correct, I believe this will
solve the problem. In the meantime, I'll also file away that good lesson in
one-to-one relationships. I suspect some of my peers aren't aware of that
either.

Keith

John Vinson said:
On Mon, 14 Mar 2005 07:47:03 -0800, "Keith McCarron" <Keith
Hello -

I have a database structure with a lot of one to many , one-to-one tables. I
am trying to bring all of that info together into one form for input.

The form, as built, has subforms to display 3 one-to-many relationships. It
has one entry each for 5 one-to-one tables. I get an error saying "The
Microsoft Jet Database Engine cannot find a record in table [one-to-one table
#3] with key matching field [ID]."

I thought the whole point of those relationships was when I brought them
together in the form, the ID would trickle down to the associated tables. As
I said before, if I manually enter records in the tables, the form displays
them perfectly.

I'm just trying to avoid having to insert redundant "ID" codes into the form
to force each table to see which record it relates to in the main table. I
have not used any VBA, b/c I don't know it.
Click to expand...

One to one relationships are VERY rare. Are you intentionally using
subclassing/superclassing, or using one to one to enforce field level
security (the only good reasons to use one to one)?

I think the error is because one to one relationships are NOT
symmetrical: they still have a parent-child relationship, just like a
one to many. The parent table should be the recordsource of the main
form, and each "child" table should be entered using a subform, with
the linking field as the master/child link field property of the
subform. Do you perhaps have a relationship established with table #3
as a parent table? If so, you won't be able to add anything to that
child table until you have populated table #3 - just having a
relationship from Table #1 to Table #3 will NOT automagically create a
record in Table #3.

John W. Vinson[MVP]
Click to expand...
 
Consolidated all one-to-one relationships into my central table and the form
works flawlessly. Thanks for the education!
Keith

Keith McCarron said:
A nuance I was not aware of. Apparently I only know enough to be dangerous! I
appreciate your response, John. It confirms and adds more detail to what I
took away from Steve's post. I have begun consolidating tables and will
recreate the forms/subforms. If the diagnosis is correct, I believe this will
solve the problem. In the meantime, I'll also file away that good lesson in
one-to-one relationships. I suspect some of my peers aren't aware of that
either.

Keith

John Vinson said:
On Mon, 14 Mar 2005 07:47:03 -0800, "Keith McCarron" <Keith
Hello -

I have a database structure with a lot of one to many , one-to-one tables. I
am trying to bring all of that info together into one form for input.

The form, as built, has subforms to display 3 one-to-many relationships. It
has one entry each for 5 one-to-one tables. I get an error saying "The
Microsoft Jet Database Engine cannot find a record in table [one-to-one table
#3] with key matching field [ID]."

I thought the whole point of those relationships was when I brought them
together in the form, the ID would trickle down to the associated tables. As
I said before, if I manually enter records in the tables, the form displays
them perfectly.

I'm just trying to avoid having to insert redundant "ID" codes into the form
to force each table to see which record it relates to in the main table. I
have not used any VBA, b/c I don't know it.
Click to expand...

One to one relationships are VERY rare. Are you intentionally using
subclassing/superclassing, or using one to one to enforce field level
security (the only good reasons to use one to one)?

I think the error is because one to one relationships are NOT
symmetrical: they still have a parent-child relationship, just like a
one to many. The parent table should be the recordsource of the main
form, and each "child" table should be entered using a subform, with
the linking field as the master/child link field property of the
subform. Do you perhaps have a relationship established with table #3
as a parent table? If so, you won't be able to add anything to that
child table until you have populated table #3 - just having a
relationship from Table #1 to Table #3 will NOT automagically create a
record in Table #3.

John W. Vinson[MVP]
Click to expand...
Click to expand...
 
I'm having the same problem as Keith. My database isn't nearly as complex as
his, but we both have the same issue of faulty parent child relationships.
But, I seem to have hit the conceptual wall with my relationships - I've
played and played and played with what I KNOW is very simple and I just can't
get it to work. See if this makes sense to you: I have one table called
"Packages". A Package can have many "Components". I had created a 3rd table
that had one record for every Component-to-Package relationship. This table
was causing my "micorsoft jet engine" problem - i.e. I could create
relationships between existing components and packages but could not add a
new component. So, I understand the problem, but I don't see the solution.
If I get rid of the 3rd table- how should I define the relationship between
Packages & Components? (I was going to try to summarize the myriad things
I've tried and the results, but I've lost my mind on this and can't remember
the zillion & one permutations, all of which solve one problem and create
another.) ALL I want is to allow the user to use a subform to either 1) look
up existing components and link them to the package or 2) add a new component
that is linked to the package.

WHAT am I doing wrong?




John Vinson said:
On Mon, 14 Mar 2005 07:47:03 -0800, "Keith McCarron" <Keith
Hello -

I have a database structure with a lot of one to many , one-to-one tables. I
am trying to bring all of that info together into one form for input.

The form, as built, has subforms to display 3 one-to-many relationships. It
has one entry each for 5 one-to-one tables. I get an error saying "The
Microsoft Jet Database Engine cannot find a record in table [one-to-one table
#3] with key matching field [ID]."

I thought the whole point of those relationships was when I brought them
together in the form, the ID would trickle down to the associated tables. As
I said before, if I manually enter records in the tables, the form displays
them perfectly.

I'm just trying to avoid having to insert redundant "ID" codes into the form
to force each table to see which record it relates to in the main table. I
have not used any VBA, b/c I don't know it.
Click to expand...

One to one relationships are VERY rare. Are you intentionally using
subclassing/superclassing, or using one to one to enforce field level
security (the only good reasons to use one to one)?

I think the error is because one to one relationships are NOT
symmetrical: they still have a parent-child relationship, just like a
one to many. The parent table should be the recordsource of the main
form, and each "child" table should be entered using a subform, with
the linking field as the master/child link field property of the
subform. Do you perhaps have a relationship established with table #3
as a parent table? If so, you won't be able to add anything to that
child table until you have populated table #3 - just having a
relationship from Table #1 to Table #3 will NOT automagically create a
record in Table #3.

John W. Vinson[MVP]
Click to expand...
 
Foolish Aunt,

The best approach here is to let the actual natire of the data entitie
and the actual relationships between them to dictate your table
structure. Don't even think about forms until you have that sorted.
You haven't provided enough information for anyone to give specific
advice, in that it is not clear what constitutes a Package and a
Component. In particular, can a given component be part of more than
one package? Maybe some examples would help.
 
Thanks. I did initially design the table relationships before thinking about
the forms but the forms is where the problem became apparent - because I have
no problem entering data directly into the tables; it's when I tried to
create a form/subform that I discovered I'd done something wrong. This
thread helped me understand the nature of the problem so at least I feel I'm
getting closer to the solution.

A package can contain multiple components. A package has a number of data
elements, such as inventory number and price, that are unique to the package
(these are fields in the package table).
A component can belong to more than one package. Components also have
unique inventory numbers and prices, which are fields in the Components table.

My initial design included a 3rd table with only 3 fields: ID, PackageID,
ComponentID - with the idea that this table would store a record for each
Component/Package relationship. This approach worked for linking existing
components to a Package but prevented me from adding a new component. So, I
had a 2-step process: go add the component to the table, then come back and
create the relationship.

I understand now that this 3rd table is not the best approach - so I've
tried various scenarios of adding a PackageID field to the Components table
and adding a ComponentID to the Package table but I think this is a case of
too much thinking - I can't see the forest for the trees! Let me know if I
need to provide more. I was thrilled to discover this forum (and to discover
that I'm not the only one struggling with this).

-FA
 
My initial design included a 3rd table with only 3 fields: ID, PackageID,
ComponentID - with the idea that this table would store a record for each
Component/Package relationship. This approach worked for linking existing
components to a Package but prevented me from adding a new component. So, I
had a 2-step process: go add the component to the table, then come back and
create the relationship.

I understand now that this 3rd table is not the best approach - so I've
tried various scenarios of adding a PackageID field to the Components table
and adding a ComponentID to the Package table but I think this is a case of
too much thinking - I can't see the forest for the trees! Let me know if I
need to provide more. I was thrilled to discover this forum (and to discover
that I'm not the only one struggling with this).
Click to expand...

For a many to many relationship, which is what you have, the third
table *IS* the best approach. You're doing it right. Putting a
ComponentID in the Packages table REQUIRES that a package have one and
only one component; putting a PackageID in the Components table
REQUIRES that each component belong to one and only one package - and
that's not the reality of your situation!

The simplest way to manage data entry in this situation is to use a
Form based on Packages, with a subform based on your relationship
table. The ComponentID field would be entered using a Combo Box
(typically storing the component ID but displaying a human-meaningful
component name).

When you need to enter a new component, you would discover that fact
by starting to type a component name and finding that it does not
exist. The combo box control has a "Not In List" event - you can write
VBA code which will be launched when this happens. This code could
"pop up" a separate Components form, allowing the new component to be
entered; when you close the form, the new componentID will be entered
into the relationship table.

There's a simple example of using NotInList at
http://www.mvps.org/access; search for "NotInList". You'll need to
amplify this example if you want to pop up a form.

John W. Vinson[MVP]
 
Thanks for the tip on "NotInList". I started over from scratch and added
buttons to open forms where add'l data was needed. I still need to try the
NotInList code to avoid the annoying error message (that currently requires
two "escapes" to clear out). But the final frustrating piece is that I can
open the "Components" form (from a button on the "Components and Packages"
form) and add a component to the table but the only way to "refresh" the data
in the "Components and Packages" form is to close it, reopen it and
re-navigate to the record where I needed the component. I've added a button
to "Refresh the Form" but no amount of "refreshing" reads the Component table
with the newly added record (except the close/reopen refresh). Ideas?

Here's a simple description of the database:
There are 6 tables in the database
Courses: Course Number, Course Name, Order ISBN
Rule: One course has one OrderISBN but one OrderISBN can belong to multiple
courses

Packages: Order ISBN, Author, Title, 3 Price fields
Components: Component ISBN, Author, Title, Price, 6 other detail fields,
not used in relationships
Components and Packages: Stores the relationship between components and
packages

Wrong ISBNs - a lookup table of the Wrong ISBNs
Wrong ISBNs for Course - Stores the relationship between a Course and its
Wrong ISBNs

The main form is organized by course. Next to the Course information, there
is a button to add a new course. On the new course screen, there is a button
to add a package. On the Package/Components screen, there is a button to add
a component. This is all intended to simplify the process of adding new
information to the database - and they all work fine. There is just one
"klugy" aspect of Access: it doesn't refresh the data in an open form. So,
when you add information to a table using one of these buttons, the data is
there - but components, in particular, will not be available until you close
the Course and Package Information form (or the Packages with Component
Details form) and re-open it.
 
Thanks for the tip on "NotInList". I started over from scratch and added
buttons to open forms where add'l data was needed. I still need to try the
NotInList code to avoid the annoying error message (that currently requires
two "escapes" to clear out). But the final frustrating piece is that I can
open the "Components" form (from a button on the "Components and Packages"
form) and add a component to the table but the only way to "refresh" the data
in the "Components and Packages" form is to close it, reopen it and
re-navigate to the record where I needed the component. I've added a button
to "Refresh the Form" but no amount of "refreshing" reads the Component table
with the newly added record (except the close/reopen refresh). Ideas?
Click to expand...

You can REquery the form in the Close event of the popup form... or,
if you use the NotInList event on the combo box instead of using a
command button to pop up a form, Access will take care of it for you
automatically.

Comments below...
Here's a simple description of the database:
There are 6 tables in the database
Courses: Course Number, Course Name, Order ISBN
Rule: One course has one OrderISBN but one OrderISBN can belong to multiple
courses
Click to expand...

good, you've got Order ISBN as the foreign key in the "many" side
table. I'd suggest not using blanks in fieldnames; you can use "camel
case" instead - CourseNo, CourseName, OrderISBN etc.
Packages: Order ISBN, Author, Title, 3 Price fields
Click to expand...

Any time you have "3 <something> fields" you have to ask: is this
REALLY a one to many relationship? WIll there *ever* be a fourth
field? If that's even possible, then you may have a many (courses) to
many (price types) relationship, and should have another table or two.
Components: Component ISBN, Author, Title, Price, 6 other detail fields,
not used in relationships
Click to expand...

ComponentISBN is the primary key?
Components and Packages: Stores the relationship between components and
packages
Click to expand...

Again - tablenames should not contain blanks; ComponentsPackages
perhaps. You might want to make ComponentISBN and PackageID (I presume
there is one?) the joint two-field Primary Key.
Wrong ISBNs - a lookup table of the Wrong ISBNs
Wrong ISBNs for Course - Stores the relationship between a Course and its
Wrong ISBNs
Click to expand...

I'm puzzled at these. Surely there are millions of ISBN's, most of
them wrong? What's the purpose of the Wrong ISBNs table??
The main form is organized by course. Next to the Course information, there
is a button to add a new course. On the new course screen, there is a button
to add a package. On the Package/Components screen, there is a button to add
a component. This is all intended to simplify the process of adding new
information to the database - and they all work fine. There is just one
"klugy" aspect of Access: it doesn't refresh the data in an open form. So,
when you add information to a table using one of these buttons, the data is
there - but components, in particular, will not be available until you close
the Course and Package Information form (or the Packages with Component
Details form) and re-open it.
Click to expand...

That's not the case!

Put a line

Forms![Course and Package Information].Requery

in the Close event, or the AfterUpdate event, of the Components form.

John W. Vinson[MVP]
 
Back
Top