Multiple tables, multiple forms - One record

[webcats]

Hi, I'm trying to create a database which has multiple tables with multiple forms relating to just one record.

I can't seem to get the next form/table to update when I enter the information. I have each form linked to each table. And when the user has finished answering the questions on the first form, they click the Next button, which opens up the next form.

But how can I link each form so that the record referenced is the same as the previous form? I can't use subforms.
The users have to be able to stop in the middle and exit, then come back later to finish entering the record. (There are 19 different forms/tables)

Thanks.

 

[WayneRyan]

web,

I don't understand the design here, but if you are on a
form and want to open up another form showing only related
data:

DoCmd.OpenForm "OtherForm",,,"[KeyField] = " & Me.Keyfield

You will have to substitute the name of your form.
KeyField refers to the field in your table that identifies
the record and Me.Keyfield is the control on your form.
In this case they are numbers, if text add single-quotes
like:

DoCmd.OpenForm "OtherForm",,,"[KeyField] = '" & Me.Keyfield & "'"

Wayne

 

[webcats]

Thanks for the reply. When I tried the OpenArgs command, it seemed to work, but I'm still having a problem with the main table not getting the foreign keys. I linked all the tables in the Relationship window. So I update the record in the main table (via form), then open the next form and enter data.

But when I try to exit out then, I'm getting a "can't update record because a required record doesn't exist in (main table).

Shouldn't it automatically pop in the foreign key in the main table?

 

[WayneRyan]

Web,

You have two problems. First the relationships that you
are defining have the main table as the "parent". There
are "children" in the other subordinate tables that do
not have a parent. They are orphans. You can find them
by seeing which children have a null parent, or a parent
that does not exist because it has been deleted.

Back to your form. The DoCmd.OpenForm command does not
care about any of the above, it will just open with any
records that qualify in the OpenArgs.

I think that what might be happening here is that you
need a main form and a subform. Link them together
using the "Master-Child" links property of the subform.
Then Access will take care of all of the synchronization
between the two forms. This also provides the "key"
values for any new child records.

If you do not use the technique above, you can base
your form on a query that uses the main form as criteria.
(Much like our OpenArgs). Make the default value for the
"key" field: =Forms![YourMainForm]![PrimaryKey]

Using this method, you have to requery the "child" form
everytime that the main form changes context.

Wayne

 

[webcats]

Thanks Wayne, I'll give that a try. Thanks for the help!

Mike