Hello, I am facing a strange problem, I've looked everywhere for a solution but couldn't find any previous posts about it. I will try to explain as best as I can, but it's quite hard to describe.
I have a main form with two fields, and a subform with 5 fields. The main form has ReceiptNumber and ReceiptDate. The subfrom is for Item Details. It's a continuous form, a row for each Item in the Receipt. Pretty standard, you might say.
The purpose of the forms is data-entry. The basic functionality of the forms works perfectly well. The subform is locked by default, I put in code in the main form field's AfterUpdate event to unlock the subform when the main form has been filled. I also put in a code triggered by subform's OnEntry event to check if the main form has been filled, if it was not filled, focus is moved back to main form and subform is locked, this is done to ensure that the subform is re-locked when the user fills out a complete form and moves on to a new main form (ie: a new page.) I am summarizing here, because this is not where the problem occurs. The validation, lock/unlock events work perfectly. I've tested all the data entry scenarios I could think of, going back and forth, filling, deleting, etc. and all was well except for one specific scenario:
When the main form is filled and the user moves on to the subform and fills out a line or two, and then clicks on any field in the middle of the new line (a blank line,) and then tries to move away from that line, Access will show an error telling the user that fieldX is required and was not entered. User would then press "Esc" if he does not want to enter data for a new record. After hitting the escape button, Access would stop giving the user the error message and would allow him to click anywhere he wants. So far this is normal, it is what's supposed to happen, no problem there.
The problem occurs when the user immediately clicks the "new record" button (either on Access's default navigation pane, or on the "add new receipt" button I created.) The main form moves on to a new parent record, both the main form and the subform are cleared as they should. However, the subform, instead of being locked and having one blank line, is unlocked and on focus, and there are two blank lines instead of one. The cursor is still on the field in the middle of the child record, and the same error message ("You must enter a value in fieldX") is displayed again, when the user clicks "OK", my OnEntry event procedure runs and fails (the procedure that's supposed to move focus to the first main form field and lock the subform,) an error message is shown, "Microsoft Access Can't move the focus to the control MainFormField1" and user is given the option to either "End" or "Debug."
The problems seems to be that Access, when moving on a new main form page, never lets go of the cursor's position in the previous page. The cursor is still positioned in the middle of the child record, because of that, it still thinks that the user is trying to create a new child record and, therefor, it is enforcing the entry of the required fields for that table (the child table, AKA the Item Details Table.) This is also causing my procedure to fail, as the procedure is trying to move focus away from the record, and Access, by default, disallows moving away from the empty new record unless the required fields are filled, or the Escape key is pressed.
I tried putting SENDKEYS "{ESC}" on top of my code, but it did nothing. I also tried adding the undo command to cancel incomplete records, but it didn't work either. If it worked, I think it would also delete complete records in normal scenarios, so I didn't think it was a good solution anyway.
This scenario is likely to occur with real users; it could happen this way: The user is entering a list of items from a paper receipt, then, after clicking the next row to enter another item, realized that the last item on the list has already been entered, he would then hit escape and click the "add new receipt button" and then the mess ensues. In fact, the only time this wouldn't happen is:
1- If the user relied only on the TAB key to navigate.
2- or neatly clicked only on the FIRST field on the child record whenever he wanted to add a new item to the list.
3- or if he never changed his mind and never made mistakes.
Needless to say, I really need to solve this problem. This bug is a real nightmare. Like I mentioned, I looked everywhere for information, and I spent at least 14 hours so far trying to find a solution, making changes here and there, but I'm still right where I started. I can't believe that I'm the only one who's facing this problem, as it is an Access-wide issue, rather than an issue specific to my database.
I have no idea what to do next.. Even if I removed my event procedure and figured out another way to control the integrity of the data, there would still be the problem of the user getting an error message requesting the required field.
Is there a way to get access to properly reset the cursor position when moving to a new record in a form?
Any help would be much appreciated.
I have a main form with two fields, and a subform with 5 fields. The main form has ReceiptNumber and ReceiptDate. The subfrom is for Item Details. It's a continuous form, a row for each Item in the Receipt. Pretty standard, you might say.
The purpose of the forms is data-entry. The basic functionality of the forms works perfectly well. The subform is locked by default, I put in code in the main form field's AfterUpdate event to unlock the subform when the main form has been filled. I also put in a code triggered by subform's OnEntry event to check if the main form has been filled, if it was not filled, focus is moved back to main form and subform is locked, this is done to ensure that the subform is re-locked when the user fills out a complete form and moves on to a new main form (ie: a new page.) I am summarizing here, because this is not where the problem occurs. The validation, lock/unlock events work perfectly. I've tested all the data entry scenarios I could think of, going back and forth, filling, deleting, etc. and all was well except for one specific scenario:
When the main form is filled and the user moves on to the subform and fills out a line or two, and then clicks on any field in the middle of the new line (a blank line,) and then tries to move away from that line, Access will show an error telling the user that fieldX is required and was not entered. User would then press "Esc" if he does not want to enter data for a new record. After hitting the escape button, Access would stop giving the user the error message and would allow him to click anywhere he wants. So far this is normal, it is what's supposed to happen, no problem there.
The problem occurs when the user immediately clicks the "new record" button (either on Access's default navigation pane, or on the "add new receipt" button I created.) The main form moves on to a new parent record, both the main form and the subform are cleared as they should. However, the subform, instead of being locked and having one blank line, is unlocked and on focus, and there are two blank lines instead of one. The cursor is still on the field in the middle of the child record, and the same error message ("You must enter a value in fieldX") is displayed again, when the user clicks "OK", my OnEntry event procedure runs and fails (the procedure that's supposed to move focus to the first main form field and lock the subform,) an error message is shown, "Microsoft Access Can't move the focus to the control MainFormField1" and user is given the option to either "End" or "Debug."
The problems seems to be that Access, when moving on a new main form page, never lets go of the cursor's position in the previous page. The cursor is still positioned in the middle of the child record, because of that, it still thinks that the user is trying to create a new child record and, therefor, it is enforcing the entry of the required fields for that table (the child table, AKA the Item Details Table.) This is also causing my procedure to fail, as the procedure is trying to move focus away from the record, and Access, by default, disallows moving away from the empty new record unless the required fields are filled, or the Escape key is pressed.
I tried putting SENDKEYS "{ESC}" on top of my code, but it did nothing. I also tried adding the undo command to cancel incomplete records, but it didn't work either. If it worked, I think it would also delete complete records in normal scenarios, so I didn't think it was a good solution anyway.
This scenario is likely to occur with real users; it could happen this way: The user is entering a list of items from a paper receipt, then, after clicking the next row to enter another item, realized that the last item on the list has already been entered, he would then hit escape and click the "add new receipt button" and then the mess ensues. In fact, the only time this wouldn't happen is:
1- If the user relied only on the TAB key to navigate.
2- or neatly clicked only on the FIRST field on the child record whenever he wanted to add a new item to the list.
3- or if he never changed his mind and never made mistakes.
Needless to say, I really need to solve this problem. This bug is a real nightmare. Like I mentioned, I looked everywhere for information, and I spent at least 14 hours so far trying to find a solution, making changes here and there, but I'm still right where I started. I can't believe that I'm the only one who's facing this problem, as it is an Access-wide issue, rather than an issue specific to my database.
I have no idea what to do next.. Even if I removed my event procedure and figured out another way to control the integrity of the data, there would still be the problem of the user getting an error message requesting the required field.
Is there a way to get access to properly reset the cursor position when moving to a new record in a form?
Any help would be much appreciated.
Last edited:
