Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-03-21
8
Medium Priority
?
383 Views
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.
0
Comment
Question by:skennelly
  • 6
  • 2
8 Comments
 
LVL 34

Expert Comment

by:jefftwilley
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?
J
0
 

Author Comment

by:skennelly
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.
0
 

Author Comment

by:skennelly
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Accepted Solution

by:
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?

J
0
 

Author Comment

by:skennelly
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.
0
 

Author Comment

by:skennelly
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.
0
 

Author Comment

by:skennelly
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

0
 

Author Closing Comment

by:skennelly
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
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 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