Number seen as string from form when using in VBA

[twgonder]

Sorry don’t have time to speculate. Provide proper information or don’t bother. syntax errors typically show as red when you are typing it and complete a line. Compile errors (assuming you are using option explicit) relate to early bound objects - so no error means not a problem there. Run time errors relate to late bound objects and data

Okay, you asked for it... I'm joking.
I've tried to duplicate the error with a simplified .accdb, it doesn't crash, as of yet.
So, I made a video of the crashing with the larger .accdb and the offending code.
I presume it's the size and the complexity of the VBA code that causes that particular line of code to crash Access.

If I do this right, the video should be visible here:
https://1drv.ms/v/s!AmMsCrz5d72TgZJ4Ei3DYHD-QdvhTg?e=PQsPbb

[CJ_London]

Link doesn't work for me and really not interested in looking at a video. What works for me is copy/pasted code in code tags and the odd screenshot. Plus detail such as error messages. Or an uploaded db to illustrate the issue with clear instructions how to replicate the issue

I presume it's the size and the complexity of the VBA code that causes that particular line of code to crash Access.

size should not be a problem, and complexity just means you are not doing something correctly.

Since you can't answer a simple question (post #13) I'm going to drop out (again)

[twgonder]

What does crash mean? You get an error, it freezes? It closes.

Access pauses then closes without any error messages. Sometimes it's reported to not respond, with an hourglass (indicating some kind of loop, but it's not a loop in my code as the error happens without any of my VBA code running--as best as I can tell).

What do you have in the way of error handling?

This code that is causing the problem, I think, is in the Form_Error procedure. There are no on error statements, so if a line of code was failing, there should be a runtime error at that line, but there isn't any run'time error happening. The crash doesn't seem to happen when a line of code runs, rather when an alpha character is entered in a textbox (with corresponding error code) for a numeric field and then another control is clicked on. The error doesn't happen with a tab, only with click.

Are you using option explicit? The list goes on

Yes, option explicit is there. But as I said, it seems to be a problem within Access and not in the running VBA code of my form. I presume it's a deep Access bug where complex code is stomping on itself with the correct condition set by the developer's code.

