Trouble in getting PK(AutoNumber) value of a new record.

[zkyknight]

Hello,



I have a button that copies current record and paste them into a new record. I am trying to keep the relationship that these records have with other tables. For example, the copied record is of a policy and has a relationship with a location. I have the policy copied, but want to add the location and connect it to the new policy.

I was using the DoCmd commands to copy and paste the record and whenever I am trying to get the new Policy ID, it came back as a Long instead of an Integer. I need the Integer value so I can continue on with my SQL inserts for the location.

I have tried using Max(ID), DAO rs.movelast and both returns Long instead of the number.

Please help and thanks.

[orange]

Please tell us about copying the current record and pasting into a new record. And the SQL inserts. Perhaps you can give us an example or 2.
Long is a long integer. Autonumbers when referenced as foreign key use Long (integer).

[zkyknight]

Please tell us about copying the current record and pasting into a new record. And the SQL inserts. Perhaps you can give us an example or 2.
Long is a long integer. Autonumbers when referenced as foreign key use Long (integer).

It appears to be a corrupted textbox, but it still did not explain why the select query cannot find the record I'm looking for.

Sample Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste
DoCmd.RefreshRecord

sql = "select [ID] from [Policy Periods]"
Set rs4 = db.OpenRecordset(sql)
rs4.MoveLast

sql = "select [ID], [Location #] from [Locations] where [Policy ID] = " & fixSQLnumOrNull(rs4![ID])
Set rs3 = db.OpenRecordset(sql)

At rs3 recordset, it is says it is EOF, but Locations table has the record. Let me paint a bigger picture.

There is a Policy Form and the Location subForm. When this copies to a new record it copies records in the form and subform. But my rs3 recordset cannot find the record in the Location table even though I can see it in the table. rs4![ID] exists as I was testing this inside the textbox, which was found to be corrupted.

[orange]

Suggest you post a copy of the db showing only a few records highlighting the issue.
There is no inherent order to the records in a table.
Can you take some screen shots to show us what you are seeing?

[zkyknight]

Suggest you post a copy of the db showing only a few records highlighting the issue.
There is no inherent order to the records in a table.
Can you take some screen shots to show us what you are seeing?

This first picture shows the result of the code. I made it that if it is not EOF it will return the ID, but if it is EOF, it returns 1.
At the bottom, where it is green, the number there represent the ID from the Location Table, so it should exist. From my previous code, rs4![ID] returns the Policy ID which is the criteria use to find the Location ID.

The second picture is that of the Locations Table, which clearly shows that the record exist. However, I cannot get this Location ID, which I need to insert to another table.

[Gicu]

If you copy a policy record wouldn't the location ID would be the same as the one being copied? If you do this on a form can't you reference the control holding the location ID instead of trying to get it from the newly pasted record? And I would try dmax("[ID]","[Policy Periods]") if you want to get the latest ID (autonumber).

Cheers,
Vlad

[orange]

Be cautious with autonumbers. They are unique, but not necessarily in sequence nor positive.

[accesstos]

Open rs3 in this way:

sql = "select [ID], [Location #] from [Locations] where [Policy ID] = (select max([ID]) AS LastID from [Policy Periods])
Set rs3 = db.OpenRecordset(sql)