Access 2003 - I have the following code that duplicates a record using the
copy/paste menu commands. The new record has an autonumber key field,
however, I cannot seem to access it with this code:

DoCmd.OpenForm stDocName, , , , acFormAdd, , False
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
MsgBox "me.IDnumber = " & Me.IDNumber
varNewID = Me.IDNumber

I get an error message saying, "The expression you entered refers to an
object that is closed or doesn't exist."

The new automatically generated IDNumber DOES appear on the form, but the
error message suggests it hasn't been saved to the table yet (I guess).
Therefore, I tried entering a save record command and a refresh screen
command, but that didn't work. Is there a way to access that new ID value
without exiting the screen?

Thanks!

Try referencing the control that holds the autonumber value:

MsgBox "me.IDnumber = " & Me!NameOfControlHoldingIDNumberValue

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/



"Dorci" <(E-Mail Removed)> wrote in message
news:1D0EC7A8-4BB1-469C-A6E6-(E-Mail Removed)...
> Access 2003 - I have the following code that duplicates a record using the
> copy/paste menu commands. The new record has an autonumber key field,
> however, I cannot seem to access it with this code:
>
> DoCmd.OpenForm stDocName, , , , acFormAdd, , False
> DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
> MsgBox "me.IDnumber = " & Me.IDNumber
> varNewID = Me.IDNumber
>
> I get an error message saying, "The expression you entered refers to an
> object that is closed or doesn't exist."
>
> The new automatically generated IDNumber DOES appear on the form, but the
> error message suggests it hasn't been saved to the table yet (I guess).
> Therefore, I tried entering a save record command and a refresh screen
> command, but that didn't work. Is there a way to access that new ID value
> without exiting the screen?
>
> Thanks!

I have a function I use to copy records. Since I use a autonumber ID field
in almost every table I create, this makes this relatively easy. However, if
your table has any Unique constraints, this will obviously not work.

Public Function fnCopyRecord(TableName As String, _
IDField As String, _
IDValue As Long) As Long

Dim rs As DAO.Recordset, rsNew As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer

strSQL = "SELECT * FROM [" & TableName & "] " _
& "WHERE [" & IDField & "] = " & IDValue
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

strSQL = "SELECT * FROM [" & TableName & "] " _
& "WHERE FALSE"
Set rsNew = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

rsNew.AddNew
For intLoop = 0 To rs.Fields.Count - 1

If rs.Fields(intLoop).Name <> IDField Then
rsNew(intLoop) = rs(intLoop)
End If

Next

fnCopyRecord = rsNew(IDField)

rsNew.Update
rsNew.Close
Set rsNew = Nothing
rs.Close
Set rs = Nothing

End Function

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Dorci" wrote:

> Access 2003 - I have the following code that duplicates a record using the
> copy/paste menu commands. The new record has an autonumber key field,
> however, I cannot seem to access it with this code:
>
> DoCmd.OpenForm stDocName, , , , acFormAdd, , False
> DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
> MsgBox "me.IDnumber = " & Me.IDNumber
> varNewID = Me.IDNumber
>
> I get an error message saying, "The expression you entered refers to an
> object that is closed or doesn't exist."
>
> The new automatically generated IDNumber DOES appear on the form, but the
> error message suggests it hasn't been saved to the table yet (I guess).
> Therefore, I tried entering a save record command and a refresh screen
> command, but that didn't work. Is there a way to access that new ID value
> without exiting the screen?
>
> Thanks!

Unfortunately IDNumber is a unique primary key. Thanks anyway.

"Dale Fye" wrote:

