Creating Variable Duplicate Records from a Single Record Based on an Int Field

Hello Experts,

The VBA code below, created for an MS Access Database, was used to address an issue described/solved here; however, now instead of a constant fixed value of "12" in line 11 of the code  I would like to use a variable field named "Number of Months" to instead calculate.  Basically, I need the VBA code modified to use a variable number of months instead of 12 and for that variable number to be defined in a field named "Number of Months".

Thank you



                                         
Option Compare Database
Option Explicit
Sub addRecords()
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, strField As String, j As Integer
Dim vDate As Date, dteStart As Date, dteEnd As Date, xAmount As Double
Set rs = CurrentDb.OpenRecordset("tblImport")
Set rs2 = CurrentDb.OpenRecordset("tblExport")

Do Until rs.EOF
    xAmount = rs![Savings Field] / 12
    dteStart = rs![Date]
    For j = 0 To 11
        vDate = DateAdd("m", j, dteStart)
        Debug.Print vDate
        With rs2
            .AddNew
                ![ID] = rs![ID]
                ![Capital Project] = rs![Capital Project]
                ![Savings Name] = rs![Savings Name]
                ![Savings Description] = rs![Savings Description]
                ![Savings Start Date] = rs![Savings Start Date]
                ![Project Status] = rs![Project Status]
                ![Deep Dive Project] = rs![Deep Dive Project]
                ![Spend Type] = rs![Spend Type]
                ![Savings Type] = rs![Savings Type]
                ![Savings Frequency] = rs![Savings Frequency]
                ![Original Offer] = rs![Original Offer]
                ![Corporate CST] = rs![Corporate CST]
                ![Select DepartmentTeam] = rs![Select DepartmentTeam]
                ![Material Group] = rs![Material Group]
                ![Purchasing Group] = rs![Purchasing Group]
                ![SBU] = rs![SBU]
                ![Project ID] = rs![Project ID]
                ![e-Sourcing Utilized] = rs![e-Sourcing Utilized]
                ![e-Auction Utilized] = rs![e-Auction Utilized]
                ![Emerging Region Sourcing] = rs![Emerging Region Sourcing]
                ![BuyHON Spend] = rs![BuyHON Spend]
                ![x-SBG Project] = rs![x-SBG Project]
                ![SoleSingle Sourced] = rs![SoleSingle Sourced]
                ![Savings Categories] = rs![Savings Categories]
                ![Supplier ID] = rs![Supplier ID]
                ![Supplier Name] = rs![Supplier Name]
                ![PO] = rs![PO]
                ![Logistics Mode] = rs![Logistics Mode]
                ![Logistics Region] = rs![Logistics Region]
                ![Savings Collaborator] = rs![Savings Collaborator]
                ![BAE] = rs![BAE]
                ![Manager Approval] = rs![Manager Approval]
                ![Director Approval] = rs![Director Approval]
                ![Legacy Project ID] = rs![Legacy Project ID]
                ![Annualized Spend] = rs![Annualized Spend]
                ![Project] = rs![Project]
                ![Plant] = rs![Plant]
                ![Date] = vDate
                ![Savings Field] = xAmount
            .Update
        End With
    Next

rs.MoveNext
Loop
rs.Close
rs2.Close
End Sub

Open in new window

ShadowITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
I'm assuming you will be passing the number of months when you call this function.  For instance, if you want to use 10 months, you would call your function this way:  Call addRecords(10)

The changes you would need are:
Line 4:    Sub addRecords(NumberOfMonths As Integer)
Line 11:   xAmount = rs![Savings Field] / NumberOfMonths
Line 13:   For j = 0 To NumberOfMonths - 1

Ron
ShadowITAuthor Commented:
Ron,

Yes, it will get the number of Months as an int from a field named "NumberOfMonths".  I tried your suggested modifications but they didn't seem to work.

Thanks for your assistance.

Option Compare Database
Option Explicit
Sub addRecords(NumberOfMonths As Integer)
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, strField As String, j As Integer
Dim vDate As Date, dteStart As Date, dteEnd As Date, xAmount As Double
Set rs = CurrentDb.OpenRecordset("tblImport")
Set rs2 = CurrentDb.OpenRecordset("tblExport")

