Opening a child form from a parent

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

I have two tables that have a one-to-one relationship. The first table
contains a "header" record with several numeric and text fields and the
second table contains only an ID, a key related to the first table, and a
memo field that holds a large amount of text. The idea was to improve
performance by separating the large memo field, which will not be queried
often, from the rest of the fields which will.

I am having difficulting implementing this one-to-one relationship in forms.

I have a form for the header record (parent) with a button that when clicked
will open a form for the memo field table (child). I need to pass the the id
of the parent to the child so that it can display the correct record.
However, if a child record does not yet exist, it needs to be created. This
is the part I am having trouble with.

Can anyone give me some guidance on how to implement this?

Thanks
Dave
 
One to one relationships are fun. They tend to do all sorts of
unexpected things, especially if you set them up enforcing referential
integrity with other tables and each other (I tried, I have never spent
so much time on a single problem before :-).
In the end, I think it is justified to do what you are trying. But I
would not rely on Access relationships to take care of the form linking.
Do it in code - this way you will get guaranteed results.
1. In the button click event of the parent, check if the record exists
in the daughter table by using an ADO recordset and its RecordCount property.
2. If record exists, open the child form and send it to the existing record.
3. If it does not exist, insert a new record into the child table in VBA
using Me!ID for the child's primary key.
4. Go to step 2, because the record now exists.

Good luck,
Pavel
 
Dave said:
I have two tables that have a one-to-one relationship. The first table
contains a "header" record with several numeric and text fields and the
second table contains only an ID, a key related to the first table, and a
memo field that holds a large amount of text. The idea was to improve
performance by separating the large memo field, which will not be queried
often, from the rest of the fields which will.
Click to expand...

Nothing wrong with the objective, but it doesn't make sense
for the memo table to have an ID field. A One-to-One
relationship is defined by both tables having the same
Primary Key values, i.e. the foreign key in the memo table
is also the table's primary key.


I am having difficulting implementing this one-to-one relationship in forms.

I have a form for the header record (parent) with a button that when clicked
will open a form for the memo field table (child). I need to pass the the id
of the parent to the child so that it can display the correct record.
However, if a child record does not yet exist, it needs to be created. This
is the part I am having trouble with.
Click to expand...


Use the OpenForm method's WhereCondition argument to filter
the second form to the matching record:

DoCmd.OpenForm "memoform", , , "relatedkey = " & txtID

As far as creating a record, that's up to the user. If
there is no existing record the form will open on a new
record ready for the user to enter data, but if a user
decides not to do that, the form can be closed without
creating a blank record.
 
Back
Top