> I have a function I use to copy records. Since I use a autonumber ID field
> in almost every table I create, this makes this relatively easy. However, if
> your table has any Unique constraints, this will obviously not work.
>
> Public Function fnCopyRecord(TableName As String, _
> IDField As String, _
> IDValue As Long) As Long
>
> Dim rs As DAO.Recordset, rsNew As DAO.Recordset
> Dim strSQL As String
> Dim intLoop As Integer
>
> strSQL = "SELECT * FROM [" & TableName & "] " _
> & "WHERE [" & IDField & "] = " & IDValue
> Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
>
> strSQL = "SELECT * FROM [" & TableName & "] " _
> & "WHERE FALSE"
> Set rsNew = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
>
> rsNew.AddNew
> For intLoop = 0 To rs.Fields.Count - 1
>
> If rs.Fields(intLoop).Name <> IDField Then
> rsNew(intLoop) = rs(intLoop)
> End If
>
> Next
>
> fnCopyRecord = rsNew(IDField)
>
> rsNew.Update
> rsNew.Close
> Set rsNew = Nothing
> rs.Close
> Set rs = Nothing
>
> End Function
>
> --
> HTH
> Dale
>
> email address is invalid
> Please reply to newsgroup only.
>
>
>
> "Dorci" wrote:
>
> > Access 2003 - I have the following code that duplicates a record using the
> > copy/paste menu commands. The new record has an autonumber key field,
> > however, I cannot seem to access it with this code:
> >
> > DoCmd.OpenForm stDocName, , , , acFormAdd, , False
> > DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
> > MsgBox "me.IDnumber = " & Me.IDNumber
> > varNewID = Me.IDNumber
> >
> > I get an error message saying, "The expression you entered refers to an
> > object that is closed or doesn't exist."
> >
> > The new automatically generated IDNumber DOES appear on the form, but the
> > error message suggests it hasn't been saved to the table yet (I guess).
> > Therefore, I tried entering a save record command and a refresh screen
> > command, but that didn't work. Is there a way to access that new ID value
> > without exiting the screen?
> >
> > Thanks!

IDNumber is the name of the control that holds the autonumber value. It is
the record's primary key.

"Ken Snell (MVP)" wrote:

> Try referencing the control that holds the autonumber value:
>
> MsgBox "me.IDnumber = " & Me!NameOfControlHoldingIDNumberValue
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
> http://www.accessmvp.com/KDSnell/
>
>
>
> "Dorci" <(E-Mail Removed)> wrote in message
> news:1D0EC7A8-4BB1-469C-A6E6-(E-Mail Removed)...
> > Access 2003 - I have the following code that duplicates a record using the
> > copy/paste menu commands. The new record has an autonumber key field,
> > however, I cannot seem to access it with this code:
> >
> > DoCmd.OpenForm stDocName, , , , acFormAdd, , False
> > DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
> > MsgBox "me.IDnumber = " & Me.IDNumber
> > varNewID = Me.IDNumber
> >
> > I get an error message saying, "The expression you entered refers to an
> > object that is closed or doesn't exist."
> >
> > The new automatically generated IDNumber DOES appear on the form, but the
> > error message suggests it hasn't been saved to the table yet (I guess).
> > Therefore, I tried entering a save record command and a refresh screen
> > command, but that didn't work. Is there a way to access that new ID value
> > without exiting the screen?
> >
> > Thanks!
>
>
>

I assumed that. I should have been more explicit. What I meant was that
there can only be one Unique index (your IDNumber) field.

You would call this something like:

varNewID = fnCopyRecord("TableName", "IDNumber", me.IdNumber)

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Dorci" wrote:

