Troubleshooting VBA code - (1 Viewer)

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
An updated version, still broke but the other one was missing a lookup table for City, State.

To see the issue live. Open the database > click the Contacts button > click with you mouse from one record to another.

1587778648451.png


The error implies that it's trying to spellcheck a field and there isn't a value. The issue is all the Call SpellChecks are on field events not on the form and you aren't changing a value and the event is firing.

So, then I commented out the spell check events on the below forms and subforms. Then individually, I uncommented the spell check.
f_Contact_Experience_Relationship
f_Contact_Grouping_Relationship
f_F_Contact_Project_Relationship
f_Contacts

It appears the error is triggered on this code within f_Contacts_Experience_Relationship.
Private Sub Title_exit(Cancel As Integer)
Call SpellChecker(Me.Title)
End Sub

Is Title a reserved word?
 

Attachments

  • IsBlank Issue - Clear All Values - Reduced More v6.zip
    760.6 KB · Views: 109
  • 1587777787432.png
    1587777787432.png
    39.8 KB · Views: 91
Last edited:

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
Not sure when I will look at this. Excuse me, but to put it as politely as I can

I AM EFFING PI$$ED OFF AT MYSELF BIG TIME. 🤬

I'm woodworking in the meantime and I have made a MAJOR boo boo. I may have to trash about 20 hours of work and do over (guitar top).
Tonight is a night for Scotch and serious reflection on how I can be so stupid and careless sometimes.
 

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
I hope I wasn't the cause of your frustration.

Not sure when I will look at this. Excuse me, but to put it as politely as I can

I AM EFFING PI$$ED OFF AT MYSELF BIG TIME. 🤬

I'm woodworking in the meantime and I have made a MAJOR boo boo. I may have to trash about 20 hours of work and do over (guitar top).
Tonight is a night for Scotch and serious reflection on how I can be so stupid and careless sometimes.
 

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
Thought I had something..... corrected database uploaded …. but still erroring.

For this version, it appears to only trigger when moving from record 1 to record 2. The different between them is record 1 has a value on the Experience subform, while record 2 doesn't.

1587779595097.png

It appears the error is triggered on this code within f_Contacts_Experience_Relationship.
Private Sub Title_exit(Cancel As Integer)
Call SpellChecker(Me.Title)
End Sub
 

Attachments

  • IsBlank Issue - Clear All Values - Reduced More v7.zip
    761.9 KB · Views: 137

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
I hope I wasn't the cause of your frustration.
Not at all. I just made a stupid, stupid calculation error and probably ruined hours of work. I may have to start a new top and figure out a way to extract the inlay from the messed up one. It took me 2 days just to do the rosette and it's all for naught (I think). I've learned to put aside such issues for a day or two and let my subconscious process the problem for me.

