Variable and field value changes after .Update in VBA
Thanks for the reply, but no, I wrote it correctly. rs![ID] is an autonumber
field and I want to store the value that Access assigns the field when the
new record is created. That is so I can use it later to assign that value to
a field in another related table that needs to know the key value of the
record in the rs table.
I think that the issue boils down to "Why should Access VBA change the value
of a variable because the value of the item that was used to assign the
value of that variable changes?" Sort of like if X = 5 and Y = X then X=6 Y
should still equal 5. Unless I am missing something, this is not happening.
Thanks again,
Dom
Quote:
> It seems to me you have your expression backward. Shouldn't it be:
> rs![ID] = lngID
> After all, you are adding to the recordset. The way you wrote it, you are
> assigning lngID the value of rs![ID]. I don't know what that value would
> be...you haven't moved to the top of the rs, so it will be wherever the
> pointer is.
> John
> > I ran into a strange behavior in Access 2000 SP1. If the following code
> > example rs![ID] is an autonumber field:
> > Dim lngID as Long, rs as Recordset
> > .
> > .
> > .
> > rs.AddNew
> > lngID = rs![ID]
> > .
> > .
> > .
> > rs.Update
> > If when executed the value of lngID and rs![ID] is, say, 71 after the
> > .AddNew. But, after the .Update BOTH rs![ID] and lngID are a different
> > value, say, 64. This, of course, causes a problem when using lngID later
> as
> > part of a primary key in a different related table because it can cause
a
> > duplicate key error.
> > I don't know why rs![ID] should change with the .Update when I haven't
> moved
> > to a new record. But certainly as an independent variable, lngID, should
> not
> > be related to the value of rs![ID]. This all worked fine in Acces 97 as
I
> > expect it should work.
> > The only work around that I have found is to do a .MoveLast after the
> > .Update and reassign lngID to the value of rs![ID] so I can continue
> > processing using the correct ID number.
> > Thanks for any insight into this,
> > Dom Cassone
