insert value for foreign key automatically

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

Guest

Hi everyone!

I have some problems with the database (Access 2002) i'm trying to create.
I got something like a "master table" and some other tables (client,
conversations...) linked to the master table in one-to-many realationships. I
want the user to enter new data and records in one "basic" form where all the
data concerning the case can be entered into a record in the "master" table
(T_Case). I added some toggle and command buttons which open the forms for
the other tables on the many side of the one-to-many relationships. I have no
problems with opening these forms and they only show those records related to
the case they were opened from.
But when i try to add a record in these forms i have to enter the CAseID
again and i don't really want the user to do something with this ID.
Is there a way that i can make a button like "Add Conversation" which opens
a blank form (F_Conversation) which already contains the CAseID from the
parent table???
I tried Macros (something like openform... Setvalue...) and Codes:

DoCmd.OpenForm "T_Conversation", acNormal,,,acFormAdd
Forms!F_Conversation.TCon_CaseID = Me.TCase_CaseID '
(based on a code in one of the threads i found here in this forum)
but it did not really work.

Can anyone help me on this?
 
If you use a sub-form, then the FK will be entered for you automatically if
you set the child/master link.

Since you are launching separate forms, then you have to use code to setup
the FK.

You don't mention if you are opening the forms with a where clause..but that
would be my bet.

Likely, you got

me.refresh
docmd.openForm "childForm",,,"FKfield = " & me!id

what you can do is in this childs form BEFORE INSERT event, use:

me!FKField = forms!mainForm!id

In your example, you open the form in add mode, and the above one line in
the on-insert should do the trick...

In above, mainForm is of couse the name of the main form that called the
child form...
 
GREAT!!!!
Thank you Albert!!!!

I am now opening the new form with a macro (Open form with data mode = add,
no WHERE clause) and use your event procedure (me!FKField =
forms!mainForm!id) for the BEFORE INSERT Event in the of the child form. As
soon as i start punching in data, the foreign key changes from value zero to
the right value (= the ID of the parent/master form).

The reason why i am not using subforms is because the master form has so
many fields that it almost fills the whole screen, and by using subforms, the
user can still see the data which will be helpful when adding new data with
subforms....

For the buttons which open the related records in the different child forms
i tried WHERE clauses (in "openform" macros) similar to the one u mentioned,
some worked some didn't but i don't remember exact codes.
As i don't really have a clue abou programmingt, it was easier for me to use
the wizard for the command button. This works perfectly if u only want to
read records...

THANKS!!!
 
Back
Top