I'm not sure why you can't see the video, try the link again if you would please. OneDrive does give me fits. The video is in a public folder, other users have been able to use my links to see other files, but who knows in this case (I can see it, but then it's my OneDrive)? Are there any error messages from OneDrive that indicate why the link doesn't work?

[twgonder]

For those that want to see it in action, not just in the video, I've stripped down the code and added the .accdb here.

Run the file, later if Access wants to run in safe mode, you may, it won't make a difference.

From the Main Menu click on MyForm2_2
Click in the My Number textbox
Replace the 10 with "a", press tab, wait 5 seconds
Click in the My text textbox.
If not an immediate shutdown of Access, then wait 20 seconds
Press tab again, wait 5 seconds
Click in the My Text textbox (or any textbox above).

Technically, the My Text won't get focus because an error still exists in the My Number textbox,
but the fact that you can click there should mean no VBA code is running.

I know I've made a few assumptions/presumptions in this and prior posts, but we have to start somewhere in debugging, even if they prove wrong later.

The video shows the lines to comment out to make the crash stop happening, but here it is:



I'm using Windows 10 22H2 19045.2364 with Access 2021 ver. 2211 build 15381.20208
I'm getting the crash on two different laptops (not identical), running the same version of Access.

[twgonder]

The error can be had by putting in My Date an "a" (not valid date), tab, and then click in another textbox.
You will have to F5 through the stop, but the crash will happen, maybe more dramatically.

Also, here is an extremely chopped down version of most of the essential code where the problem occurs,
but it doesn't happen in this wizard created, minimal VBA version. So, there's something in the size or complexity that's causing the problem,
unless the crash will happen for someone else doing the testing on this smaller .accdb file.

[CJ_London]

I tried your crash2 db - could not replicate the error although the messages are a bit confusing



similarly with the date field - control format is dd/mm/yyyy, whilst requested format is yyyy/mm/dd. Entering as yyyy/mm/dd works however


I'm sure they are only teething problems

so if you are having problems, it may be your messages are getting mixed up since for myNumber one is saying between 2 and 2 characters which implies a text entry

[Minty]

With such a restrictive period of available input dates why not remove all the pain and simply have a combo box listing them?
You could do the same with the numbers if they can only enter 10 to 99 ?

Just a thought.

[twgonder]

@ CJ_London Thanks for doing the test. I'm trying to narrow down why it's crashing on my two laptops and not others, although it's hard to find two that are the same, even mine are somewhat different, but both running Windows 10 with Access 2021.

But to be sure, you did enter an "a" in MyNumber, press tab (maybe a few times, getting the error messages), wait a few seconds and then try and CLICK on MyText, and again wait maybe 20 seconds? If it's going to crash, you'll know it as the application hangs and you can't do tab again or anything. You might get the hour-glass, but it's not consistently there.

There are cascading levels to the validation tests, first is if it's a number, then its size and then the range of values. Similar with dates. Depending on what you enter, you will get a different error. (In the Crash2.accdb VBA code, you are seeing only 10% of the validations that will ultimately be used.)

The date fields are entered and then validated by Access first to see if it's a date. There are many formats that Access allows. I use the region settings to display the dates, so each user has control over that, I just prefer to enter in the ISO manner (YYYYMMDD) to eliminate confusion, as I often see MM/DD and DD/MM living in South America. When I see a date as 01/02/03, I often have no idea what it could mean.

The size error message is showing the upper and lower number of digits to enter. Works the same for numbers as well as text entry. So, one can enter from 10 to 99 (to comply with 2 digits requirement), but as a test you can see that the range is 11 to 98. Look at the orange highlights if you can see them:



Remember, the db is mostly for testing various scenarios, so it's a mixed bag of deliberate "errors" and bugs.

[twgonder]

@ Minty Well, it's just a test of the range feature. But if it was a combo box, it would be about 180 rows.
It could just as well be a range of two years with this simple change:



producing this validation message:



let's see, that would be how many rows in the combo box?...um, I think not.

Anyways, did you do the crash test? What was the result? What versions of Windows and Access are you using, if I may ask?

[twgonder]

In case anyone is confused by the number of posts and variety of questions, Crash2 is just part of a bigger prototype that I've been asking questions for. And as features are shaken out in the next larger prototype test, they get added to the development environment, which has a shell around Access to accommodate the 3+ million lines of code being ported from a forty-year application.

Ultimately, the data you see in the Valdt() array will be placed in the shell records, not hard coded, much like the RAD/app did some 40 years ago in its db environment. These are just test validations parameters that Microsoft forgot to design into Access on day one, so we are working to accommodate (and now test just a few of) the features needed and present in the old RAD/app design.

In other words, this is just simple testing of how Access does and doesn't handle some requirements. Much to my chagrin, we're spending a lot more time working around some quirky designs and bugs in Access than I anticipated. Hence my somewhat critical exasperations at times. I would much rather be moving ahead in the project and managing the team than bothering you all with WTF questions that seem to come up hourly around here. Thanks for your understanding and help in all this.

[CJ_London]

But to be sure, you did enter an "a" in MyNumber, press tab (maybe a few times, getting the error messages), wait a few seconds and then try and CLICK on MyText,

I followed your instructions - several times

Click in the My Number textbox
Replace the 10 with "a", press tab, wait 5 seconds
Click in the My text textbox.
If not an immediate shutdown of Access, then wait 20 seconds
Press tab again, wait 5 seconds

There are cascading levels to the validation tests

I worked that out, I use a different method which does all the checks which always asks for the right data and doesn't leave the user guessing what is actually required - where appropriate I also use the control format property to show the user what format the data is required in. I also use code to prevent users entering characters not valid the data type

Ultimately, the data you see in the Valdt() array will be placed in the shell records, not hard coded, much like the RAD/app did some 40 years ago in its db environment.

I presume by shell records, you mean a meta table - if so, a common technique, I use that all the time for data input, importing and things like calculated values that could end up out of range.

These are just test validations parameters that Microsoft forgot to design into Access on day one

really? name them. Other than a dynamic date or number range, I can't think of one.

[twgonder]

...

really? name them. Other than a dynamic date or number range, I can't think of one.

Well, you just named two, and then you can look at most of the things you had to put in your "meta table".
One poor sap (it'll probably be me) is going to port all my old code that validates addresses, geographic areas, phone numbers and zip codes, etc. for a host of countries.

[twgonder]

I followed your instructions - several times...

Thank you for doing that. I think you mentioned there were no problems.
May I ask what versions of Windows and Access you tested with?
I'm trying to narrow down why my laptops are crashing and not other computers.

[twgonder]

...

I presume by shell records, you mean a meta table - if so, a common technique, I use that all the time for data input, importing and things like calculated values that could end up out of range.....

Kinda, think of MS templates and the old table wizard all wrapped up in one. The "meta table" will actually be its own shell application, with many tables and records.
In the old RAD, we had "properties" that did things for the app rather than 50+ for what colors, font or style type to use for hover, borders, background, foreground, etc. of controls.

[twgonder]

...
I worked that out, I use a different method which does all the checks which always asks for the right data and doesn't leave the user guessing what is actually required - where appropriate I also use the control format property to show the user what format the data is required in. I also use code to prevent users entering characters not valid the data type...

It's all good, I could do the same by commenting one or two lines of code. I try not to overload the textbox with messages (unless for debugging). I find most experienced users need just one prompt to realize they were entering in a different field than they thought. Hence, the cascade order.

I used to use formats, but that was part of a system that used "meta data" and depending on which country or maybe even something like phone type (as an example, there are countries that had different formats for fax numbers, and I know from first-hand experience they've done the same with cell phones; dang, even the land-line "area codes" here ranged from 1 to 3 digits!), formats could be modified during entry. And this is just the simple stuff, it gets a lot more complicated with the different legal systems and social norms which the porting app has to deal with. We're not yet there with Access, but getting there.