Cannot refer to autonum field in new record

[Dorci]

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!

 

[Ken Snell \(MVP\)]

Try referencing the control that holds the autonumber value:

MsgBox "me.IDnumber = " & Me!NameOfControlHoldingIDNumberValue

 

[Dale Fye]

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]

Unfortunately IDNumber is a unique primary key. Thanks anyway.

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.

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!

 

[Dorci]

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

 

[Dale Fye]

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.

Unfortunately IDNumber is a unique primary key. Thanks anyway.

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.

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!

 

[Dorci]

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.

 

[Dale Fye]

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.

 

[Dale Fye]

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.

 

[Dorci]

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"

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.

"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

 

[Dale Fye]

Dorci,

The way to avoid copying specific fields would be to do this:

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

Select Case rs.Fields(intLoop).Name
Case IDField, "Date Shipped", "Other field"
'do nothing
Case Else
rsNew(intLoop) = rs(intLoop)
End Select

Next

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

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"

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.

"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

 

[Dorci]

Dale, it turns out that it wasn't your code that worked, but a combination of
your code and several other people's posts. In trying to get back to that
version, I've rendered the entire database inoperable; now even the "print"
and "exit" commands give an error message. Thankfully I have a backup, but
I'm back at square 1. I'm going to step back from this for now and try
piecing it back together in a couple of days.

Thanks so much for your patience and all of your efforts. You've been a
great help.

Dorci,

The way to avoid copying specific fields would be to do this:

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

Select Case rs.Fields(intLoop).Name
Case IDField, "Date Shipped", "Other field"
'do nothing
Case Else
rsNew(intLoop) = rs(intLoop)
End Select

Next

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

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"

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.



:

"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

 

[Dale Fye]

Aren't backups wonderful?

I start every day by making a backup of the app I'm going to work on that day.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

Dale, it turns out that it wasn't your code that worked, but a combination of
your code and several other people's posts. In trying to get back to that
version, I've rendered the entire database inoperable; now even the "print"
and "exit" commands give an error message. Thankfully I have a backup, but
I'm back at square 1. I'm going to step back from this for now and try
piecing it back together in a couple of days.

Thanks so much for your patience and all of your efforts. You've been a
great help.

Dorci,

The way to avoid copying specific fields would be to do this:

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

Select Case rs.Fields(intLoop).Name
Case IDField, "Date Shipped", "Other field"
'do nothing
Case Else
rsNew(intLoop) = rs(intLoop)
End Select

Next

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.

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"

:

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.



:

"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