Copy Record - Is field autonumber

[Dale Fye]

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.

 

[Allen Browne]

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

 

[Dale Fye]

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]

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.