After adding a new record followed by the enter key, the user is in a blank newrecord field. Suffice to move the cursor back one record to see the new record and default subform records, but is there a simple way for the macro to bring the cursor (setfocus) one record back? Thank you
There are multiple situations. For example, there are ten records and you are editing #2 record then jump to a new record. Problem. Also cannot go back one record as it is not the last one. Another is as you suggest going back one record, you then cannot tell if that one record was entered as new or was existing.
The new needs to be identified as it is entered and not after updating.
It is your choice upon using triggers. Database triggers, procedures and functions (where available) can reduce user code and actually speed up processing. But again, your choice.
Thank you for the explication.
I reckon I’ve figured out a solution.
Similar to the Sub AddNewRecord routine, I made a similar routine to store a global variable (BRC) on the before record change event and one (ARC) on the after record change event.
According to my tests, interogating the BRC and ARC variables before doing anything in the Sub AfterAddRecord routine, any record edits leave something in the BRC and/or ARC, a record delete leaves something in ARC.
Only when I enter a new record, whether going direct after open form or after doing an edit, or scrolling down, only in this case BRC and ARC are both empty.
Does that make sense to you?
Will update the code here. Meantime need to figure out how to setfocus on the record just added using the ID applied.
Personally I would use the trigger. The code just keeps getting deeper & deeper.
As for your solution, my choice would have been simply in Sub AddNewRecord if it were an existing record put something in sGlobalHold like *NOT NEW* then at the start of Sub AfterAddRecord check for it & if equal exit.
Trigger first, otherwise simply keep as simple as possible.
Thank you for your suggestions. I have posted the working solution.
I think the final code is fairly simple (forgetting the RunSomeSql routine which is just for fulldisclosure info for visitors). That was quite a brainteaser to get the right combination of “appropriate” .event triggers. Thank you very much for your great help and guidance.