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 23 2019, 07:50 AM
Post#1


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


Hi Everyone

I am trying yet again to Lop through the records on a Subform to Insert them into another Table

It is adding the data but is repeating the First Record as shown below from the Immediate Window:-

INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, Discount, DateAdded ) VALUES ( 46, 2, 3, 'May Exam', 150, 0, 23/10/2019 )
INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, Discount, DateAdded ) VALUES ( 46, 2, 3, 'May Exam', 150, 0, 23/10/2019 )

It should insert as shown below:-

INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, Discount, DateAdded ) VALUES ( 46, 2, 3, 'May Exam', 150, 0, 23/10/2019 )
INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, Discount, DateAdded ) VALUES ( 46, 2, 7, 'Sep Exam', 300, 0, 23/10/2019 )

Any help appreciated in pointing out my mistakes.

The Code used is as follows:-

CODE
Private Sub cmdMay_Click()

10        On Error GoTo cmdMay_Click_Error
        Dim strSQL As String
          Dim lngStudentID As Long
          Dim dteDateAdded As Date
          Dim mySQL  As String
          Dim i As Integer
          Dim lngDiscount As Long
          
20        lngStudentID = Me.StudentID
30        dteDateAdded = Me.DateAdded
40        lngDiscount = 0
41        i = MonthID
        'This inserts the data from current Form to the Students Exam Fees Table
        Dim rsTemp As DAO.Recordset

      'Create a copy of this forms Recordset

50    Set rsTemp = Me.RecordsetClone
60    rsTemp.MoveFirst

      'Loop through all records and insert records....
70    For i = 1 To rsTemp.RecordCount
80    mySQL = "INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, Discount, DateAdded ) " _
        & "VALUES ( " & Me!StudentID & ", " & Me!txtY & ", " & Me!MonthID & ", '" & Me!txtType & "', " & Me!ExamFeeAmount & ", " & lngDiscount & ", " & Me!DateAdded & " ) "
90      Debug.Print mySQL
100   CurrentDb.Execute mySQL, dbFailOnError
110   Next i

      'Release resources

120   rsTemp.Close

130   Set rsTemp = Nothing

140   MsgBox "Student Fees have been added.", vbInformation, "Complete"



150       On Error GoTo 0
160       Exit Sub

cmdMay_Click_Error:

170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdMay_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
 
cheekybuddha
post Oct 23 2019, 08:17 AM
Post#2


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


Try it this way, Mike:
CODE
' ...
60    rsTemp.MoveFirst

      'Loop through all records and insert records....
70    Do Until rsTemp.EOF
80    mySQL = "INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, Discount, DateAdded ) " _
        & "VALUES ( " & rsTemp!StudentID & ", " & rsTemp!txtY & ", " & rsTemp!MonthID & ", '" & rsTemp!txtType & "', " & rsTemp!ExamFeeAmount & ", " & lngDiscount & ", " & rsTemp!DateAdded & " ) "
90      Debug.Print mySQL
100   CurrentDb.Execute mySQL, dbFailOnError
110   Loop
' ...


But note - make sure you change the field names of the values being inserted to match the recordset field names and not the name of the control on the form.

hth,

d

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


Regards,

David Marten
Go to the top of the page
 
Jeff B.
post Oct 23 2019, 08:29 AM
Post#3


UtterAccess VIP
Posts: 10,336
Joined: 30-April 10
From: Pacific NorthWet


You're describing "how" you want to accomplish something. I'm not sure I understand "what" that is. If you'll describe the desired end-point, folks here might be able to offer more.

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
PaulBrand
post Oct 23 2019, 08:48 AM
Post#4



Posts: 1,747
Joined: 4-September 02
From: Oxford UK


You need to move through the recordset...

CODE
60    rsTemp.MoveFirst

      'Loop through all records and insert records....
70    Do Until rsTemp.EOF
80    mySQL = "INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, Discount, DateAdded ) " _
        & "VALUES ( " & rsTemp!StudentID & ", " & rsTemp!txtY & ", " & rsTemp!MonthID & ", '" & rsTemp!txtType & "', " & rsTemp!ExamFeeAmount & ", " & lngDiscount & ", " & rsTemp!DateAdded & " ) "
90      Debug.Print mySQL
100   CurrentDb.Execute mySQL, dbFailOnError

rsTest.MoveNext


110   Loop

This post has been edited by PaulBrand: Oct 23 2019, 08:51 AM

--------------------
Paul
Go to the top of the page
 
mike60smart
post Oct 23 2019, 09:06 AM
Post#5


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


Hi David & Paul

I get the following error which I am not following as I have checked all the names two or three times and all OK.

Attached File  Error.PNG ( 3.3K )Number of downloads: 0

--------------------
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 23 2019, 09:16 AM
Post#6


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


Is txtY bound to the underlying recordset?

Also, txtType is likely to have a different field name to which it is bound

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


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Oct 23 2019, 09:18 AM
Post#7


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


Also, just noticed that you haven't delimited what appears to be a date:
CODE
' ...
... lngDiscount & ", " & Format(rsTemp!DateAdded, "\#yyyy\-mm\-dd\#") & " ) "
' ...


Are there any text values which will need to be delimited as well?

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


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Oct 23 2019, 10:02 AM
Post#8


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


Hi David

