• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

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

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.
0
skennelly
Asked:
skennelly
  • 6
  • 2
1 Solution
 
jefftwilleyCommented:
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?
J
0
 
skennellyAuthor Commented:
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.
0
 
skennellyAuthor Commented:
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.
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
jefftwilleyCommented:

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?

J
0
 
skennellyAuthor Commented:
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.
0
 
skennellyAuthor Commented:
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.
0
 
skennellyAuthor Commented:
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

0
 
skennellyAuthor Commented:
The accepted solution provided me a good template to work off. It took awhile to figure out the correct syntax for the sSQL statement
0
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

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now