Solved Copy the Primary Key [ID] and Place It In a Different Control in Same Form (1 Viewer)

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
I have a Main Form CAD_CallEntrySplitF which is bound to table ActivityT (which has ID as its primary key). There is another field in the same table called [ID_Activity]. I cannot rely just on the ID alone; I need the ID_Activity number archival purposes and related tables. I put

If IsNull ([ID_Activity]) Then
[ID_Activity] = [ID]
End If

In the BeforeEvent on the main form but it does nothing. I am assuming it is because the ID has not yet gotten its value until it is saved.

What should the syntax be and where should it go?

I need the value to show in the ID_Activity control on the form as soon as the event fires. Here are my current events on the form and some of its controls. Any help is appreciated.

Mark

Snap 2022-08-13 at 15.16.09.png
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
Why create a redundant field? Its both bad database practice & unnecessary work
If ID_Activity =ID then you don't need both for archival purposes or for related tables
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
All of my tables are linked by a field that is not the primary key ID. The reason for this is because in the past when we relied totally on the primary key ID for all linking, when we once had to migrate from one server to another, new autonumber ID's were set by the server. This messed up all of the relationships between tables. I decided then to have a unique number, other than the server issued ID, to preserve the relationships. If we ever had to migrate to another server for any reason, the relationships would not be effected. Maybe my logic is flawed, but I wanted to error on the side of caution to ensure preservation of the relationships.
 

isladogs

MVP / VIP
Local time
Today, 08:59
Joined
Jan 14, 2017
Messages
18,186
Sorry but your logic is flawed
Use your ID PK as a foreign key in your other related tables.
Suggest you rename that as e.g. ID_Activity.
Problem solved
 

June7

AWF VIP
Local time
Today, 00:59
Joined
Mar 9, 2014
Messages
5,425
Exactly what do you mean by 'server', why would a server create data? Why would migrating to another server mess up relationships? My office changed server hardware twice and that did not impact database relationships.
 

plog

Banishment Pending
Local time
Today, 03:59
Joined
May 11, 2011
Messages
11,613
Not to play captain hindsight, but a little planning and a little post-migration work could have made moving your database a virtually painless experience.

You should have created [OldID] fields in every table of the old database, duplicated the [ID] values into [OldID], moved the data, then updated all your foreign keys to the correct new ID values in the new server.

Now you're just doing more work because of the past pain you experienced. I'd just do it the right way and not waste time on a low probability event that is easily rectified.
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
In our case it was Sharepoint. We moved all lists from one Sharepoint account to another Sharepoint account. It created all new primary key IDs for each record and none of them matched their previous relationship.
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
Not to play captain hindsight, but a little planning and a little post-migration work could have made moving your database a virtually painless experience.

You should have created [OldID] fields in every table of the old database, duplicated the [ID] values into [OldID], moved the data, then updated all your foreign keys to the correct new ID values in the new server.

Now you're just doing more work because of the past pain you experienced. I'd just do it the right way and not waste time on a low probability event that is easily rectified.
Thanks plog and I'm sure you are probably right, but it seems like the IDs that Sharepoint assigns each record are not easily manipulated. At least with my knowledge level, I would not be comfortable with such a migration. The ID_Activity =ID is basically making the OldID as you suggested, but I'm more or less just doing it before it might ever be necessary. I am sure "updating" the ID_Activity field with the value that is in the ID field of the same record should be an easy thing to do, but at my VBA level, I don't know how to.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
I agree with the others. You should not be using anything other than the PK to establish relationships. This is a conversion issue that I have dealt with dozens of times over the years. As @plog suggested, you can resolve this problem by using a temporary field while doing the conversion.

When you append the "parent" table to the new database, you copy the old ID to the temp field and allow the database engine to generate a new autonumber. Then when you append the child table, you join its current FK to the new table on the oldID for the select part. For the append part, you pick up the new ID and use that as the FK.

This is really not hard once you understand the concept. I don't use SharePoint but if the BE were SQL Server, you could simply append the rows using the existing autonumbers. This is the ONE and ONLY ONE situation where you get to specify a value for the autonumber. You might have to turn the feature on for each table as you run each append query. With Jet/ACE, you don't need to do anything except include the autonumber in the select and apend parts of the query. Using the method I suggested above, you are omitting the autonumber in the append part of the query and are pointing the autonumber to the temp field and allowing the engine to generate a new ID.

The issue you are running into is that when the Access FE is linked to Jet/ACE tables, the new autonumber is generated immediately as soon as the new record is dirtied. You will see it happen on your form if the form shows the autonumber. But, when you are using an ODBC BE, the autonumber cannot be generated untill the insert request gets to the server. After the row is inserted, the database engine passes back the generated ID and you will only see it on the form AFTER the record has been saved. That is why your code in the BeforeUpdate event cannot see the autonumber.
 

GPGeorge

Grover Park George
Local time
Today, 01:59
Joined
Nov 25, 2004
Messages
1,776
In our case it was Sharepoint. We moved all lists from one Sharepoint account to another Sharepoint account. It created all new primary key IDs for each record and none of them matched their previous relationship.
Ah, SharePoint. Well, SharePoint is NOT a "database", SharePoint DOES do that, and it's a huge hassle. See this video for some insights into migration to SP.

I also blogged about it in 2011 AND here in 2012 Andagain here

However, no actual server based database wreaks that kind of havoc on Primary Keys. You're safe adopting the standard approach unless you intend to use SharePoint again.
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
I agree with the others. You should not be using anything other than the PK to establish relationships. This is a conversion issue that I have dealt with dozens of times over the years. As @plog suggested, you can resolve this problem by using a temporary field while doing the conversion.

