Recordset loop updating correctly but for one field

[pubdude2003 via AccessMonster.com]

Hey there,

I have a form with a button with the following code attached to it. I want to
insert the values from the controls on that form to a separate table. (Form
is bound to another table).

It's working fine except for the Text3 field, it inserts the same value for
every record on the form. Weird or what?

If Not rs.EOF Then rs.MoveFirst
Do Until rs.EOF

CurrentDb.Execute "INSERT INTO tblDirectory2" & _
" ( FileDate, subject, justfile )" & _
" VALUES ( '" & Forms!form1!Received & "', '" & Forms!form1!
Subject & "', '" & Forms!form1!Text3 & "' )"
rs.MoveNext

Loop
rs.Close
Set rs = Nothing

The value of the Text3 field is parsed from a separate table, it's the only
thing that makes that field different from the rest. Any help would be
appreciated.

Text3 = Mid$([Contents],[text6]+42,5)

 

[Tim Ferguson]

Do Until rs.EOF

CurrentDb.Execute _

.. "INSERT INTO tblDirectory2" & " (FileDate, subject, justfile )" &
_

" VALUES ( " & _
"'" & Forms!form1!Received & "', " & _
"'" & Forms!form1!Subject & "', " & _
"'" & Forms!form1!Text3 & "'" & _
")", _
rs.MoveNext

Loop

The value of the Text3 field is parsed from a separate table,

I have to say that I don't see how _any_ of the values change: you should
be getting as many copies of the same data as there are rows in rs.
Unless there is a GoToRecord command hidden in there somewhere, the
controls on the form have no reason to change as this loop executes... I
would strongly recommend using the dbFailOnError argument for the
..Execute method because, as this procedure is written, you may be missing
Key Violation errors.


Best wishes


Tim F

 

[pubdude2003 via AccessMonster.com]

thanks Tim, wouldn't the rs.movenext game me to the next record in the form?

also the Text3 field is a parse of a memo field which (I think - just found
some notes on this site about it) maybe the nature of the problem as well.

is there a better way to write this?

 

[pubdude2003 via AccessMonster.com]

Problem solved, there are too many issues involved parsing the memo field
(and it's locked to me) so I just duped it and changed the value of the data
type for the Text3 field. Doing that allowed me to use rs values in the
Insert statement and problem solved.

Thanks again for the response. (and that loop was certainly suspect)

 

[Tim Ferguson]

thanks Tim, wouldn't the rs.movenext game me to the next record in the
form?

No: it moves the pointer of the recordset rs.

I am guessing that you have set rs to either thisForm.recordset or
thisForm.recordsetClone (thisForm is usually Me, but I didn't want to
make too many assumptions).

The way to change the record displayed on the form is either to use the
DoCmd.GoToRecord or Me.Bookmark. I just spotted this in the help file,
however:-

However, unlike using the RecordsetClone property, changing
which record is current in the recordset returned by the
form's Recordset property also sets the current record of
the form.

.... so I suppose what you are trying to do should work. I see that you
have already found another problem with the control's source expression.

Still, I am not sure that this is a good way to do it. It's not very good
manners to change a form's current record except as a direct result of
the user issuing a find or move request. I don't see what you are doing
here that couldn't be done faster and more discretely with in INSERT INTO
.... SELECT ... command, which is what SQL is for. Then again, I've just
demonstrated that I don't see very much about what you are trying to
achieve so perhaps I'd better just be quiet

All the best


Tim F