Solved

Issue With Spell Checking a Field on a Form

Posted on 2013-06-14
20
663 Views
Last Modified: 2013-11-28
I have the following VBA code to do a spell check on a field called Audit_Notes.  I have this code on the After_Update Event for this field:

Code:
Private Sub Audit_Notes_AfterUpdate()
Me!Audit_Notes.SetFocus
Me!Audit_Notes.SelStart = 0
Me!Audit_Notes.SelLength = Len(Me!Audit_Notes)
RunCommand acCmdSpelling

End Sub

Open in new window




Code in the Form's Before_Update Event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

DoCmd.Echo False

If Me.NewRecord <> True And Me.cboAuditStatus = "Completed" Then
    Call AuditTrail(Me, InquiryNum)
End If
        
If Me.NewRecord <> True And Me.Action_Required = "" Or Me.Action_Required = "No" Then
    Me.Action_REQ_Reason.Enabled = False
ElseIf Me.NewRecord = True And Me.Action_Required <> "" Or Me.Action_Required = "Yes" Then
    Me.Action_REQ_Reason.Enabled = True
Else
    Me.Action_REQ_Reason.Enabled = False
End If

If Me.cboDept Like "EGR*" Then
    Me.cboSubDept.Enabled = True
Else
    Me.cboSubDept = ""
    Me.cboSubDept.Enabled = False
End If

DoCmd.Echo True

Exit_Handler:
    Exit Sub

Err_Handler:
    'MsgBox Err.Description
    Cancel = True
    Resume Exit_Handler

End Sub

Open in new window



Screen Shot of Error I'm getting:

Screen Shot of Error When Running Spell Check Code

What in the Form's Before_Update Event is causing this error?

Thanks,
gdunn59
0
Comment
Question by:gdunn59
  • 11
  • 2
  • 2
  • +2
20 Comments
 
LVL 84
ID: 39249795
When does the error occur? What are you doing when this happens? Are you moving to a new record, did you click a button, etc etc?
0
 

Author Comment

by:gdunn59
ID: 39254776
Sorry for just now getting back to you, I was out of the town for the weekend.

It happens when it's doing the spell check.  I ignore the first two words, and then when it spell checks the next word I tell it to change the word because it is misspelled and I click the correct spelling, and that's when the error happens.

Thanks,
gdunn59
0
 
LVL 84
ID: 39255873
Hmmm ... the code in that AU event shouldn't fire the Form's BU event. This would lead me to question whether the database needs some maintenance:

Compact the database, the Compile it, then Compact again.

You might consider Decompiling the database: Build a Shortcut with a target like this:

"full path to msaccess.exe" "full path to your db" /decompile

Run that, then run the 3 steps above again.

Also be sure your Office and Windows installs are fully up to date.
0
 

Author Comment

by:gdunn59
ID: 39264955
LSMConsulting:

I did the steps you suggested above (compact, compile, compact, decompile etc.), and I still get the first error I mentioned above, and it does the spell check and makes any corrections, but then I get a second error, and if I click OK, it undoes the spell check changes and starts the spell check again.

I've attached a screen shot of the 2nd error I'm getting.

Thanks,
gdunn59
ScreenShotOfSpellCheckError-2-.docx
0
 

Author Comment

by:gdunn59
ID: 39272071
Thank you!
0
 

Author Comment

by:gdunn59
ID: 39278889
Still no response.

Thanks,
gdunn59
0
 
LVL 21
ID: 39279357
gdunn59,

Will you please post a sample database with a form that demonstrates the issue.
0
 

Author Comment

by:gdunn59
ID: 39282163
TheHiTechCoach:

Here is a dummy database with the spell check issue.

So if you open the database and on the Form the Audit Note field, if there is a typo there it is setup to do the spell check on the After_Update Event of this field.  When an error is encountered, it allows the user to change the incorrect word, but once it is through spell checking it automatically goes to the next record.  I want it to just do the spell check on a specific field (in this case the Audit Note field), and not go to the next record after completing the spell check.  After the spell check is complete, I need it to set the focus to the next field (Action_Required).

I also tried the code on the On_Exit Event of this field, also tried it on the Before_Update Event.  

As well, I tried this code also:

Me!Audit_Notes.SetFocus
Me!Audit_Notes.SelStart = 0
Me!Audit_Notes.SelLength = Len(Me!Audit_Notes)
RunCommand acCmdSpelling

Open in new window


Thanks,
gdunn59
EE-BETA-Audit-Database-SQL-Relea.accdb
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 21
ID: 39293432
This code works with Access 2010:

Private Sub Audit_Notes_AfterUpdate()

'If the textbox contains data run the Spell Checker after data is entered.
    If Len(Me!Audit_Notes & "") > 0 Then
      
        DoCmd.SetWarnings False   ' <<<<***** turn warning off
      
        Me!Audit_Notes.SetFocus
        Me!Audit_Notes.SelStart = 0
        Me!Audit_Notes.SelLength = Len(Me!Audit_Notes)
        
        DoCmd.RunCommand acCmdSpelling

        DoCmd.SetWarnings True   '<<<<******** turn warning back on
        
    '   Me.Action_Required.SetFocus

    Else
        Exit Sub
    '    Me.Action_Required.SetFocus
    End If

'Me!Audit_Notes.SetFocus
'Me!Audit_Notes.SelStart = 0
'Me!Audit_Notes.SelLength = Len(Me!Audit_Notes)
'RunCommand acCmdSpelling

'Me.Action_Required.SetFocus

End Sub
Private Sub Audit_Notes_Exit(Cancel As Integer)
On Error GoTo Err_Handler