txtY is not bound to any Control on the Subform. I am using =[Parent]![AcademicYear] to obtain the Year Value

txtType is an Unbound Control. I am using =[cboType].Column(1) to obtain the Value

The Code is now this:-

CODE
110   mySQL = "INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, DateAdded ) " _
        & "VALUES ( " & rsTemp!StudentID & ", " & rsTemp!txtY & ", " & rsTemp!MonthID & ", '" & rsTemp!txtType & "', " & rsTemp!ExamFeeAmount & ", " & Format(rsTemp!DateAdded, "\#yyyy\-mm\-dd\#") & " ) "
120     Debug.Print mySQL
130   CurrentDb.Execute mySQL, dbFailOnError



--------------------
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 23 2019, 10:09 AM
Post#9


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


Hi David

I set a Breakpoint on Line 110

When I run the Code it highlights the 2 mySQL lines

When I hover with my mouse on Values rsTemp!StudentID it says <Item not found in this Collection> ??

Attached File  BP.PNG ( 138.97K )Number of downloads: 2

--------------------
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
 
PaulBrand
post Oct 23 2019, 10:16 AM
Post#10



Posts: 1,747
Joined: 4-September 02
From: Oxford UK


Is StudentID a field in rsTemp?

--------------------
Paul
Go to the top of the page
 
mike60smart
post Oct 23 2019, 10:19 AM
Post#11


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


Hi Paul

No I am obtaining this by using =[Parent]![studentID]


--------------------
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
 
PaulBrand
post Oct 23 2019, 10:29 AM
Post#12



Posts: 1,747
Joined: 4-September 02
From: Oxford UK


Then it's NOT in the collection.

your code is writing to rsTemp!StudentID.

Either remove it from the insert and values statement or add the field to rsTemp

--------------------
Paul
Go to the top of the page
 
MadPiet
post Oct 23 2019, 10:36 AM
Post#13



Posts: 3,365
Joined: 27-February 09



I am trying yet again to Lop through the records on a Subform to Insert them into another Table

Can't you just use the foreign key to the parent record to grab all these records and then just use a simple INSERT query?

INSERT INTO MyTable (column list)
SELECT column list
FROM Sourcetable
WHERE SourceTable.ForeignKey = @ForeignKey

and then just execute that using currentdb.Execute?
Go to the top of the page
 
cheekybuddha
post Oct 23 2019, 10:46 AM
Post#14


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


CODE
110   mySQL = "INSERT INTO tblStudentsExamFeesDue ( StudentID, AcademicYear, MonthID, FeeType, Amount, DateAdded ) " _
        & "VALUES ( " & Me!StudentID & ", " & Me!txtY & ", " & rsTemp!MonthID & ", '" & Me!txtType & "', " & rsTemp!ExamFeeAmount & ", " & Format(rsTemp!DateAdded, "\#yyyy\-mm\-dd\#") & " ) "

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


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Oct 23 2019, 11:42 AM
Post#15


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


Hi David

The following inserts the first Record from the Subform.

How would I get it to Loop through the Records on the Subform?

CODE
    CurrentDb.Execute "INSERT INTO tblStudentsExamFeesDue ( StudentExamFeeID, StudentID, AcademicYear, MonthID, FeeType, Amount, DateAdded  ) " & _
                       " VALUES (" & Me!StudentExamFeeID & ", " & Me!StudentID & ", " & Me!txtY & ", " & Me!MonthID & ", '" & Me!txtType & "', " & Me!ExamFeeAmount & ", " & Format(Me!DateAdded, "\#yyyy\-mm\-dd\#") & " )"


--------------------
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 23 2019, 11:47 AM
Post#16


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


So, the values that should be taken from the subform records need to be prefaced with rstTemp.

Those that get their values from the form should be prefaced with Me.

If the values txtY and StudentID change per record in the subform then we will need to include the relationship that causes them to change.

You seem to do this a lot! I have assumed this code runs from the subform's module. It might be easier to run it from the mainform's module where you have more control over the values coming from there.

I/we are all flying a bit blind here!

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


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Oct 23 2019, 01:34 PM
Post#17


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


Hi David

The values txtY and StudentID are the same for every Record

--------------------
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 23 2019, 01:50 PM
Post#18


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


OK, so did you try what I suggested in Post#14?

NB It is different from what you posted in Post#15

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


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Oct 23 2019, 02:13 PM
Post#19


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


Hi David

Many thanks yet again for hanging in with this.


This code has finally fixed it:-

CODE
110       CurrentDb.Execute "INSERT INTO tblStudentsExamFeesDue ( StudentExamFeeID, StudentID, AcademicYear, ExamFeeTypeID, Amount, DateAdded  ) " & _
                       " VALUES (" & rsTemp!StudentExamFeeID & ", " & Me!StudentID & ", " & Me!txtY & ", " & rsTemp!ExamFeeTypeID & ", " & rsTemp!ExamFeeAmount & ", " & Format(Me!DateAdded, "\#yyyy\-mm\-dd\#") & " )"


Your explanation of where to use Me & rsTemp was the key

Many thanks yet again
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
 
cheekybuddha
post Oct 23 2019, 02:31 PM
Post#20


UtterAccess Moderator
Posts: 11,893
Joined: 6-December 03
From: Telegraph Hill


Well done, Mike! I'm glad you got it worked out!

thumbup.gif

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


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    10th December 2019 - 12:59 AM