Do Until rs.EOF
    xAmount = rs![Savings Field] / NumberOfMonths 
    dteStart = rs![Date]
    For j = 0 To NumberOfMonths - 1
        vDate = DateAdd("m", j, dteStart)
        Debug.Print vDate
        With rs2
            .AddNew
                ![ID] = rs![ID]
                ![Capital Project] = rs![Capital Project]
                ![Savings Name] = rs![Savings Name]
                ![Savings Description] = rs![Savings Description]
                ![Savings Start Date] = rs![Savings Start Date]
                ![Project Status] = rs![Project Status]
                ![Deep Dive Project] = rs![Deep Dive Project]
                ![Spend Type] = rs![Spend Type]
                ![Savings Type] = rs![Savings Type]
                ![Savings Frequency] = rs![Savings Frequency]
                ![Original Offer] = rs![Original Offer]
                ![Corporate CST] = rs![Corporate CST]
                ![Select DepartmentTeam] = rs![Select DepartmentTeam]
                ![Material Group] = rs![Material Group]
                ![Purchasing Group] = rs![Purchasing Group]
                ![SBU] = rs![SBU]
                ![Project ID] = rs![Project ID]
                ![e-Sourcing Utilized] = rs![e-Sourcing Utilized]
                ![e-Auction Utilized] = rs![e-Auction Utilized]
                ![Emerging Region Sourcing] = rs![Emerging Region Sourcing]
                ![BuyHON Spend] = rs![BuyHON Spend]
                ![x-SBG Project] = rs![x-SBG Project]
                ![SoleSingle Sourced] = rs![SoleSingle Sourced]
                ![Savings Categories] = rs![Savings Categories]
                ![Supplier ID] = rs![Supplier ID]
                ![Supplier Name] = rs![Supplier Name]
                ![PO] = rs![PO]
                ![Logistics Mode] = rs![Logistics Mode]
                ![Logistics Region] = rs![Logistics Region]
                ![Savings Collaborator] = rs![Savings Collaborator]
                ![BAE] = rs![BAE]
                ![Manager Approval] = rs![Manager Approval]
                ![Director Approval] = rs![Director Approval]
                ![Legacy Project ID] = rs![Legacy Project ID]
                ![Annualized Spend] = rs![Annualized Spend]
                ![Project] = rs![Project]
                ![Plant] = rs![Plant]
                ![Date] = vDate
                ![Savings Field] = xAmount
            .Update
        End With
    Next

rs.MoveNext
Loop
rs.Close
rs2.Close
End Sub

Open in new window

Gustav BrockCIOCommented:
"Doesn't work" is not very useful.

If your original code works, Ron's modified code has to work.
So you have probably changed something else.

/gustav
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

ShadowITAuthor Commented:
Which is why I included the modified code back to Ron to see if I've missed something.  As far as I know I incorporated his suggested changes exactly as he described.
Gustav BrockCIOCommented:
Then neither did your original code work or you have changed something else.
Still, "doesn't work" leaves no clue at all.

/gustav
ShadowITAuthor Commented:
Gustav the original code worked fine.  If you don't want to help then don't but please don't troll.  I don't need your useless commentary and I'm sure Ron doesn't need you to be his advocate.   Please find someone else to harras because these iterations with you have had absolutely no value-add.
Gustav BrockCIOCommented:
Sorry, but you give us nothing to work with. As the original code works, so will the revised if you pass it 12 as parameter.

If it doesn't work, something else has happened or you don't pass a useful parameter value.
And again: "Doesn't work" leaves us no indication what the possible error can be.

/gustav
ShadowITAuthor Commented:
It will not be "passed as a parameter", it will come from a field within the dataset itself.  It will also not be the same for every record.  In one record, the "NumberOfMonths" may have a value of 4 while in the next record it could have 8.  The number of month value is completely arbitrary which is why I specify it as a variable...never said it was a parameter to be entered by the user.
Gustav BrockCIOCommented:
Well, it is a parameter of the function:

    Sub addRecords(NumberOfMonths As Integer)

so if you pass a value of 12:

    Call addRecords(12)

