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

[regnewby2465]

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

No, the operator really only needs to see the ID_Activity after it is saved. Where would I put the if Me.Dirty statement, and where would I put the update syntax... I am assuming maybe BeforeUpdate event of form?

 

[Pat Hartman]

You would have to update the field in the AfterUpdate event of the Form which is generally NOT recommended. As long as you use an IF to only update when the field is null, you should be OK since that will cause the infinite loop to end after only one iteration.

If IsNull(Me.ID_Activity) and Not IsNull(Me.ID) Then
    Me.ID_Activity = ID
End If

Changing the value this way will dirty the record and Access will save it again after this event runs. DO NOT include any instruction to save. It will cause an error because you are already in a save loop as it is.

 

[regnewby2465]

You would have to update the field in the AfterUpdate event of the Form which is generally NOT recommended. As long as you use an IF to only update when the field is null, you should be OK since that will cause the infinite loop to end after only one iteration.

If IsNull(Me.ID_Activity) and Not IsNull(Me.ID) Then
    Me.ID_Activity = ID
End If

Changing the value this way will dirty the record and Access will save it again after this event runs. DO NOT include any instruction to save. It will cause an error because you are already in a save loop as it is.

Thank you.

 

[Pat Hartman]

And just yesterday, I told you to not do this I still think you shouldn't but then I don't understand the actual problem. So, as long as you think it works, who am I to say it doesn't. I've never been able to use SharePoint because all my clients had too much data so my clients got to use Citrix or RD to run their Access apps in the cloud. That way they get to keep a real database as the BE instead of having to use a SharePoint list with all the trouble and limitations they have.

 

[regnewby2465]

And just yesterday, I told you to not do this I still think you shouldn't but then I don't understand the actual problem. So, as long as you think it works, who am I to say it doesn't. I've never been able to use SharePoint because all my clients had too much data so my clients got to use Citrix or RD to run their Access apps in the cloud. That way they get to keep a real database as the BE instead of having to use a SharePoint list with all the trouble and limitations they have.

Well you were right again.... using

If IsNull(Me.ID_Activity) and Not IsNull(Me.ID) Then
Me.ID_Activity = ID
End If

in the AfterUpdate, the form locked in the Edit mode. I guess to move past this issue I will just use the DMax and increment +1. We tried. I am interested in the Citrix and RD. I think the reason my clients like the Sharepoint is because they have the Office 365 Business which includes Sharepoint.... I doubt they will want to spend much money on another cloud-based solution unless its FREE. ha ha ha

 

[GPGeorge]

@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?

I think you can work around the problem, yes. You can't avoid SharePoint's self-absorbed desire to control everything it sees, though.

 

[GPGeorge]

Well you were right again.... using

If IsNull(Me.ID_Activity) and Not IsNull(Me.ID) Then
Me.ID_Activity = ID
End If

in the AfterUpdate, the form locked in the Edit mode. I guess to move past this issue I will just use the DMax and increment +1. We tried. I am interested in the Citrix and RD. I think the reason my clients like the Sharepoint is because they have the Office 365 Business which includes Sharepoint.... I doubt they will want to spend much money on another cloud-based solution unless its FREE. ha ha ha

Pat's remark on the limitations of SharePoint regarding record numbers is crucial to thinking about using SharePoint. Theoretically, SP lists can hold many thousands of records. In practice, the upper limit you can expect to use is going to be much less. If you have a relatively small, simple database application and never expect to exceed say 20,000 or 25,000 records in a table, SharePoint is a viable option. It's not a cut and dried number, though. YMMV.

 

[GPGeorge]

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.

Not in SharePoint. You have zero control over this. The Primary Key is always created and it's always an Autonumber.

In fact, by using their own pseudo primary key, all that is happening is that the actual PK is being ignored.

 

[GPGeorge]

@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?

I re-read your post suggesting that. It sounds like the same method I blogged about 10 or 11 years ago when we had this shiny new toy called "Access Web Databases"