Recordset loop updating correctly but for one field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

pubdude2003 - 30 Jan 2007 14:50 GMT

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)

Reply to this Message

Tim Ferguson - 30 Jan 2007 17:01 GMT

> Do Until rs.EOF
>
> CurrentDb.Execute _

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

> " VALUES ( " & _
> "'" & Forms!form1!Received & "', " & _
[quoted text clipped - 4 lines]
>
> 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

Reply to this Message

pubdude2003 - 30 Jan 2007 17:19 GMT

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?

Reply to this Message

pubdude2003 - 30 Jan 2007 18:18 GMT

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)

Reply to this Message

Tim Ferguson - 31 Jan 2007 17:20 GMT

> 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

Reply to this Message