UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Loop, Access 2016    
 
   
mike60smart
post Oct 17 2019, 10:37 AM
Post#1


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi Everyone

I am trying to use the following code to insert a number of records into a table from a Continuous Subform.

It inserts the correct number of records but it duplicates the data from the first record only?

What would I need to modify in the code to allow it to loop through each of the records?

Any help appreciated.

The data in the subform is as shown below:==-

Attached File  Sub.PNG ( 10.23K )Number of downloads: 12


The table displays the following:-

Attached File  table.PNG ( 16.22K )Number of downloads: 7


CODE
Private Sub cmdInsert_Click()

10        On Error GoTo cmdInsert_Click_Error

      Dim rsTemp As DAO.Recordset
      Dim mySQL  As String
      Dim i As Integer

      'Create a copy of this forms Recordset

20    Set rsTemp = Me.RecordsetClone
30    rsTemp.MoveFirst

      'Loop through all records and insert records....
40    For i = 1 To rsTemp.RecordCount
50    mySQL = "INSERT INTO tblStudentsOtherPayments ( ClassID, StudentID, OtherYearID, OtherMonthID, Other, Amount ) " _
              & "VALUES ( " & Me!ClassID & ", " & Me!StudentID & ", " & Me!AcademicYearID & ", " & Me!MonthID & ", '" & Me!Other & "', " & Me!Amount & ") "
60    CurrentDb.Execute mySQL, dbFailOnError
70    Next i

      'Release resources

80    rsTemp.Close

90    Set rsTemp = Nothing


100   MsgBox "All Other payments added!!", vbInformation


          
110       On Error GoTo 0
120       Exit Sub

cmdInsert_Click_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInsert_Click, line " & Erl & "."

End Sub

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
orange999
post Oct 17 2019, 10:42 AM
Post#2



Posts: 1,984
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Mike,

I think you need to move to the next record in your loop.
rsTemp.movenext

--------------------
Good luck with your project!
Go to the top of the page
 
LeeAnn
post Oct 17 2019, 10:46 AM
Post#3



Posts: 1,280
Joined: 2-February 00
From: Mississippi USA Central Time Zone


You ae not telling it to go to the next records.

Try this:

CODE
40    For i = 1 To rsTemp.RecordCount
50    mySQL = "INSERT INTO tblStudentsOtherPayments ( ClassID, StudentID, OtherYearID, OtherMonthID, Other, Amount ) " _
              & "VALUES ( " & Me!ClassID & ", " & Me!StudentID & ", " & Me!AcademicYearID & ", " & Me!MonthID & ", '" & Me!Other & "', " & Me!Amount & ") "
60    CurrentDb.Execute mySQL, dbFailOnError
65     rsTemp.MoveNext
70    Next i



Also I would probably use the while not eof instead of a for loop but either will probably work.


--------------------
Lee Ann Davidson
Go to the top of the page
 
mike60smart
post Oct 17 2019, 10:55 AM
Post#4


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi orange & Lee Anne

Changed the code to this and it did exactly the same??

CODE
Private Sub cmdInsert_Click()

10        On Error GoTo cmdInsert_Click_Error

      Dim rsTemp As DAO.Recordset
      Dim mySQL  As String
      Dim i As Integer

      'Create a copy of this forms Recordset

20    Set rsTemp = Me.RecordsetClone
30    rsTemp.MoveFirst

      'Loop through all records and insert records....
40    For i = 1 To rsTemp.RecordCount
50    mySQL = "INSERT INTO tblStudentsOtherPayments ( ClassID, StudentID, OtherYearID, OtherMonthID, Other, Amount ) " _
              & "VALUES ( " & Me!ClassID & ", " & Me!StudentID & ", " & Me!AcademicYearID & ", " & Me!MonthID & ", '" & Me!Other & "', " & Me!Amount & ") "
60    CurrentDb.Execute mySQL, dbFailOnError
61      rsTemp.MoveNext
70    Next i

      'Release resources

80    rsTemp.Close

90    Set rsTemp = Nothing


100   MsgBox "All Other payments added!!", vbInformation


          
110       On Error GoTo 0
120       Exit Sub

cmdInsert_Click_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInsert_Click, line " & Erl & "."

End Sub

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
arnelgp
post Oct 17 2019, 11:07 AM
Post#5



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


and don't use Me
it will only copy the first record, even if you use rst.MoveNext

use rst!ClassID, rst!StudentID, etc..

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
mike60smart
post Oct 17 2019, 11:14 AM
Post#6


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi Arnelgp

Tried that and now get the following error:-

Attached File  Error.PNG ( 24.09K )Number of downloads: 8

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
mike60smart
post Oct 17 2019, 11:18 AM
Post#7


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi Arnelgp

I added Dim rst as String but now get the following error:-

Attached File  Dim.PNG ( 40.75K )Number of downloads: 7

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
arnelgp
post Oct 17 2019, 11:28 AM
Post#8



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


its rsTemp

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
mike60smart
post Oct 17 2019, 11:33 AM
Post#9


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi Arnelgp

Changed to rsTemp and now get this error:-

Attached File  Error.PNG ( 18.08K )Number of downloads: 12

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
orange999
post Oct 17 2019, 11:48 AM
Post#10



Posts: 1,984
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Mike,

Please show us the latest code for the cmdInsert button click event.
Also, have you tried putting a break point in the code to see the values involved?

--------------------
Good luck with your project!
Go to the top of the page
 
arnelgp
post Oct 17 2019, 11:57 AM
Post#11



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


sorry, had to change the code a little bit, if you don't mind.
CODE
Private Sub cmdInsert_Click()

    On Error GoTo cmdInsert_Click_Error