or just:

    addRecords 12

it has to work as before, and if you pass a value of 4 or 8, it will adjust to those values.

If it doesn't, it probably hasn't been passed a useful value for the parameter.

/gustav
IrogSintaCommented:
@ShadowIT,
You may be reading more into Gustav's comments than there is.  All he really has stated is that there is something else going on that is not obvious to us and we definitely need more information from you.  Obviously none of us here are privy to what you see when you run your process so the more info you can give us, the better equipped we are to provide assistance.  Gustav is very good in providing solutions that has stumped others and I have never known him to be other than gracious in his responses.  

Going back to your problem at hand.  Perhaps you could post some of the code you are using to call this function.  Maybe that would provide a clue.  Also, as has been stated, please let us know exactly what tells you it isn't working.  If, it's an error message, give us the specific message.  Did it not add the correct amount of records?  Were the results of the calculation incorrect?  That way, we can give you an informed answer.

Ron
mlmccCommented:
Is the number of records you need in the rs1 (tbl1Import) recordset?

If so you only need to change 2 lines in your function


Do Until rs.EOF
    [b]xAmount = rs![Savings Field] / rs1!NumberofRecords
[/b]    dteStart = rs![Date]
    [b]For j = 0 To rs1!NumberofRecords - 1
[/b]

Open in new window


Replace NumberofRecords with the correct field

mlmcc
ShadowITAuthor Commented:
Thank you mlmcc, just to clarify, I am not well-versed in VBA.  Do you mean take the original code and add this or take the original code, along with what IrogSinta previously suggested, and add this?  Also I assume the opening and closing breaks in your code aren't required, correct?
ShadowITAuthor Commented:
Getting a compile error on rs1 not being defined, please see attached.

Thank you

p.s. I just changed my field name from NumberOfMonths to your suggested NumberofRecords.

Option Compare Database
Option Explicit
Sub addRecords()
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, strField As String, j As Integer
Dim vDate As Date, dteStart As Date, dteEnd As Date, xAmount As Double
Set rs = CurrentDb.OpenRecordset("tblImport")
Set rs2 = CurrentDb.OpenRecordset("tblExport")

Do Until rs.EOF
    xAmount = rs![Savings Field] / rs1!NumberofRecords
    dteStart = rs![Date]
    For j = 0 To rs1!NumberofRecords - 1
        vDate = DateAdd("m", j, dteStart)
        Debug.Print vDate
        With rs2
            .AddNew
                ![ID] = rs![ID]
                ![Capital Project] = rs![Capital Project]
                ![Savings Name] = rs![Savings Name]
                ![Savings Description] = rs![Savings Description]
                ![Savings Start Date] = rs![Savings Start Date]
                ![Project Status] = rs![Project Status]
                ![Deep Dive Project] = rs![Deep Dive Project]
                ![Spend Type] = rs![Spend Type]
                ![Savings Type] = rs![Savings Type]
                ![Savings Frequency] = rs![Savings Frequency]
                ![Original Offer] = rs![Original Offer]
                ![Corporate CST] = rs![Corporate CST]
                ![Select DepartmentTeam] = rs![Select DepartmentTeam]
                ![Material Group] = rs![Material Group]
                ![Purchasing Group] = rs![Purchasing Group]
                ![SBU] = rs![SBU]
                ![Project ID] = rs![Project ID]
                ![e-Sourcing Utilized] = rs![e-Sourcing Utilized]
                ![e-Auction Utilized] = rs![e-Auction Utilized]
                ![Emerging Region Sourcing] = rs![Emerging Region Sourcing]
                ![BuyHON Spend] = rs![BuyHON Spend]
                ![x-SBG Project] = rs![x-SBG Project]
                ![SoleSingle Sourced] = rs![SoleSingle Sourced]
                ![Savings Categories] = rs![Savings Categories]
                ![Supplier ID] = rs![Supplier ID]
                ![Supplier Name] = rs![Supplier Name]
                ![PO] = rs![PO]
                ![Logistics Mode] = rs![Logistics Mode]
                ![Logistics Region] = rs![Logistics Region]
                ![Savings Collaborator] = rs![Savings Collaborator]
                ![BAE] = rs![BAE]
                ![Manager Approval] = rs![Manager Approval]
                ![Director Approval] = rs![Director Approval]
                ![Legacy Project ID] = rs![Legacy Project ID]
                ![Annualized Spend] = rs![Annualized Spend]
                ![Project] = rs![Project]
                ![Plant] = rs![Plant]
                ![Date] = vDate
                ![Savings Field] = xAmount
            .Update
        End With
    Next

