Add record looping problem ?

[David]

Hi,

I have a continuous form based on a query.
Lets say the form displays 6 records.
I also have a button against each record which sets a field to Y or N
for each record.

I am trying to copy partial fields only for those records where the
field = Y

My code as follows:

_________________________________
'recordset 1 'rst' is the recordset for the new table
'recordset 2 'rst2' is for the recordset underlying the open form

Dim dbs As Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ProductPackREC", dbOpenDynaset)
Set rst2 = Me.RecordsetClone

rst2.MoveFirst
Do While Not rst2.EOF
Me.Text32.SetFocus
If Me.Text32 = "Y" Then

rst.AddNew
rst!packitemNAME = Me!packitemNAME
rst!packitemDESC = Me!packitemDESC
rst!packitemQTY = Me!packitemQTY
rst!packitemCODE = Me!packitemCODE
rst!JobNumber = Me!Text41
rst.Update

Else

End If
rst2.MoveNext

Loop

Set rst = Nothing
Set db = Nothing

______________________________________

What this does is take the first record marked 'Y' and saves the data
correctly, BUT....it records the same record equal to the number of
records on the form !

i.e. if the form displayed
record 1 showed: Blue Y
record 2 showed: Red
record 3 showed: Green Y
record 4 showed: Purple

The new table now shows Blue x 4
If I remove the loop for rst2, then it records the first record marked
with Y only the once, which is correct. What it should do is run
through the forms recordset starting at the first. Check for Y, if
yes, record, if no, movenext.

The new table should now hold :

record 1 : Blue
record 3 : Green

What am I doing wrong, this is driving me crazy. I can only get
multiple copies of the same record recording or 1 copy, but not all
marked Y ?

Appreciate your help.
David

[Brian]

"David" <da***@scene-double.co.uk> wrote in message
news:c1**************************@posting.google.c om...

Hi,

I have a continuous form based on a query.
Lets say the form displays 6 records.
I also have a button against each record which sets a field to Y or N
for each record.

I am trying to copy partial fields only for those records where the
field = Y

My code as follows:

_________________________________
'recordset 1 'rst' is the recordset for the new table
'recordset 2 'rst2' is for the recordset underlying the open form

Dim dbs As Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("ProductPackREC", dbOpenDynaset)
Set rst2 = Me.RecordsetClone

rst2.MoveFirst
Do While Not rst2.EOF
Me.Text32.SetFocus
If Me.Text32 = "Y" Then

rst.AddNew
rst!packitemNAME = Me!packitemNAME
rst!packitemDESC = Me!packitemDESC
rst!packitemQTY = Me!packitemQTY
rst!packitemCODE = Me!packitemCODE
rst!JobNumber = Me!Text41
rst.Update

Else

End If
rst2.MoveNext

Loop

Set rst = Nothing
Set db = Nothing

______________________________________

What this does is take the first record marked 'Y' and saves the data
correctly, BUT....it records the same record equal to the number of
records on the form !

i.e. if the form displayed
record 1 showed: Blue Y
record 2 showed: Red
record 3 showed: Green Y
record 4 showed: Purple

The new table now shows Blue x 4
If I remove the loop for rst2, then it records the first record marked
with Y only the once, which is correct. What it should do is run
through the forms recordset starting at the first. Check for Y, if
yes, record, if no, movenext.

The new table should now hold :

record 1 : Blue
record 3 : Green

What am I doing wrong, this is driving me crazy. I can only get
multiple copies of the same record recording or 1 copy, but not all
marked Y ?

Appreciate your help.
David

By using "Me" you are referring to the form's current record, when what you
intended to do was to refer to the current record in rst2. So, for example,
instead of Me!packitemNAME, you should use rst2!packitemNAME