'DoCmd.Echo False

'DoCmd.RunCommand acCmdRefreshPage

'Me.Action_Required.SetFocus

'DoCmd.Echo True

Exit_Handler:
    Exit Sub

Err_Handler:
    DoCmd.CancelEvent
    Resume Exit_Handler
    
End Sub

Open in new window


See attached
EE-BETA-Audit-Database-SQL-Relea.zip
0
 

Author Comment

by:gdunn59
ID: 39294829
TheHiTechCoach:

When I try to spell check in the "Audit Notes" field, I'm getting an error about the BeforeUpdate Event.  Here is a screen shot of the Error:

ScreenShotOfSpellCheckError--7-2.docx


Here is the code that I have for the Form's BeforeUpdate Event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

DoCmd.Echo False

If Me.NewRecord <> True And Me.cboAuditStatus = "Completed" Then
    Call AuditTrail(Me, InquiryNum)
End If
        
If Me.cboDept Like "EGR*" Then
    Me.cboSubDept.Enabled = True
Else
    Me.cboSubDept = ""
    Me.cboSubDept.Enabled = False
End If

DoCmd.Echo True

Exit_Handler:
    Exit Sub

Err_Handler:
    'MsgBox Err.Description
    Cancel = True
    Resume Exit_Handler

End Sub

Open in new window

0
 

Author Comment

by:gdunn59
ID: 39294840
TheHiTechCoach:
 
I mentioned the BeforeUpdate Event error earlier in discussions, and someone suggested that the database needed some maintenance, and told me to do some compiles and repairs, and decompile, etc.  I did all this, and that didn't solve the issue with the BeforeUpdate Event error.

Thanks,
gdunn59
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39294862
I'm guessing that this has to do with the subform.  Which "BeforeUpdate" event is causing this error, the main form, or the subform?

What field are you attempting to spell-check and how are you initiating the spell check routine?  Are you using the text boxes standard right click menu, or some other method?
0
 

Author Comment

by:gdunn59
ID: 39294899
fyed:

It's in the Form's BeforeUpdate Event.  The field that I am attempting to spell check is the "Audit Notes" field.  The spell check code is in the field "Audit Notes" AfterUpdate Event, which is what TheHiTechCoach suggested.

Thanks,
gdunn59
0
 

Author Comment

by:gdunn59
ID: 39307429
It has been 6 days since I last posted, still haven't heard back.

Thanks,
gdunn59
0
 

Author Comment

by:gdunn59
ID: 39308923
I've tried this code since my last posting, but I'm getting a run-time error 3270 (property not found).

The error is occurring on the following line of code:
            DoCmd.RunCommand acCmdSelectRecord

Here is the Code:
Private Sub Audit_Notes_AfterUpdate()
'If the textbox contains data run the Spell Checker after data is entered.
    If Len(Me!Audit_Notes & "") > 0 Then
       Me!Audit_Notes.SetFocus
       DoCmd.RunCommand acCmdSelectRecord
       DoCmd.RunCommand acCmdSpelling
       DoCmd.RunCommand acCmdRefreshPage
    Else
        Exit Sub
    End If

End Sub

Open in new window


Also, I've increased the points by 50 from (250 to 300).

Assistance needed -- PLEASE!!!!!!!!!!!!1

Thanks,
gdunn59
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 300 total points
ID: 39310391
I haven't use spell check like that myself, but it seems obvious it's causing the field to be updated again and your in a loop.

Couple of suggestions:

1. Move the spell check to the end of the Before Update event code.

2. Create a yes/no flag and check it in the BeforeUpdate event.  If it's set, bypass the code you have.

 So when you go to spellcheck, set the flag to true, and after set it to false.

3. Place a STOP in the following event procedures:

Control BeforeUpdate
Control AfterUpdate
Form BeforeUpdate
Form AfterUpdate
Form OnCurrent

 Then step through the operation to see the order of events.   That will give you a clue as to what is triggering what.

 You can then use the bypass flag technique to avoid re-executing code.

One of the things with Access and bound forms is that the more you try to do and control the flow of things, the more problems you run into (ie. setting Me.Dirty true to force a save).

 Another thing that gets a lot of people is in a main/subform combination, when you move to the subform control, the main form record is saved.  That throws a lot of people off.

 If you have that setup and focus is moving into a subform, that could be firing off a whole chain of events that your not aware of.

 Also watch out for any timer events you may have on the form or in other forms that are open.   They will execute in the background and carry out operations, which can mess up current operations.

Jim.
0
 

Author Comment

by:gdunn59
ID: 39312368
Jim,

I put BreakPoints (STOPS) as you suggested above, and was able to determine that the After_Update Event of the Audit Notes field would run the lines of code up to the following line "docmd.runcommand accmdSelectRecord" and then it would run the code for the "Form_BeforeUpdate Event", then it would run the code for the "Audit_Notes_Exit Event", which was setting the focus to the next field "Action_Required".  I removed the following Code for the Audit_Notes_Exit Event, and that resolved the issue:

Removed Code:

Private Sub Audit_Notes_Exit(Cancel As Integer)
On Error GoTo Err_Handler

DoCmd.Echo False

'DoCmd.RunCommand acCmdRefreshPage

Me.Action_Required.SetFocus

DoCmd.Echo True

Exit_Handler:
    Exit Sub

Err_Handler:
    DoCmd.CancelEvent
    Resume Exit_Handler
    
End Sub

Open in new window


Thanks for your assistance!

gdunn59
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

This article will show you how to use shortcut menus in the Access run-time environment.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now