Add (x) Amount of Records to Subform Based on Numbered Value of Field in Mainform

Posted on 2011-03-21
Medium Priority
Last Modified: 2012-08-14
I have a one to many relationship between the Mainform and Subform. Within the Mainform I have 4 unbound fields. The data type for one of the unbound fields is a number field (fldNumber). I need to append (x) number of records into the subform where (x) is value of the fldNumber field. The other three unbound field values will be appended into subform bound fields. In effect:

Mainform Unbound Fields:
fldNumber = 5
fldRepsUbound = 15
fldWtUbound= 250
fldTimeUbound = 30

After selecting the Click Event in a Command button on the Main form, the following is displayed in the subform records:

Record 1: fldReps = 15; fldWt = 250; fldTime = 30
Record 2: fldReps = 15; fldWt = 250; fldTime = 30
Record 3: fldReps = 15; fldWt = 250; fldTime = 30
Record 4: fldReps = 15; fldWt = 250; fldTime = 30
Record 5: fldReps = 15; fldWt = 250; fldTime = 30

So the number of records added to the subform is based on the value of the fldNumber field in the Mainform (in this case, it adds 5 records). I have been experimenting with a few loop statements but cannot get it to work. Thanks.
Question by:skennelly
  • 6
  • 2
LVL 34

Expert Comment

ID: 35180358
Do you intend to come back to these records on the main form and perhaps add more than 5? If so, do you continue the numbering starting at 5?
What happens if you delete one of the subform records....do you renumber?

Author Comment

ID: 35180842
Once the original records are added to the subform via the command button, the unbound text boxes and command button become disabled. Therefore, the only way to add or edit the subform records is to do it individually within the subform itself. Hope this helps. Thanks J.

Author Comment

ID: 35180973
Sorry, you can individually delete the subform records, renumbering is not necessary.

The intent of the unbound text boxes and command button are to quickly generate a predetermined subset of records with the same values (duplicates). So the only purpose of the fldNumber field is to provide the number of duplicate records to generate in the subform. Therefore, if the number 5 is listed in the fldNumber text box - 5 records are generated, if the number 10 is listed, than 10 records are generated.

After the initial values are generated via the command button, the user can edit or delete them individually, while the text boxes and command buttons become disabled for that particular set of records. Thanks again.
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

LVL 34

Accepted Solution

jefftwilley earned 1500 total points
ID: 35183828

Dim i as integer
Dim sSQL as string

for i = 1 to fldNumber

sSQL = "insert into [YourTable] (Number , RepsUbound , WtUbound, TimeUbound) SELECT"
sSQL = sSQL &  i  & " , '" &  fldRepsUbound & '" , " & fldWtUbound & "," &  fldTimeUbound
Docmd.runSQL sSQL

Next i

Something like so?

Are you validating that you actually have an entry in those unbound boxes?


Author Comment

ID: 35184438
Yes, defiinitely validating if there is data in the unbound text boxes. Your code looks good, however I am trying to figure out the correct syntax of the sSQL string. If you could elaborate on that statement, it would be appreciated. I'll keep trying to work with it as well. Thanks J.

Author Comment

ID: 35190415
I was able to work with the syntax. The apostrophe's were reversed for the fldRepsUbound field. I am working now to insert it into my procedure. Thanks. I will get back to you.

Author Comment

ID: 35194952
Ok, after much trial and error, I got the following syntax to work. Thanks for your help:

Dim i As Integer
Dim sSQL As String

for i = 1 to fldNumber

sSQL = "INSERT INTO [tblDetail] (SetNumber, RepsUBound, WtUBound, TimeUBound) " _
        & "VALUES ('" & i & "', '" & (Forms![frmDetail]![fldRepsUbound]) & "', '" & (Forms![frmDetail]![fldWtUbound]) & "', '"  _
        & (Forms![frmDetail]![fldTimeUbound]) & "')"
CurrentDb.Execute sSQL

Next i


Author Closing Comment

ID: 35194965
The accepted solution provided me a good template to work off. It took awhile to figure out the correct syntax for the sSQL statement

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Beware when using the ListIndex and the Column() properties of a listbox in Access 2007.  A bug has been identified in the Access 2007 listbox code which can cause the .ListIndex property to return a -1, and the .Columns(#) property to return a NULL…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question