We had a split Access database, and recently moved the back-end from Access to SQL (by "upsizing it"). The front-end remains in Access. There is a slight performance difference that is affecting some people, that I am trying to figure out the best way to address.
We have some entry Forms that are linked directly to the underlying tables. One of the fields is an Autonumber field. When the back-end was in Access, the Autonumber field would be populated as soon as they begin to enter data in any field on the Form. Now that the back-end is in Access, it appears that this Autonumber field does not get updated until they move off the record (I am guessing that this is the point at which the record is actually written to the table).
This slight change is causing an inconvenience for our users. They sometimes need to note the Autonumber ID field. Before they could see it as soon as they begin entering the information. Now, they are hitting the a record selector (forward/previous/new record), and then going back to see it.
What is the best way of allowing them to see the Autonumber ID without having to first move off the record?
I thought about adding VBA code to "write/update" the record to the table (not exactly sure the exact syntax to do that). Then I could maybe add a button that they can click to run that VBA code. Or maybe put it in the AfterUpdate event of some key fields. Of course, one concern might arise is what if they make an error and want to remove that record completely. So I would probably need to address that.