I'm working on a function that will copy a specific record. Anyone have a
function that will do this? Basically, I'd like to be able to pass a
tablename, the ID field, and ID value to this function, have it copy the
appropriate record, and return the new value. In addition, I would like to
have the option of passing a newID (FK) value so that if the ID field is
actually a FK, I could replace that value with the optional NewID argument.

My current concept, is to open a recordset with the current record. then
Clone that recordset, add a new record to the clone, and loop through the
fields, setting the field in the copy to the value of the original set. The
problem I'm having at the moment is trying to determine whether the current
field is an autonumber field, in which case, I would get the value rather
than putting it. I guess I could probably try setting the value, and if it
generates an error, get the value, but I was thinking there might be a more
elegant way to determine whether a field is an autonumber.

Would appreciate any ideas or code samples.
--

Dale

email address is invalid
Please reply to newsgroup only.

To identify the AutoNumber, examine the Attributes of the Field in the
Recordset. Test if:
(rs.Fields(i).Attributes And dbAutoIncrField) = 0&

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dale Fye" <(E-Mail Removed)> wrote in message
news:CCAFD1AD-683C-4FEE-BFA5-(E-Mail Removed)...
> I'm working on a function that will copy a specific record. Anyone have a
> function that will do this? Basically, I'd like to be able to pass a
> tablename, the ID field, and ID value to this function, have it copy the
> appropriate record, and return the new value. In addition, I would like
> to
> have the option of passing a newID (FK) value so that if the ID field is
> actually a FK, I could replace that value with the optional NewID
> argument.
>
> My current concept, is to open a recordset with the current record. then
> Clone that recordset, add a new record to the clone, and loop through the
> fields, setting the field in the copy to the value of the original set.
> The
> problem I'm having at the moment is trying to determine whether the
> current
> field is an autonumber field, in which case, I would get the value rather
> than putting it. I guess I could probably try setting the value, and if
> it
> generates an error, get the value, but I was thinking there might be a
> more
> elegant way to determine whether a field is an autonumber.
>
> Would appreciate any ideas or code samples.

Thanks Allen. That was precisely what I was looking for. What I ended up
with is:

rsCopy.AddNew
For Each fld In rs.Fields
If (fld.Attributes And dbAutoIncrField) = dbAutoIncrField Then
varNewIDValue = rsCopy(fld.Name)
ElseIf fld.Name = IDField And (IsNull(ReplaceValue) = False) Then
rsCopy(fld.Name) = ReplaceValue
Else
rsCopy(fld.Name) = rs(fld.Name)
End If
Next
rsCopy.Update

This works great when I'm trying to copy a record where the ID is the PK.
The first time I did this, I had it inside an if statement:

IF not rs.eof then
....
Endif

Then realized that I had a bunch of one-to-many tables that needed to have
multiple records copied, so I changed the If Then to a While not rs.eof ...
Wend. Only to find out that as records were inserted into rsCopy, they were
also showing up in the rs recordset (infinite loop). So I had to create a
separate routine to copy the records in the subrodinate tables. This routine
just creates a SQL "INSERT INTO " statement and executes that.

Thanks for your help.

Any chance you had a chance to look at the Spell check issue I sent you an
email about?
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"Allen Browne" wrote:

> To identify the AutoNumber, examine the Attributes of the Field in the
> Recordset. Test if:
> (rs.Fields(i).Attributes And dbAutoIncrField) = 0&
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dale Fye" <(E-Mail Removed)> wrote in message
> news:CCAFD1AD-683C-4FEE-BFA5-(E-Mail Removed)...
> > I'm working on a function that will copy a specific record. Anyone have a
> > function that will do this? Basically, I'd like to be able to pass a
> > tablename, the ID field, and ID value to this function, have it copy the
> > appropriate record, and return the new value. In addition, I would like
> > to
> > have the option of passing a newID (FK) value so that if the ID field is
> > actually a FK, I could replace that value with the optional NewID
> > argument.
> >
> > My current concept, is to open a recordset with the current record. then
> > Clone that recordset, add a new record to the clone, and loop through the
> > fields, setting the field in the copy to the value of the original set.
> > The
> > problem I'm having at the moment is trying to determine whether the
> > current
> > field is an autonumber field, in which case, I would get the value rather
> > than putting it. I guess I could probably try setting the value, and if
> > it
> > generates an error, get the value, but I was thinking there might be a
> > more
> > elegant way to determine whether a field is an autonumber.
> >
> > Would appreciate any ideas or code samples.
>
>

"Dale Fye" <(E-Mail Removed)> wrote in message
news:1F601A61-82A9-4F7F-AA27-(E-Mail Removed)...
> Any chance you had a chance to look at the Spell check issue I sent
> you an email about?

I did receive that, but it might be another couple of weeks before I can get
to it, Dale.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.