rs.MoveNext
Loop
rs.Close
rs2.Close
End Sub

Open in new window

CompileError.jpg
ShadowITAuthor Commented:
I think I got it, can you please confirm?

                                         
Option Compare Database
Option Explicit
Sub addRecords()
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, strField As String, j As Integer
Dim vDate As Date, dteStart As Date, dteEnd As Date, xAmount As Double
Set rs = CurrentDb.OpenRecordset("tblImport")
Set rs2 = CurrentDb.OpenRecordset("tblExport")

Do Until rs.EOF
    xAmount = rs![Savings Field] / rs!NumberofRecords
    dteStart = rs![Date]
    For j = 0 To rs!NumberofRecords - 1
        vDate = DateAdd("m", j, dteStart)
        Debug.Print vDate
        With rs2
            .AddNew
                ![ID] = rs![ID]
                ![Capital Project] = rs![Capital Project]
                ![Savings Name] = rs![Savings Name]
                ![Savings Description] = rs![Savings Description]
                ![Savings Start Date] = rs![Savings Start Date]
                ![Project Status] = rs![Project Status]
                ![Deep Dive Project] = rs![Deep Dive Project]
                ![Spend Type] = rs![Spend Type]
                ![Savings Type] = rs![Savings Type]
                ![Savings Frequency] = rs![Savings Frequency]
                ![Original Offer] = rs![Original Offer]
                ![Corporate CST] = rs![Corporate CST]
                ![Select DepartmentTeam] = rs![Select DepartmentTeam]
                ![Material Group] = rs![Material Group]
                ![Purchasing Group] = rs![Purchasing Group]
                ![SBU] = rs![SBU]
                ![Project ID] = rs![Project ID]
                ![e-Sourcing Utilized] = rs![e-Sourcing Utilized]
                ![e-Auction Utilized] = rs![e-Auction Utilized]
                ![Emerging Region Sourcing] = rs![Emerging Region Sourcing]
                ![BuyHON Spend] = rs![BuyHON Spend]
                ![x-SBG Project] = rs![x-SBG Project]
                ![SoleSingle Sourced] = rs![SoleSingle Sourced]
                ![Savings Categories] = rs![Savings Categories]
                ![Supplier ID] = rs![Supplier ID]
                ![Supplier Name] = rs![Supplier Name]
                ![PO] = rs![PO]
                ![Logistics Mode] = rs![Logistics Mode]
                ![Logistics Region] = rs![Logistics Region]
                ![Savings Collaborator] = rs![Savings Collaborator]
                ![BAE] = rs![BAE]
                ![Manager Approval] = rs![Manager Approval]
                ![Director Approval] = rs![Director Approval]
                ![Legacy Project ID] = rs![Legacy Project ID]
                ![Annualized Spend] = rs![Annualized Spend]
                ![Project] = rs![Project]
                ![Plant] = rs![Plant]
                ![Date] = vDate
                ![Savings Field] = xAmount
            .Update
        End With
    Next

rs.MoveNext
Loop
rs.Close
rs2.Close
End Sub

Open in new window

mlmccCommented:
That is basically what I meant.

You need to replace  NumberofRecords with the appropriate field name from the recordset that has the number of duplicates you need.

mlmcc

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShadowITAuthor Commented:
Thanks for the help!
mlmccCommented:
Glad I could help.  I think I looked at the question with a different mindset than the other experts.  I guessed the number of records needed would be with each record and could be used.  They were thinking the number of records was coming from the calling routine thus the need for the parameter.

mlmcc
ShadowITAuthor Commented:
Yes, I realize that now but at the time we were talking around one another.  Thanks for seeing the problem domain clearly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.