Avatar of wlwebb
wlwebb
Flag for United States of America 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]
Microsoft Access

Avatar of undefined
Last Comment
wlwebb

8/22/2022 - Mon
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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!