Sometimes it seems that Microsoft Access has its own persistent personality that throws out the bugs that remain, no matter what you try to do. I wanted to share my recent experience of problem solving to show what steps can be taken when a job simply doesn’t work as expected, and what decision-making process is needed to solve the problem.
Recently I received an error message by e-mail from one of my customers. They tried to execute the report I made for them some time ago and got a rather vague mistake.
The VBA error handling has in any case functioned as intended; it has passed on the number and description of the error issued by the VBA so that the user can leave the screen and continue working with the program. It can’t hurt to mention the name of the subprogram for my convenience, but this client is good at giving information about where he found it. In this case it was a form that allows you to view the results of the query and then export it to Excel.
When I looked at the procedure behind the form, I found the DoCmd.TransferSpreadsheet command where it failed and the request it tried to export. While executing the request, I had another error that tried to view the data instead of exporting it:
The error is too complex to be assessed in the access request, it’s a bit like the Check Engine light in your car. It won’t tell you what’s going on, but there’s definitely something Access SQL doesn’t like and won’t cooperate until you find out.
The next step was to examine the elements of the motion to find out what could be the cause of the problem. By deleting all fields, except for a few basic ones, the query could be executed, which meant that at least the query itself worked with multiple table relations.
I finally narrowed it down to this area:
This customer uses the freight reference number, which combines the date code with the serial number (-1, -2, -3, etc.). In the first line above, the query uses Left() and InStr() to remove the hyphen and serial number so that you can only find records with the date code. The criteria row below indicates that the resulting value will be compared with the value selected in the combined field of the form. In this way, the customer can retrieve all data for this date code.
There was clearly something in this combination of functions and criteria that made it indigestible, but I needed it for my work. I was also a little confused about why it failed now, because I know I tested it before releasing the changes.
Since Access no longer provided information, I split the left() function into my own VBA function and used it in the request. With hindsight, I should have taken a closer look at the values initially generated by the query(), but there are a lot of things that seem obvious after they hit us.
Public function GetDateCodeFromShipment (ShipmentID as string) As string
‘Get line position in NonReturnID.
intDash = InStr([ShipmentID], -) –
‘Returns the corresponding part.
GetDateCodeFromShipment = Links ([ShipmentID], intDash)
I could then use this function in the query, and it would return the date code needed to compare it with the code selected in the form.
…and he failed!!!!
An error has occurred in the Left() function from the last line of the code above. Then I decided to look at the values returned by the InStr() function and of course I found that a record -1 returned, which the Left() function didn’t like.
The reason for this is that, for the first time in tens of thousands of cases, a customer accidentally entered a shipping ID number without a hyphen and without a continuous number. InStr() returned 0 because no hyphens were found, then Left() -1 tried to select characters in the load identifier, resulting in an illegal function call. Since the whole function was previously included in the application, there was no error processing to explain the message until I analyzed it in my own function.
I also noticed that the query has to be filtered in another field, which will further reduce the number of returned records. With an extra filter, bad data had no effect, and it worked faster.
The extra request filter was the first step. Since I had already written a new function, I decided to leave it and add a few lines to work with values that didn’t contain dashes.
Get a line position in the load ID.
intDash = InStr([ShipmentID], -) – 1
Send back the right section.
If intDash > 0, then
GetDateCodeFromShipment = Left([SendID], intDash)
‘No dash, return all.
GetDateCodeFromShipment = Shipment
I made it controversial at the time because I decided to try to avoid further errors when entering the data in the form from which it came.
Partial transmission privateID_BeforeUpdate(Cancel as a whole)
Make sure there is a serial number at the end.
If InStr(Me.ShipmentID, -) = 0, then the code
of the MsgBox transmission must end with a hyphen followed by an ordinal number. (-1, -2, -3 etc.). Correct this if possible.
egOKOnly, sequence number missing…
The end, like
The event BeforeUpdate in the form field can be used to start the data check and cancel the update if necessary. You will notice the cancellation argument in the announcement of the above event. Setting this argument to Where (even if it is an integer) will cancel the update and the focus will remain on this field until the user has solved the problem. In this case, I decided to simply display the warning and not restrict the customer’s data entry more than absolutely necessary.