cycling through records in a recordset

  • Thread starter Thread starter JulieD
  • Start date Start date
J

JulieD

Hi All

i know i've done this before, but can't remember how -
i have a form - based on a table (tbl_Assignments) - with a primary key
field (primary key made up of two fields) called AssignNum (long integer).
What i do is when the user picks a value from a combo box, i run an append
query to append a series of records into the tbl_Assignments and then
display this information in the form so that they can modify it. As
AssignNum is a primary key field, i populate it initially with the value of
another field in the select / append query. But then i would like to cycle
through the records and renumber the AssignNum field starting from 1 each
time (this is before they are used anywhere else).

i'm guess that i need to use a select query to drag out the records that
i've just appended (sorted in the order that i want) and then cycle through
the recordset updating each record in turn - it's this last bit i can't get.

the bit of code i need help with (i think) is:
strsql = "SELECT jTBL_ClassAssignment.ClassID,
jTBL_ClassAssignment.AssignNum, jTBL_ClassAssignment.AssignmentSortID FROM
jTBL_ClassAssignment WHERE jTBL_ClassAssignment.ClassID = " & intclassid & "
ORDER BY jTBL_ClassAssignment.AssignmentSortID;"
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
rst.MoveFirst
i = 1
For Each ??????? in rst
rst("AssignNum") = i
Next


Please help
Cheers
JulieD
 
JulieD said:
Hi All

i know i've done this before, but can't remember how -
i have a form - based on a table (tbl_Assignments) - with a primary key
field (primary key made up of two fields) called AssignNum (long integer).
What i do is when the user picks a value from a combo box, i run an append
query to append a series of records into the tbl_Assignments and then
display this information in the form so that they can modify it. As
AssignNum is a primary key field, i populate it initially with the value of
another field in the select / append query. But then i would like to cycle
through the records and renumber the AssignNum field starting from 1 each
time (this is before they are used anywhere else).

i'm guess that i need to use a select query to drag out the records that
i've just appended (sorted in the order that i want) and then cycle through
the recordset updating each record in turn - it's this last bit i can't get.

the bit of code i need help with (i think) is:
strsql = "SELECT jTBL_ClassAssignment.ClassID,
jTBL_ClassAssignment.AssignNum, jTBL_ClassAssignment.AssignmentSortID FROM
jTBL_ClassAssignment WHERE jTBL_ClassAssignment.ClassID = " & intclassid & "
ORDER BY jTBL_ClassAssignment.AssignmentSortID;"
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
rst.MoveFirst
i = 1
For Each ??????? in rst
rst("AssignNum") = i
Next


Please help
Cheers
JulieD
Click to expand...

You can't use For..Each..Next in Recordsets Object, it's only allowed on a
Collection,
so try like this:

Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
rst.MoveFirst
i = 1
Do Untill rst.eof
rst.edit
rst("AssignNum") = i
rst.Update
rst.movenext
loop


Alessandro(IT)
 
i'm guess that i need to use a select query to drag out the records that
i've just appended (sorted in the order that i want) and then cycle through
the recordset updating each record in turn - it's this last bit i can't get.

the bit of code i need help with (i think) is:
strsql = "SELECT jTBL_ClassAssignment.ClassID,
jTBL_ClassAssignment.AssignNum, jTBL_ClassAssignment.AssignmentSortID FROM
jTBL_ClassAssignment WHERE jTBL_ClassAssignment.ClassID = " & intclassid & "
ORDER BY jTBL_ClassAssignment.AssignmentSortID;"
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
rst.MoveFirst
i = 1
For Each ??????? in rst
rst("AssignNum") = i
Next
Click to expand...


You don't need any VBA code to do this at all, actually. You can use
domain functions to calculate the incremental assign number. Try

strSQL = "INSERT INTO targettable
SELECT jTBL_ClassAssignment.ClassID,
DCount(""*"", ""jTBLClassAssignment"", ""ClassID = "" & intClassID &
"" AND AssignmentSortID <= "" & [AssignmentSortID]),
jTBL_ClassAssignment.AssignmentSortID FROM
jTBL_ClassAssignment WHERE jTBL_ClassAssignment.ClassID = " &
intclassid & " ORDER BY jTBL_ClassAssignment.AssignmentSortID;"
 
