Systematically add Records Through RecordsetClone

[Intui_Sol]

Hello,

I was looking for some help. I am trying to add records based on the
'Start_End_Difference' field. I have 183 records in a MS Access 2003
in a form. I want to change the 'Start_End_Difference' to
('Start_End_Difference'-1) until 0. Basically I have data starting
and ending in different months. I'm trying to graph data new to the
month and carried over from the previous (stacked chart)

The code only goes through the first record. It successfully creates
2 records but then stops. It appears that it is going to a new record
for some reason, eventually getting a "Invalid Use of Null Error, 94"

Here is my code

X = 0
Do Until X = 183
Reps = Forms("f_Carry_Over").[Start_End_Difference].Value
For i = 1 To Reps

With Me.RecordsetClone
.AddNew
![Field1] = Me.[Field1]
![Field2] = Me.[Field2]
![Field3] = Me.[Field3]
![Field4] = Me.[Field4]
![Field5] = Me.[Field5]
![FIeld6] = Me.[Field6]
![Field7] = Me.[Field7]
![Field8] = Me.[Field8]
![Status] = Me.[Status] '
![Month_Start] = Me.dateadd("m",1,Workstop_Month_Start)
![Cleared_Date] = Me.Workstop_Cleared_Date
![Start_End_Difference] = Me.Start_End_Difference - i
![Monthly_Status] = "Carry Over"
.Update
Me.Bookmark = .LastModified
End With
Next i
Set Reps = Nothing
lngID = Me.[ID]
' Do your update stuff
With Me.RecordsetClone
.FindFirst "[ID]=" & lngID
Me.Bookmark = .Bookmark
End With
DoCmd.GoToRecord , , acNext

X = X + 1

Loop

Please help

 

[Mark A. Sam]

You need to move through the recordset. Incrementing X and your Do Loop
won't do that for you. Try this as a starting point.



' X = 0
' Do Until X = 183
' Reps = Forms("f_Carry_Over").[Start_End_Difference].Value
' For i = 1 To Reps

With Me.RecordsetClone
.MoveFirst
Do Until .EOF 'This will move you through the recordset
.AddNew
![Field1] = Me.[Field1]
![Field2] = Me.[Field2]
![Field3] = Me.[Field3]
![Field4] = Me.[Field4]
![Field5] = Me.[Field5]
![FIeld6] = Me.[Field6]
![Field7] = Me.[Field7]
![Field8] = Me.[Field8]
![Status] = Me.[Status] '
![Month_Start] = Me.dateadd("m",1,Workstop_Month_Start)
![Cleared_Date] = Me.Workstop_Cleared_Date
![Start_End_Difference] = Me.Start_End_Difference - i
![Monthly_Status] = "Carry Over"
.Update
Me.Bookmark = .LastModified
.MoveNext
Loop

End With
Next i

' Set Reps = Nothing
' lngID = Me.[ID]
' Do your update stuff
' With Me.RecordsetClone
' .FindFirst "[ID]=" & lngID
' Me.Bookmark = .Bookmark
' End With
' DoCmd.GoToRecord , , acNext

' X = X + 1

' Loop


Note I remmed out some lines. I think this is what you are asking for.

God Bless,

Mark A. Sam

 

[Steve Sanford]

in a form. I want to change the 'Start_End_Difference' to
('Start_End_Difference'-1) until 0.

But your code has:

![Start_End_Difference] = Me.Start_End_Difference - i

See the difference?? The code should be a number one (1), not the letter "I".

I'm really not sure what you are trying to do. Lets say that Reps = 10.

If your code ran, it would add 1,830 records! Is that what you are trying to
do??

Could you explain what you are trying to do in a little more detail??


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hello,

I was looking for some help. I am trying to add records based on the
'Start_End_Difference' field. I have 183 records in a MS Access 2003
in a form. I want to change the 'Start_End_Difference' to
('Start_End_Difference'-1) until 0. Basically I have data starting
and ending in different months. I'm trying to graph data new to the
month and carried over from the previous (stacked chart)

The code only goes through the first record. It successfully creates
2 records but then stops. It appears that it is going to a new record
for some reason, eventually getting a "Invalid Use of Null Error, 94"

Here is my code

X = 0
Do Until X = 183
Reps = Forms("f_Carry_Over").[Start_End_Difference].Value
For i = 1 To Reps

With Me.RecordsetClone
.AddNew
![Field1] = Me.[Field1]
![Field2] = Me.[Field2]
![Field3] = Me.[Field3]
![Field4] = Me.[Field4]
![Field5] = Me.[Field5]
![FIeld6] = Me.[Field6]
![Field7] = Me.[Field7]
![Field8] = Me.[Field8]
![Status] = Me.[Status] '
![Month_Start] = Me.dateadd("m",1,Workstop_Month_Start)
![Cleared_Date] = Me.Workstop_Cleared_Date
![Start_End_Difference] = Me.Start_End_Difference - i
![Monthly_Status] = "Carry Over"
.Update
Me.Bookmark = .LastModified
End With
Next i
Set Reps = Nothing
lngID = Me.[ID]
' Do your update stuff
With Me.RecordsetClone
.FindFirst "[ID]=" & lngID
Me.Bookmark = .Bookmark
End With
DoCmd.GoToRecord , , acNext

X = X + 1

Loop

Please help