How to ensure a dataset in a subForm?

[Karl-Heinz]

Hello!

I got a table “A” accessed by the mainForm and a table “B” accessed as its subForm. table “B” associates multiple entries with the primary key from table “A”, so that each row in “A” has one or more rows in “B” - not zero.
When first creating an entry in “A” a corresponding entry in “B” has to be created. More entries in “B” referencing “A” can be added later.

A real example: Table “A” holds products, Table “B” holds the price of the product and the date the price was changed. A query for an product always gets the latest corresponding price, so a entry in “B” must be ensured. An additional entry for the current price in “A” would pose another threat for inconsistency.

Following problem: If i add a new row to “A”, the row is added to the table before i can add the corresponding entry in the subForm for “B”. If i try to enter a new row in “B” first, there is no primary key in “A” i could enter, obviously. If - for some reason - the user stops after entering in “A” and forgets to enter a corresponding entry in “B”, i have an inconsistency.

My question: How can i ensure, that an entry in “A” and “B” is entered at the same time?

Thank you for your time and help
Regards
Karl

[RobertG]

You could never enter content in a subform, connected by the primary key in the mainform, before the row in the mainform exists. So you have to fill first content to the mainform and after this content to the subform.

The only possibility is to create a macro, which has to be linked to the event “Before Record change…”, reads the primary key of the table connected to mainform and looks for this key in the table connected to subform. This macro has to stop record changing and give a warning, if there doesn’t exist a row in table of subform.

Try it this way: database - subform must have entries. You have to enable macro executing.

This is the macro working in the database:

FUNCTION SubForm_has_Content(oEvent AS OBJECT) AS BOOLEAN
DIM stID AS STRING
oFormC = oEvent.Source
IF oFormC.ImplementationName = "org.openoffice.comp.svx.FormController" THEN
	oForm = oFormC.Model
	stID = oForm.getString(oForm.findColumn("ID"))
	IF stID <> "" THEN
		oSubForm = oForm.getByName("SubForm")
		IF oSubForm.RowCount = 0 THEN
			msgbox "Entry in Subform missing"
			SubForm_has_Content = False
		EXIT FUNCTION
		END IF
	END IF
END IF	
SubForm_has_Content = True
END FUNCTION