Dim rsTemp As DAO.Recordset
Dim mySQL  As String
Dim i As Integer
Dim cIDs As New Collection

'Create a copy of this forms Recordset

Set rsTemp = Me.RecordsetClone
rsTemp.MoveFirst

'Loop through all records and insert records....
With rsTemp
    While Not .EOF
        cIDs.Add !StudentID.value, !StudentID.value & ""
        .MoveNext
    Wend
End With

rsTemp.Close
Set rsTemp = Nothing

If cIDs.count > 0 Then
    
    For i = 1 To cIDs.count
        mySQL = "INSERT INTO tblStudentsOtherPayments ( ClassID, StudentID, OtherYearID, OtherMonthID, Other, Amount ) " _
                & "SELECT  TOP 1 t.ClassID, t.StudentID, t.OtherYearID, t.OtherMonthID, t.Other, t.Amount FROM tblStudentsOtherPayments AS t " _
                & "WHERE t.StudentID = " & cIDs(i) & " ORDER BY t.OtherYearID DESC, t.OtherMonthID DESC;"
        CurrentDb.Execute mySQL, dbFailOnError
    Next
Next
Set cIDs = Nothing
Me.Requery

MsgBox "All Other payments added!!", vbInformation

End If
    On Error GoTo 0
    Exit Sub

cmdInsert_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.description & ") in procedure cmdInsert_Click, line " & Erl & "."

End Sub

This post has been edited by arnelgp: Oct 17 2019, 12:18 PM

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
mike60smart
post Oct 17 2019, 12:37 PM
Post#12


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi Arnelgp & Orange

I have put together a stripped down Test Db attached.

On Open it displays the Form & Subform I am working with.

Select a Month Due , Fee Type and enter an Amount then Tab out of the Amount Control.

Run the Insert Payments.

Attached File  Test.zip ( 316.36K )Number of downloads: 5

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
arnelgp
post Oct 17 2019, 12:49 PM
Post#13



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


the form is not on the db attached.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
mike60smart
post Oct 17 2019, 02:08 PM
Post#14


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi arnelpg

Sorry My mistake
Attached File  Test.zip ( 50.34K )Number of downloads: 6


--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
cheekybuddha
post Oct 17 2019, 03:54 PM
Post#15


UtterAccess VIP
Posts: 11,675
Joined: 6-December 03
From: Telegraph Hill


Hi Mike,

You can also try it this way:
CODE
Private Sub cmdInsert_Click()
On Error GoTo cmdInsert_Click_Error

  Dim strSrc As String
  Dim mySQL  As String

10  strSrc = "SELECT ClassID, StudentID, OtherYearID, OtherMonthID, Other, Amount FROM "
20  If Left(Me.RecordSource, 6) = "SELECT" Then
30    strSrc = strSrc & "(" & Me.RecordSource
40    If Right(strSrc, 1) = ";" Then strSrc = Left(strSrc, Len(strSrc) - 1)
50    strSrc = strSrc & ")"
60  Else
70    strSrc = strSrc & "[" & Me.RecordSource & "]"
80  End If

90  mySQL = "INSERT INTO tblStudentsOtherPayments ( ClassID, StudentID, OtherYearID, OtherMonthID, Other, Amount ) "  & _
            strSrc & ";"

100  CurrentDb.Execute mySQL, dbFailOnError

cmdInsert_Click_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInsert_Click, line " & Erl & "."

End Sub


hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
arnelgp
post Oct 17 2019, 10:04 PM
Post#16



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


mike only want to add records to tblStudentsOtherPayments table.
the recordsource don't have all the necessary fields.
try this.
This post has been edited by arnelgp: Oct 17 2019, 10:05 PM
Attached File(s)
Attached File  Test_3.zip ( 46.13K )Number of downloads: 2
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
cheekybuddha
post Oct 18 2019, 03:30 AM
Post#17


UtterAccess VIP
Posts: 11,675
Joined: 6-December 03
From: Telegraph Hill


Hmmm... OK. I didn't look at the attachment.

I was just going by the original attempt:
CODE
' ...
50    mySQL = "INSERT INTO tblStudentsOtherPayments ( ClassID, StudentID, OtherYearID, OtherMonthID, Other, Amount ) " _
              & "VALUES ( " & Me!ClassID & ", " & Me!StudentID & ", " & Me!AcademicYearID & ", " & Me!MonthID & ", '" & Me!Other & "', " & Me!Amount & ") "
' ...

which implied that the calling form had the required fields in its RecordSource/recordset.

My suggestion in Post #15 should have read:
CODE
' ...
10  strSrc = "SELECT ClassID, StudentID, AcademicYearID, MonthID, Other, Amount FROM "
' ...


thumbup.gif

d

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Oct 18 2019, 08:33 AM
Post#18


UtterAccess VIP
Posts: 13,465
Joined: 6-June 05
From: Dunbar,Scotland


Hi arnelgp & David

Arnelgp - Your revised code works a treat. Many many thanks

David - Your revised code just inserts the first record only.

cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
arnelgp
post Oct 18 2019, 08:50 AM
Post#19



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


it's better to use querydef, you don't need to worry of adding
what type of delimiter to use on your field. just plug and play.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
MadPiet
post Oct 18 2019, 09:15 PM
Post#20



Posts: 3,356
Joined: 27-February 09



Wait. If you have a single set of records already in the table that you want to clone for a whole bunch of other students, why not do a deliberate cross join and append?

INSERT INTO FinalTable(StudentID, CourseID,...)
(SELECT StudentID, CourseID,...
FROM Student CROSS JOIN Course
WHERE StudentID IN (…)
AND CourseID IN (…)

Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th November 2019 - 03:33 PM