Insert in 1:N relation, Jet engine can't find record

[henkus]

Hi,

I have a Access 2003 DB, which is split in a backend and a frontend.
Backend has two tables (which are relevant for this question):
Articles (Artnr, Price and so on)
CampaignWeek (Artnr, Week and so on)

Artnr in Articles has a 1:N relation to Artnr in CampaignWeek.

In a form, which has fields both from Articles and CamapignWeek the user can enter info.
In the AfterUpdate event on the textfield containing CampaignWeek.Artnr, I check if the entered Article number allready exists in table Article. If not, I insert it during this event.
When I go to another record or close the form, I suppose Access does the actual insert. However, then I get the error message that "The Microsoft Jet database engine cannot find a record in table 'Articles' with key matching field(s) 'Artnr'." I check the Article table, and see that the insert is successfull. Any idea why Access doesn't recognize this?

/henkus

 

[wazz]

In the AfterUpdate event on the textfield containing CampaignWeek.Artnr, I check if the entered Article number allready exists in table Article. If not, I insert it during this event.

- you should be checking for "things" in the beforeupdate event.
- with fields from both tables on the same form it gets slightly awkward because you must populate the 1-side first. probably what happens is, the 1-side does get populated when the current record is updated but the many-side can't yet.
- if possible build a form with a subform: main form on articles and subform on CampaignWeek. ensure that the main form (articles) is filled in first. when you move to the subform the main form is automatically updated. also you won't have to fill in the foreign key, that is also automatic.

 

[henkus]

- you should be checking for "things" in the beforeupdate event.

I'm checking for things in the afterupdate of the text-box. this event should fire before the insert into DB? Or are you saying I should do things like this in the afterupdate event of the form?

- if possible build a form with a subform: main form on articles and subform on CampaignWeek. ensure that the main form (articles) is filled in first. when you move to the subform the main form is automatically updated. also you won't have to fill in the foreign key, that is also automatic.

Not possible, unfortunally. The form is actually allready a sub-form to a header of te campaign table...

/henkus