asked on

Access - Continuous Form (is A subform). How can I have the Subform create a Set number of New records to be input in to.

I have a main form and when the clerk is ready to input the data to the Subform I need it to create an exact number of new records (let's say 5).

The main form name is [frm_Reporting]
The subform name is [frm_Reporting_Details]

The subform's keyID field is [RptgDetails_ID] from table [ReportingAmt]

I have set a static number of lines to be created field for setting the exact number of lines that are always needing to be created in table [Valid_Registers] in the field [NbrOfMach]

mbizup

<< The subform's keyID field >>

It's not the subform's key ID that is important here (assuming the subform's recordsource has an autonumber primary key). The important field is the master/child link field(s) so that the subform records are associated with the correct parent once they are created. I'll refer to that field as LinkID - you'll have to change that as needed.

You can use DLookup to get the number of records needed and a loop to create those records. I'm assuming you're running this from a button on the Main Form:

Dim intRecs as integer
Dim I as integer
dim strSQL as string

intrecs = DLookup("NbrOfMach", "Valid_Registers")
for I = 1 to intrecs
      strSQL = "INSERT INTO ReportingAmt (LinkID ) VALUES (LinkID ) = " & Me.LinkID 
      CurrentDB.Execute strSQL, dbFailOnError
Next
Me.frm_Reporting_Details.Form.Requery

Open in new window

wlwebb

ASKER

Yes, they will execute by clicking a control button... I try to always use autonumber.... can't think of when I haven't right now.

Thanks for the input...
I will attempt and be back.

wlwebb

ASKER

Errors on
CurrentDb.Execute strSQL, dbFailOnError

Run-time error '3137'
Missing semicolon (;) at end of SQL statement.

when I put a semicolon after dbFailOnError it get a Compile Error:
Expected: End of statement

mbizup

Oops ... try this:

Dim intRecs as integer
Dim I as integer
dim strSQL as string

intrecs = DLookup("NbrOfMach", "Valid_Registers")
for I = 1 to intrecs
      strSQL = "INSERT INTO ReportingAmt (LinkID ) VALUES (" & Me.LinkID & ")" 
      CurrentDB.Execute strSQL, dbFailOnError
Next
Me.frm_Reporting_Details.Form.Requery

Open in new window

wlwebb

ASKER

Still get the same missing semicolon message.... hold on didn't see the other line change....

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wlwebb

ASKER

OK! I don't get an error now. And when I look at the table.. it has created the records with a zero in the amount field. that looks good......

HOWEVER,
The subform itself where the clerk will input info only shows the one line that it shows when the form first opens.... That subform is set to Continuous Form .... Data Entry = Yes

mbizup

Set data entry to NO.

DataEntry is for creating new records not viewing existing ones. Since we have created these records behind the scenes, they are existing records not new records :-)

wlwebb

ASKER

Well, that worked to show multiple lines......... but......
Now it shows ALL the records that the Subform table is linked to....
Not just the ones created for this Main Forms Rptg_ID.

?????????

I assume somehow in the code I have to tell it to display only the MainForm's RptgID records for the Child

The Master ID is Rptg_ID
The related Child is Reporting_ID.

mbizup

It sounds like your master/child links are not set up properly between your main and sub form. I'm calling it a night but can take a look tomorrow if you post a sample database.

wlwebb

ASKER

Ok... thanks for the help to here... you've already earned most all of the points available.

wlwebb

ASKER

Thanks for all of the help. Much appreciated!