Hi John

couldn't get this to work - the table that i'm copying the records out of is
called ztbl_Assignment, the destination table is jtbl_ClassAssignment.
ztbl_Assignment contains all the fields i need except classid (which is
obtained from a combo box on the form) and assignnum - these two fields are
primary key fields.

i ended up modifying your code to look like this -
dbs.execute "INSERT INTO jtbl_ClassAssignment (ClassID, AssignNum,
Assignment, Marks, ShortName, AssignmentLevel, AssignmentSortID) " _
& "SELECT " & intclassid & ", DCount(""*"",
""zTBL_ClassAssignment"", ""ClassID = " & intclassid & " AND
AssignmentSortID <= "" & [AssignmentSortID]), " _
& "zTBL_Assignment.Assignment, zTBL_Assignment.Marks,
zTBL_Assignment.ShortName, zTBL_Assignment.AssignmentLevel,
zTBL_Assignment.AssignmentSortID " _
& "FROM zTBL_Assignment WHERE zTBL_Assignment.Unit = '" &
txtunit & "';"

but it still didn't work. I have found a solution (provided by
Alessandro) - so this is more for interest sake then necessity, but if you
could explain how the code can be further modified (or corrected) i would
appreciate it.

Regards
JulieD


John Vinson said:
i'm guess that i need to use a select query to drag out the records that
i've just appended (sorted in the order that i want) and then cycle through
the recordset updating each record in turn - it's this last bit i can't get.

the bit of code i need help with (i think) is:
strsql = "SELECT jTBL_ClassAssignment.ClassID,
jTBL_ClassAssignment.AssignNum, jTBL_ClassAssignment.AssignmentSortID FROM
jTBL_ClassAssignment WHERE jTBL_ClassAssignment.ClassID = " & intclassid & "
ORDER BY jTBL_ClassAssignment.AssignmentSortID;"
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)
rst.MoveFirst
i = 1
For Each ??????? in rst
rst("AssignNum") = i
Next
Click to expand...


You don't need any VBA code to do this at all, actually. You can use
domain functions to calculate the incremental assign number. Try

strSQL = "INSERT INTO targettable
SELECT jTBL_ClassAssignment.ClassID,
DCount(""*"", ""jTBLClassAssignment"", ""ClassID = "" & intClassID &
"" AND AssignmentSortID <= "" & [AssignmentSortID]),
jTBL_ClassAssignment.AssignmentSortID FROM
jTBL_ClassAssignment WHERE jTBL_ClassAssignment.ClassID = " &
intclassid & " ORDER BY jTBL_ClassAssignment.AssignmentSortID;"
Click to expand...
 
Alessandro,

You also need to increment i. Otherwise, the field
'AssignNum' in all records will equal 1.

'-----------------
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)

If rst.BOF and rst.EOF Then
MsgBox "No Records!!"
Else
rst.MoveFirst
i = 1
Do Until rst.eof
rst.edit
rst("AssignNum") = i
rst.Update
rst.movenext
i = i+1 '<<<<<<<<<<<<<<<<
Loop
End If
'----------------


Steve
 
SteveS said:
Alessandro,

You also need to increment i. Otherwise, the field
'AssignNum' in all records will equal 1.

'-----------------
Set rst = dbs.OpenRecordset(strsql, dbOpenDynaset)

If rst.BOF and rst.EOF Then
MsgBox "No Records!!"
Else
rst.MoveFirst
i = 1
Do Until rst.eof
rst.edit
rst("AssignNum") = i
rst.Update
rst.movenext
i = i+1 '<<<<<<<<<<<<<<<<
Loop
End If
'----------------


Steve
Click to expand...


I presume it, but is not mentioned in the original post, so
i don't write anythink about.....!
Many thanks for the right correction....!

Sorry about my English, i'm try to learn it...... !" ;-)

Alessandro(IT).
 
Back
Top