?
Solved

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

Posted on 2011-03-21
8
Medium Priority
?
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

718 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