By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,037 Members | 2,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,037 IT Pros & Developers. It's quick & easy.

Creating new records with a form and multi subforms

P: 27
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.
Dec 4 '08 #1
Share this Question
Share on Google+
5 Replies

Expert 2.5K+
P: 3,072
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 ?

Dec 5 '08 #2

P: 27
I'm kinda seeing where you are going with this, I just have to remember how to change it to an update form.
Dec 5 '08 #3

Expert 2.5K+
P: 3,072
I normally use some code like:
Expand|Select|Wrap|Line Numbers
  1. ' Insert new rec with e.g. the name or description
  2. Currentdb.Execute("INSERT INTO tblX (FieldY VALUES ('Fieldvalue')")
  3. ' Get the assigned autonumber ID
  4. lngID = Dmax("ID","tblX")
  5. ' Open form with filter:
  6. Docmd.Openform "frmInsertUpdateWise",,,"ID=" & lngID
Dec 5 '08 #4

P: 27
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.
Dec 8 '08 #5

Expert 2.5K+
P: 3,072
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" !

Dec 8 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.