The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Adding multiple records 
Posted: 08 May 08 3:00 PM
|
I have a Tab control with text fields on the first tab, the second tab has a split screen with a treeview on one side and a datagrid on the other side. The third tab is this way also. On the last tab the data is displayed in three different grids. I am using a managed form and I am able to update the database with the first record from each grid. The code updates one table using the data from one grid. This is the code I am using:
Set objRS = CreateObject("ADODB.Recordset")
With objRS
Set .ActiveConnection = Application.GetNewConnection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open "SELECT * FROM TR_Attendee_Fee WHERE 1=2"
.AddNew
.Fields("TR_Attendee_FeeID").Value = Application.BasicFunctions.GetIDFor("TR_Attendee_Fee")
.Fields("TR_Class_AttendeeID").Value = strClassAttendeeId
.Fields("TR_ClassID").Value = strClassId
.Fields("CREATEUSER").Value = Application.BasicFunctions.CurrentUserID
.Fields("CREATEDATE").Value = Now
.Fields("MODIFYUSER").Value = Application.BasicFunctions.CurrentUserID
.Fields("MODIFYDATE").Value = Now
.Fields("Actual_Amount").Value = dgAttendeeFees.GetCurrentField("Amount")
.Fields("TR_Course_FeeID").Value = dgAttendeeFees.GetCurrentField("tr_course_feeid")
.Update
.Close
End With
Set objRS = nothing
This update is the second of four that execute. It needs to add a new record to the database for every record the grid on the treeview contains. Any ideas on the syntax to get this to loop? Everything I tried failed.
Thanks...... |
|
|
|
Re: Adding multiple records
Posted: 09 May 08 9:55 AM
|
Loop through what? the Grid's recordset? The Nodes on the Tree view?
SET rsDG = GridName.RECORDSET
WHILE NOT rsDG.EOF
strClassAttendeeId = gridname.GetcurrentField(""ClassAttendeeId) ''get the ID field value and then call your insert routine
rsDG.MOVENEXT
WEND
kinds of stuff.....
or
FOR I = 0 to rs.RecordSet.Count - 1
NEXT
MUST DO!
grid.REFRESH on all of these grids that you add records to...after you've inserted the rows in the database!!!
So what have you tried for a looping structure?
Desireable: If you are in 7.x+ don't worry about the Create, Modify fields....let the provider handle that. (also AutoIncrement possible on the ID field....) |
|
|
|
Re: Adding multiple records
Posted: 09 May 08 3:29 PM
|
RJ,
How do we determine if one of these rows is selected while we're cycling through the records? |
|
|
| |
|
Re: Adding multiple records
Posted: 12 May 08 8:39 AM
|
I figured it out, after I deleted the code I tried for my loop I started putting it back in and ended up using this:
Set objRS = CreateObject("ADODB.Recordset")
Set objRS2 = dgAttendeeFees.Recordset
With objRS
Set .ActiveConnection = Application.GetNewConnection
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open "SELECT * FROM TR_Attendee_Fee WHERE 1=2"
with objRS2
if not (.BOF and .EOF) then
intCount = .RecordCount
objRS2.MoveFirst
for i = 0 to .RecordCount - 1
objRS.AddNew
objRS.Fields("TR_Attendee_FeeID").Value = Application.BasicFunctions.GetIDFor("TR_Attendee_Fee")
objRS.Fields("TR_Class_AttendeeID").Value = strClassAttendeeId
objRS.Fields("TR_ClassID").Value = strClassId
objRS.Fields("CREATEUSER").Value = Application.BasicFunctions.CurrentUserID
objRS.Fields("CREATEDATE").Value = Now
objRS.Fields("MODIFYUSER").Value = Application.BasicFunctions.CurrentUserID
objRS.Fields("MODIFYDATE").Value = Now
objRS.Fields("Actual_Amount").Value = objRS2.Fields("Amount").Value
objRS.Fields("TR_Course_FeeID").Value = objRS2.Fields("TR_Course_FeeID").Value
objRS.Update
objRS2.MoveNext
next
objRS.Close
End if
End With
end With
Set objRS = nothing
Set objRS2 = nothing
|
|
|
|