When you append the "parent" table to the new database, you copy the old ID to the temp field and allow the database engine to generate a new autonumber. Then when you append the child table, you join its current FK to the new table on the oldID for the select part. For the append part, you pick up the new ID and use that as the FK.

This is really not hard once you understand the concept. I don't use SharePoint but if the BE were SQL Server, you could simply append the rows using the existing autonumbers. This is the ONE and ONLY ONE situation where you get to specify a value for the autonumber. You might have to turn the feature on for each table as you run each append query. With Jet/ACE, you don't need to do anything except include the autonumber in the select and apend parts of the query. Using the method I suggested above, you are omitting the autonumber in the append part of the query and are pointing the autonumber to the temp field and allowing the engine to generate a new ID.

The issue you are running into is that when the Access FE is linked to Jet/ACE tables, the new autonumber is generated immediately as soon as the new record is dirtied. You will see it happen on your form if the form shows the autonumber. But, when you are using an ODBC BE, the autonumber cannot be generated untill the insert request gets to the server. After the row is inserted, the database engine passes back the generated ID and you will only see it on the form AFTER the record has been saved. That is why your code in the BeforeUpdate event cannot see the autonumber.
All tables in this huge database is linked on a number field that is unique. This is a structure that was established way before my time so I don't really want to change in mid stream, even though I know the recommendations to do otherwise are correct.

To that end, is there code that will do what I am needing it to do.... And again that is to place the value that is in the ID field into the ID_Activity field on the form?
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
Ah, SharePoint. Well, SharePoint is NOT a "database", SharePoint DOES do that, and it's a huge hassle. See this video for some insights into migration to SP.

I also blogged about it in 2011 AND here in 2012 Andagain here

However, no actual server based database wreaks that kind of havoc on Primary Keys. You're safe adopting the standard approach unless you intend to use SharePoint again.
The Clients prefer Sharepoint so that is what I am having to use as the "container" of the data. That is the reason that I want my own unique field for linking tables.
 

June7

AWF VIP
Local time
Today, 00:59
Joined
Mar 9, 2014
Messages
5,425
Can't you define non-autonumber field as primary key in parent table? You can still have autonumber field in table but ignore it. I have designed dbs that do not use autonumber as primary key field. I use a vba generated unique identifier. DMax() is often used to increment a non-autonumber field.

The code you attempted should work once the ID is generated. So exactly when does that happen? I don't know how SharePoint impacts this but in normal Access, the autonumber is available in the BeforeUpdate event of any control that is edited on a new record. I just tested.
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
Can't you define non-autonumber field as primary key in parent table? You can still have autonumber field in table but ignore it. I have designed dbs that do not use autonumber as primary key field. I use a vba generated unique identifier. DMax() is often used to increment a non-autonumber field.

The code you attempted should work once the ID is generated. So exactly when does that happen? I don't know how SharePoint impacts this but in normal Access, the autonumber is available in the BeforeUpdate event of any control that is edited on a new record. I just tested.
If I was going to go with that rationale, I could do a DMax() +1 on the ID_Activity field and not have to involve the Sharepoint generated ID at all. I will basically be ignoring the Sharepoint generated ID. What is the syntax for the DMax and where would I put it? And I would only want it to execute if the field is Null.
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
If I was going to go with that rationale, I could do a DMax() +1 on the ID_Activity field and not have to involve the Sharepoint generated ID at all. I will basically be ignoring the Sharepoint generated ID. What is the syntax for the DMax and where would I put it? And I would only want it to execute if the field is Null.
Disregard last.... I found the syntax. Thanks for the suggestion.
 

June7

AWF VIP
Local time
Today, 00:59
Joined
Mar 9, 2014
Messages
5,425
Yes, I did say 'ignore' the autonumber.

One issue to be dealt with is that of multiple simultaneous users generating the same value and causing conflict when trying to save. In my db the identifier is generated and record immediately saved to reduce opportunity for conflict. Since there are only about 10 users, the chances of conflict are quite slim. So there's the advantage of using autonumber field as source for the key.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
@GPGeorge , are you saying that my suggestion on how to link the child to the parent won't work, aside from potential table size issues?
 

regnewby2465

Member
Local time
Today, 04:59
Joined
Sep 22, 2015
Messages
58
If there is no simple way through VBA code to copy the Sharepoint generated ID to the ID_Activity, then I will just use the DMax and hope that now two people hit the save button at the same time.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:59
Joined
Feb 19, 2002
Messages
42,981
To minimize the risk of generating a duplicate, put the DMax() as the LAST statement in the Form's BeforeUpdate event. If your users are savvy, they can probably deal with getting a duplicate error. If not, you need to trap the duplicate error in the on Error event and tell them what to do. Trying to save again is probably OK as an instruction to them.

If there is no simple way through VBA code to copy the Sharepoint generated ID to the ID_Activity
I explained why you can't do this in an earlier post. You also couldn't do it if the BE were SQL Server. You can ONLY do it if the BE is Jet or ACE.

I'm confused regarding the actual problem now. I thought this was a FK issue. Why can't you see the ID of the parent record? You wouldn't need to see the ID except when you are attempting to insert a child record and need the parent's PK to use as a FK.

Did your conversion work OK? If you already had these separate but equal keys, did they get broken during the conversion?
 

June7

AWF VIP
Local time
Today, 00:59
Joined
Mar 9, 2014
Messages
5,425
Do users really need to see ID_Activity before record is saved?

Could save record and then populate field with the ID.

If Me.Dirty Then Me.Dirty = False

or

DoCmd.RunCmd acCmdSaveRecord

Or an UPDATE action after record is committed.

UPDATE ActivityT SET ID_Activity = ID WHERE ID_Activity IS NULL
 
Last edited:

Users who are viewing this thread

Top Bottom