Make an autonumber in a subform to follow the Autonumber in Main form?

[catty1160]

Hi, I have a problem, maybe someone can help:

I have a Main form and the subform is inside the Main form. They are now linked by the field AutoNo in Main and AutoNo field in subform. (Both of the AutoNo fields are autonumber fields). Main form feed to tbl_Main, Subform feeds to tbl_Subform.

When I open the Main form, the Main form will be assigned an autonumber in AutoNo field. I started to go to the Subform (an continuos form) to enter the new record, then the AutoNO of a record in subform is asssigned. But it is not the same as the one in Main form. I need to make the AutoNo in the new record of th subform match to the one just assigned in hte Main form.

The reason that I need this functiion is because I have a query to do the report combined some data on Main form and some data in the Subform. Main form data of that particular AutoNo that will need to match to the data of the subform belonging to that same AutoNO. In the criteria of that query (under AutoNo field of Subform), I enter as "[FORMS]![Main]![AutoNo]". Under this criteria, if the AutoNo in Subform can not be assigned matched to the AutoNo in Main form, the query will not able to produce the report.

Any advice to resolve this for me to get the report. Thank you a lot.

Catty

 

[SeeThru]

You need two number fields in the sub form.

One autonumber that has to be unique, and not related to the main form, and then an integer field that is the field that links to the main form.

Relate the two fields using the link child/parent bit in form design, and it will be automatically filled in when you create a new sub record.

 

[catty1160]

Ok, I create 2nd number (called "LinkNo') in Subform and its tbl_Subform. In the Data Control Source property of "LinkNo", I wrote "=[FORMS]![Main]![AutoNo]". I do see the LinkNo in Subform will match the AutoNo in Main form. Now the two forms are linked by AutoNo of MAin form and AutoNo of Subform. I then go to the query, add the field of LinkNo of Subform to the query and its criteria is written as [FORMS]![[Main]![AutoNo]. After done all these, I then tried to enter a new record in Main form and a set of new record in Subform. I tried to preview the report, the query still not able to come out anything. Did I do anything wrong?

I am not pretty sure that you said "Relate the two fields using the link child/parent bit in form design". Which 2 fields you relay to? Can you be more specific?

Thank you.

Catty

 

[951753852]

You might also try to set up a relationship that enforces refential integrity and check to cascade update and delete related records. Then in the main table you have the auto number field and the sub table has a field that is number / interger. Make sure the relationship is joined between these two fields. Now when you add records to the sub table the id will flow from the main table. Also when deleting records, the sub table will also be removed of the linked data.