Creating new records with a form and multi subforms

[Randoz]

I have a table with 8 child tables that are suppose to be one-to-one relationship to main table (some how it got changed to one-to-many). I am haveing trouble creating a new record in a form that contains the 8 tables as subforms. I can create a new record in the main form but when I click one of the subforms, I get a message that the Microsoft jet database engine cannot find a record in the table 'tblxxx' with key matching field(s) 'qryxxx.indexno'. I have ensured that all the tables are connected in the relationship window and that the primary key and a foreign key are identical name and formating. Oh, I have the source for the forms coming from a query if that helps. Thanks for the help.

[nico5038]

My "trick" to solve this is to change the main form into an "update" form.
The button that triggers this form to show will just create a new record and filters for this record. Thus a record is present and no problems will occur when adding related records in the subform.
The back draw will be that when a user Cancels the insert you'll have to delete the record.

Getting the idea ?

Nic;o)

[Randoz]

I'm kinda seeing where you are going with this, I just have to remember how to change it to an update form.

[nico5038]

I normally use some code like:

' Insert new rec with e.g. the name or description
Currentdb.Execute("INSERT INTO tblX (FieldY VALUES ('Fieldvalue')")
' Get the assigned autonumber ID
lngID = Dmax("ID","tblX")
' Open form with filter:
Docmd.Openform "frmInsertUpdateWise",,,"ID=" & lngID
 

Nic;o)

[Randoz]

Forgive but I'm a little confused at the first line. If my main form is frmPersEntry based on qryMainConnector based on all the tables linked and the subform is frmEducation, based on qryEducation then table tblEducation. tblIndex has field 'Index' linked to field 'EdIndexNo' in tblEducation. How would I write the line? Thanks.

[nico5038]

Your form frmPersEntry should be based on the "Main" table.
When placing a subform for a related table Access will propose a linkage field. Just accept that and Access will automatically synchronize the forms.

Using a query with multiple tables will in general cause a form to be "Read only" !

Nic;o)