id's

[loneranger27]

Hi,

I seem to be having a problem when I pass the primary key of one form to the foreign key of another form.

I am using appraisalid as the primary key in the field form and I am passing that value to appraisalid (the foreign key) of the appraisal form.

To assign the value I am using the before insert event of the appraisal form and I have this code:
Me![appraisalid]=forms![field]![appraisalid]

THis works fine but I have some other statements in the on current event that gets some variables from the same form (field). When these statements are in place the appraisal form wont recognize the appraisalid but if I remove them then it will recongnize the appraisalid .

If you have any ideas they are welcome.

Thanks

 

[Terpsfan]

Your best bet is to first set up a relationship between the two tables, enforcing referential integrity and cascading updates and deletes. Then you should create a form, subform relationship in the form wizard. First add all the fields from the first table, then all the fields from the related table and the wizard will automatically set up a form/subform in which your foreign key is automatically updated. This method is preferable.

Now if you absolutely have to have these forms reside completely separate from each other, then you will have to enforce this relationship with code, which I wouldn't suggest unless have a great reason to do so.

 

[Rolliee]

Are you saying that you want to set the ID on a new record?


Rollie E

 

[danvlas]

You may have an 'event' conflict...
Current event always executes, whereas the BeforeInsert runs just when it is fired by attempting to write data in a new record.

Your Current event may reach a new record (so the values it searches for do not yet exist).
However, without seeing the entire code, it is like shooting in the dark.

Omega36's suggestion would help you get rid of the Primary key - foreign key management and I'd go for it. With one exception: I wouldn't enforce cascade deletes. (But this is just a matter of preferences.)
In this way Access will automatically pass the join value to the underlying table of the subform.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html