Back to my recorded baseball game and Scotch #2. 🍸
(there doesn't seem to be an emoji for imbibing with ice).
 

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
Before taking another look and why you didn't/can't use the Text property as I think I pointed out long ago, I came up with a question or two.
What is the purpose of the function, spell checking? If so, why would you ever run that check on a bound combo on so many events? Even AfterUpdate seems pointless as a user can only pick from predetermined values. The only time you'd want to spell check is IF you allow users to add values to the table that the control is bound to, and that field would have to be supported by a dictionary.

I'm wondering if you have a code issue that doesn't need to be solved in the first place. It's also interesting that your message indicates the function is in a module that doesn't seem to exist.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:07
Joined
Sep 21, 2011
Messages
14,050
I am trying to understand why you are calling a spell checker when the control is empty?
 

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
I am trying to understand why you are calling a spell checker when the control is empty?
A great rhetorical question. When you figure out why it's happening we're all ears.
 

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
What is the purpose of the function, spell checking? If so, why would you ever run that check on a bound combo on so many events? Even AfterUpdate seems pointless as a user can only pick from predetermined values. The only time you'd want to spell check is IF you allow users to add values to the table that the control is bound to, and that field would have to be supported by a dictionary.
The Isblank is a public function. It appears that having a no record value on a sub data sheet triggers it. Nothing to do with spelling. If try, why is the question we're trying to answer.

The position title field is getting spelled checked because people would be typing in their own values. But again I don't think that is the issue.
 

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
But again I don't think that is the issue.
Well, I think it is related. You're checking spelling on so many events that don't make sense.
Sometimes, there is no value to check.
Sometimes there will be text that doesn't match the value.
You can't check text unless you can set the focus. You can't set the focus in every event you've chosen.

Does that make sense? Anything that is done to solve the error on a stripped down version is likely to be for naught when you bring back all the other events. The only time you should be spell checking is when you handle the NotInList event if you're allowing value additions to the field.

I'm curious to know how you can spell check organization names. You have a dictionary that supports every organization name that users might come across?
 

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
Well, I think it is related. You're checking spelling on so many events that don't make sense.
Sometimes, there is no value to check.
Sometimes there will be text that doesn't match the value.
You can't check text unless you can set the focus. You can't set the focus in every event you've chosen.

Does that make sense? Anything that is done to solve the error on a stripped down version is likely to be for naught when you bring back all the other events. The only time you should be spell checking is when you handle the NotInList event if you're allowing value additions to the field.

I'm curious to know how you can spell check organization names. You have a dictionary that supports every organization name that users might come across?
Looks like we're talking past each other. I think Ive only coded the spelling to happen on specific text fields and definitely not on the organization field which is a combo box.
 

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
OK, I must have lost some direction between last night and today. I'll take a look at your latest upload later and see if I can make sense of what the code does and to what.
 

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
I'm going to keep trying to reduce the code down to try and get to the essentials.

Compact on close was checked. I unchecked for the next update.

Removed the Navigation form, most of the fields on the Contacts form. Removed all but one subform (since having zero subforms stops the issue. Removed the tables and queries that supported those subforms.
 

Attachments

  • IsBlank Issue - Clear All Values - Reduced More v9.zip
    392.4 KB · Views: 105

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
Continuing to reduce.
 

Attachments

  • IsBlank Issue - Clear All Values - Reduced More v11.zip
    215.7 KB · Views: 137

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
I'm down to a main form with primary key, last and first name and one subform with a text field.
Only one field on the subform doing spell checking. I pulled most of the code off the forms.

Maybe I'm down to a small enough subset to troubleshoot? In the attached, open Contacts and then move from record 1 to 2. What's causing this error? Now that I've reduced, the error doesn't repeat, so to see again, close and reopen the form.

1587846473659.png
 

Attachments

  • IsBlank Issue - Clear All Values - Reduced More v12.zip
    327.5 KB · Views: 114

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
Well, if you remove .Value from your spellcheck code and replace it with .Text the problem goes away. While stepping through the spell check function and in the immediate window
?txt.Value raises the latest error you posted.
?txt.Name returns the name of the control, so it is being passed to the function
?txt.Text returns the control value.

So it has been said in this and I think the other thread you had that was related to this function, using Text property was suggested, but so was the notion of passing the contents of the control instead of the control itself. Unless you are going to modify an object or need to deal with its properties (beyond Text or Value) I see no reason to complicate things by passing an object when you can pass a simple string instead. Who knows? Perhaps this is a bug, where .Value cannot be accessed from a passed control but Text can. I don't know, but my advice would be to drop the idea of passing the control, and if you can't do that, try using Text property instead.
 

dgreen

Member
Local time
Today, 05:07
Joined
Sep 30, 2018
Messages
397
@Micron Thank you for your persistence.

Tmrw, I'm going to continue to troubleshoot and see if this fixes the issue with the base database. I hope this was the root cause that triggered the request for support.
 

Micron

AWF VIP
Local time
Today, 06:07
Joined
Oct 20, 2018
Messages
3,476
If you try that, don't forget to do it everywhere you're using this. In the one place (function) I saw it, there were 2 references to .Value. I had to change both of them to get the error to stop when navigating. That comment assumes you're going to continue to pass the control instead of its contents.
Good luck!
 

Users who are viewing this thread

Top Bottom