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
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 SubASKER
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.
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
"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
If your original code works, Ron's modified code has to work.
So you have probably changed something else.
/gustav
ASKER
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.
Then neither did your original code work or you have changed something else.
Still, "doesn't work" leaves no clue at all.
/gustav
Still, "doesn't work" leaves no clue at all.
/gustav
ASKER
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.
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
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
ASKER
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.
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
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
@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
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
Is the number of records you need in the rs1 (tbl1Import) recordset?
If so you only need to change 2 lines in your function
Replace NumberofRecords with the correct field
mlmcc
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]Replace NumberofRecords with the correct field
mlmcc
ASKER
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?
ASKER
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.
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 SubASKER
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 SubASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the help!
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
mlmcc
ASKER
Yes, I realize that now but at the time we were talking around one another. Thanks for seeing the problem domain clearly.
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