Adding a new record to subform in parent form BeforeUpdate event

[ArviLaanemets]

Hi

My situation:

I have a application, which is linked several ODBC (SQL Server) databases (a datasource for every database, but the server is same). Users are employees in various subunits of company (Sites);

There is a form for registering chemicals. The chemicals registry (ChemReg) is same for all Sites, a chemical is identified by Catalogue and ChemCode (there is an autonumeric ID (CRID) too, but for current problem it was causing additional problems, so I don't use it here). Is the chemical used in certain Site is determined with crosstable ChemSite: Catalogue, ChemCode, SiteCode. Forms datasource is SQL Server view:
SELECT * FROM dbo.ChemReg WHERE CRID IN (SELECT reg.CRID FROM dbo.ChemSite site LEFT OUTER JOIN dbo.ChemReg reg ON reg.Catalogue = site.Catalogue AND reg.ChemCode = site.ChemCode WHERE SiteCode = 'SiteCode')
I.e. application displays only chemicals used in current front-end's Site (every Site gets his own front-end, where SiteCode is determined by global variable).

The problem is, that when user registers a new chemical, it must be registered with proper SiteCode in table ChemSite too - otherwise it isn't displayed in form at all. And it looks like I need to do this in form's BeforeUpdate event.

There are 2 possible solutions, I think.
1. Add a new record in BeforeUpdate event through code, but as I have several datasources, it will be quite complicated (I have to check, to which datasource the table is linked);
2. Add a new record to hidden subform on form, set values for controls on this form, and save the record. Currently I'm trying this.

I found out, that I can't use the subform as linked form (Catalogue, ChemCode, SiteCode) , because at moment when first control (SiteCode) on form is populated for new record, subform's SiteCode will be populated, and others controls will be populated too with values from some random record. So currently I have unbound subform, and it displays of-course 1st record of ChemSite.



The problem is, how can I add a new record to this subform? It looks like to do this, I need to activate the subform, but it means that I'm leaving the record in parent form - and the BeforeUpdate event is fired again! Is there a way to do this, or I have to try the code instead?

[Homegrownandy]

is the site code always unique. I mean every time you add chemicals is it always a new site. or could you pick from an existing one?

[ArviLaanemets]

Hi

The SiteCode is same for certain front-end, I set it up separately for every Site's front-end.

To be more detailed, I have to check parent form's Catalogue, ChemReg recordsource fields and global SiteCode variable against fields in ChemSite table, and:

1. When there is a record in ChemSite where all three match, and forms NewRecord property is False, then ChemReg is updated;
2. When there is a record in ChemSite where all three match, and forms NewRecord property is True, then update is canceled (probably user is given a choice between moving to existing record, or continuing with same new record to register a different chemical);
3. When there are matching Catalogue and ChemCode, but no matching SiteCode, a new record is added to ChemSite table. ChemReg is updated;
4. When either Catalogue or ChemCode don't match, a new record is added to ChemSite table and to ChemReg Table.

[ArviLaanemets]

Btw, currently after updating the record in parent form is marked deleted. At same time the new record appears in ChemReg table. I think it is deleted from form, because there is no record in view on SQL server which matches for inserted record.

[ArviLaanemets]

OK. It looks like I got this subform's new record problem solved. I'm setting the new record for subform earlier - in parent forms OnCurrent event (previously checking, is subform's NewRecord property True or False)