Solved

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

Posted on 2011-03-21
8
375 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 34

Accepted Solution

by:
jefftwilley earned 500 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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ms access 2010 vba, copy table from db1 to db2 from db3 27 46
Dynamically Reorder List Box 4 38
ms access filter query with empty combobox 5 29
Binding recordsets to a form 6 24
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

777 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