Today’s adventure was to fix a bug in a menu form in which the user was getting a “This record has been deleted” error message.
What was happening was that the navigation form was based on a local temp table that gets rebuilt whenever the application is opened.
There is a link on the users desktop to open the app that links to MS Access with the database as the file argument.
The link opens a new instance of Access every time you open it and so the second instance would rebuild the temp table that the first instance was in, and this was what was causing the record to be deleted.
There’s a few ways I might have approached this. I may try a more complex change later on, but for now I chose a quick approach to directly address the error behavior where it was being experienced. In the form backend code, the call was simple:
Private Function HandleButtonClick(intBtn As Integer) Call AJSmenu_ButtonClick(Me,intBtn,InputParam) End Function
The error occurred when the AJSmenu_ButtonClick sub called a control Me![Menu_Number] which then handled the error. I couldn’t easily handle it there without changing a library where the function lives, so I checked the variable myself to force the error if the deleted record condition exists.
I first created TestVal as a variable to hold the Menu_Number value. Then I set a new error handler in case the record was deleted. If it’s not, it will move on to the button code. Otherwise, if the record is deleted, it will throw error 3167 which I check for and then requery the form and resume back at the line that errored to try it again.
Private Function HandleButtonClick(intBtn As Integer) Dim TestVal As Variant On Error GoTo CheckForDeletedRecordSet TestVal = Me![MenuNumber] On Error GoTo 0 Call AJSmenu_ButtonClick(Me,intBtn,InputParam) GoTo Exit_Function CheckForDeletedRecordSet: If Err.Number = 3167 Then Me.Requery Resume End If Resume Next Exit_Function: End Function
This worked and I deployed it to the customer to prevent the error (which he got 3 times in the last 3 days!
Although this very specifically handles the situation my customer was experiencing, I already have some thoughts on addressing this in the future.
- I don’t want them to be able to open the app twice! But in order to fix that, I need to change the setup routine and create some sort of launching script. Probably the safest script would be in a command line batch file. I could get fancier and use either another Access database or perhaps a PowerShell script, although for PowerShell I’d need to look into security constraints. Probably just using a batch file to test for the deletability / non-existence of the .laccdb file would work well. I’d also need to figure out how to build this into my deployment script and installable exe file.
- I could also place the VBA script to fix the deleted record issue up higher. I originally thought I’d put a Requery into the form’s OnActivate event, but that didn’t end up working very well in my initial test because I had no conditional code yet and the reQuery sent my app into an infinite loop of some kind. Maybe re-firing the onCurrent and onActivate in a circle of death. I have also thought about putting the check in the Form OnClick event.
- Ultimately, do I really want the script to depend on being able to connect to the temp table? Normally I just load the app menu and never touch it again, but what if I needed special extra code in the form for something in particular? Security could be something that would trigger code there (although I’m already dealing with the security in other ways). But in any event, I really don’t need the form to stay connected to the record set. It could have just kept that current menu in a global variable in the form or a static variable in the function itself. I could refactor the library not to require a form object to be passed to it. That’s sort of asking for trouble of this kind.
So there’s a few other options I will consider to improve my application framework for the future as well as making this app better for my current customer!