> Unfortunately IDNumber is a unique primary key. Thanks anyway.
>
> "Dale Fye" wrote:
>
> > I have a function I use to copy records. Since I use a autonumber ID field
> > in almost every table I create, this makes this relatively easy. However, if
> > your table has any Unique constraints, this will obviously not work.
> >
> > Public Function fnCopyRecord(TableName As String, _
> > IDField As String, _
> > IDValue As Long) As Long
> >
> > Dim rs As DAO.Recordset, rsNew As DAO.Recordset
> > Dim strSQL As String
> > Dim intLoop As Integer
> >
> > strSQL = "SELECT * FROM [" & TableName & "] " _
> > & "WHERE [" & IDField & "] = " & IDValue
> > Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
> >
> > strSQL = "SELECT * FROM [" & TableName & "] " _
> > & "WHERE FALSE"
> > Set rsNew = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
> >
> > rsNew.AddNew
> > For intLoop = 0 To rs.Fields.Count - 1
> >
> > If rs.Fields(intLoop).Name <> IDField Then
> > rsNew(intLoop) = rs(intLoop)
> > End If
> >
> > Next
> >
> > fnCopyRecord = rsNew(IDField)
> >
> > rsNew.Update
> > rsNew.Close
> > Set rsNew = Nothing
> > rs.Close
> > Set rs = Nothing
> >
> > End Function
> >
> > --
> > HTH
> > Dale
> >
> > email address is invalid
> > Please reply to newsgroup only.
> >
> >
> >
> > "Dorci" wrote:
> >
> > > Access 2003 - I have the following code that duplicates a record using the
> > > copy/paste menu commands. The new record has an autonumber key field,
> > > however, I cannot seem to access it with this code:
> > >
> > > DoCmd.OpenForm stDocName, , , , acFormAdd, , False
> > > DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
> > > MsgBox "me.IDnumber = " & Me.IDNumber
> > > varNewID = Me.IDNumber
> > >
> > > I get an error message saying, "The expression you entered refers to an
> > > object that is closed or doesn't exist."
> > >
> > > The new automatically generated IDNumber DOES appear on the form, but the
> > > error message suggests it hasn't been saved to the table yet (I guess).
> > > Therefore, I tried entering a save record command and a refresh screen
> > > command, but that didn't work. Is there a way to access that new ID value
> > > without exiting the screen?
> > >
> > > Thanks!

Thanks Linq. I've gotten that far. The problem is copying the CHILD records
and having them linked to the new parent record. Even though I can see that
the new IDNumber on the screen is, say "112", I can't get Access to assign
that number to the IDNumber field in the linked table. So far, all I've come
up with is telling Access to "find" the last IDNumber in the parent table.
This is unsafe in the event that another user just created a new record while
this code is creating the copy.

"Linq Adams via AccessMonster.com" wrote:

> This is kind of klutsy, especially with a lot of fields, but it works:
>
> Private Sub CopyPartialRecord2NewRecordButton_Click()
>
> ‘Assign field values to be carried forward to variables
> MyFirstField = Me.FirstField
> MySecondField = Me.SecondField
> MyThirdField = Me.ThirdField
>
> 'Go to a new record
> DoCmd.GoToRecord , , acNewRec
>
> 'Plug in old values from variables to new record
> Me.FirstField = MyFirstField
> Me.SecondField = MySecondField
> Me.ThirdField = MyThirdField
>
> End Sub
>
> With this code you just have to be sure that any field included in the copy
> operation is a field that ***does*** allow duplicates.
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200902/1
>
>

My method is a lot more efficient.

If you want to go to the new record when you are done, try adding a couple
of lines to the code that calls the function.

Private Sub cmd_CopyRecord_Click

Dim rs as dao.recordset
Dim varNewID as variant
varNewID = fnCopyRecord("TableName", "IDNumber", me.IDNumber)
set rs = me.recordsetclone
rs.findfirst "[IDNumber] = " & varNewID
if rs.Nomatch then
msgbox "record not found"
else
me.bookmark = rs.bookmark
endif
rs.close
set rs = nothing
end sub

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Dorci" wrote:

> Thanks Linq. I've gotten that far. The problem is copying the CHILD records
> and having them linked to the new parent record. Even though I can see that
> the new IDNumber on the screen is, say "112", I can't get Access to assign
> that number to the IDNumber field in the linked table. So far, all I've come
> up with is telling Access to "find" the last IDNumber in the parent table.
> This is unsafe in the event that another user just created a new record while
> this code is creating the copy.
>
> "Linq Adams via AccessMonster.com" wrote:
>
> > This is kind of klutsy, especially with a lot of fields, but it works:
> >
> > Private Sub CopyPartialRecord2NewRecordButton_Click()
> >
> > ‘Assign field values to be carried forward to variables
> > MyFirstField = Me.FirstField
> > MySecondField = Me.SecondField
> > MyThirdField = Me.ThirdField
> >
> > 'Go to a new record
> > DoCmd.GoToRecord , , acNewRec
> >
> > 'Plug in old values from variables to new record
> > Me.FirstField = MyFirstField
> > Me.SecondField = MySecondField
> > Me.ThirdField = MyThirdField
> >
> > End Sub
> >
> > With this code you just have to be sure that any field included in the copy
> > operation is a field that ***does*** allow duplicates.
> >
> > --
> > There's ALWAYS more than one way to skin a cat!
> >
> > Answers/posts based on Access 2000/2003
> >
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/For...dules/200902/1
> >
> >

Linq,

You are absolutely right.

Since the OPs first 3 posts failed to say anything about a "child" record or
table, I failed to pickup on that last post. But my solution does give the
ID number that he/she is looking for, without having to declare a separate
variable for each control on the form, and then set that variable for each
field in the current record, and then go to a new record and reverse that
process, and (oh wait, that method doesn't account for "child" records
either).

So all the OP needs to do is write a parameter query that copies records in
her "child" table, based on the parameter, something like:

Parameters [NewID] long, [OldID] long;
INSERT INTO tblChildTable (IDNumber, Field2, Field3, Field4)
SELECT [NewID], Field2, Field3, Field4
FROM tblChildTable
WHERE [IDNumber] = [OldID]

Once saved, all the OP has to do is:

Dim qdf as dao.querydef
set qdf = currentdb.querydefs("queryName")
qdf.parameters(0) = varNewID
qdf.parameters(1) = me.IDNumber
qdf.execute

Once this is complete, then add the code that takes the user to the new
record (from my last post).

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



"Linq Adams via AccessMonster.com" wrote:

> "The problem is copying the CHILD records and having them linked to the new
> parent record. "
>
> Unless there is a post in this thread that isn't visible to me, you have
> never mentioned trying to copy CHILD records! You really need to post little
> details like that when you originate a thread!
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/For...dules/200902/1
>
>

Sorry I didn't mention the child table. At the time, that was not what I was
having a problem with.

Dale,
I did try your code, and it worked perfectly. Too perfectly, in fact,
because it copied the entire parent record when I need to omit some fields,
like "Date Shipped", which the user must enter manually. I tried changing
the "select * from" to select specific field names, and it just made a mess.
I'm now trying to get it back to where it at least copied everything. I'll
keep you "posted"

"Dale Fye" wrote:

> Linq,
>
> You are absolutely right.
>
> Since the OPs first 3 posts failed to say anything about a "child" record or
> table, I failed to pickup on that last post. But my solution does give the
> ID number that he/she is looking for, without having to declare a separate
> variable for each control on the form, and then set that variable for each
> field in the current record, and then go to a new record and reverse that
> process, and (oh wait, that method doesn't account for "child" records
> either).
>
> So all the OP needs to do is write a parameter query that copies records in
> her "child" table, based on the parameter, something like:
>
> Parameters [NewID] long, [OldID] long;
> INSERT INTO tblChildTable (IDNumber, Field2, Field3, Field4)
> SELECT [NewID], Field2, Field3, Field4
> FROM tblChildTable
> WHERE [IDNumber] = [OldID]
>
> Once saved, all the OP has to do is:
>
> Dim qdf as dao.querydef
> set qdf = currentdb.querydefs("queryName")
> qdf.parameters(0) = varNewID
> qdf.parameters(1) = me.IDNumber
> qdf.execute
>
> Once this is complete, then add the code that takes the user to the new
> record (from my last post).
>
> --
> HTH
> Dale
>
> email address is invalid
> Please reply to newsgroup only.
>
>
>
> "Linq Adams via AccessMonster.com" wrote:
>
> > "The problem is copying the CHILD records and having them linked to the new
> > parent record. "
> >
> > Unless there is a post in this thread that isn't visible to me, you have
> > never mentioned trying to copy CHILD records! You really need to post little
> > details like that when you originate a thread!
> >
> > --
> > There's ALWAYS more than one way to skin a cat!
> >
> > Answers/posts based on Access 2000/2003
> >
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/For...dules/200902/1
> >
> >