Application focus problems between access and word 1

[magicmandan]

Hi,

I have an extremely frustrating problem. I have developed an Access database (Office 2007 on Windows 7).
I want to allow users to spell and grammar check certain information that they enter onto the system.
As Access has no native grammar checker I found and adapted the following code that uses the Microsoft Word Grammar and spelling dialog which is perfect for my user needs.

The function is as follows:

Public Function SpellIt(ckStr As String, boxChecked As String) As String
On Error GoTo SpellIt_Err

Dim oldStr As String
Dim objWord As Word.Application
Dim doc As Word.Document
Dim uCancel As Boolean
Dim bChecked As String

bChecked = boxChecked
oldStr = ckStr

Set objWord = CreateObject("Word.Application")


With objWord
.visible = False

Set doc = .Documents.Add
.Selection.Text = ckStr

.visible = True
.Activate
.visible = False

.Dialogs(wdDialogToolsSpellingAndGrammar).Show

If Len(.Selection.Text) <> 1 Then
ckStr = .Selection.Text
uCancel = False
Else
'ckStr = oldStr
uCancel = True
End If


' close the document
doc.Close wdDoNotSaveChanges
Set doc = Nothing
' close the Word instance
.Quit wdDoNotSaveChanges
End With


'objWord.Quit wdDoNotSaveChanges

Set objWord = Nothing


If uCancel = True Then
SpellIt = oldStr
Dialog.Box "You cancelled the spell checker for " & UCase(bChecked) & ", please make sure you manually check the spelling and grammar for this entry before saving the record.", , "::Spellcheck Cancelled::", , , 0
Exit Function

End If

SpellIt = Replace(ckStr, Chr(13), Chr(13) & Chr(10))

Dialog.Box "Spelling and Grammar Check Complete for text contained in '" & UCase(bChecked) & "'.", vbInformation, ":Spelling And Grammar Completed:", , , 0

Exit Function

SpellIt_Err:
Err.Clear
Dialog.Box "There was an error performing the grammar and spelling check for the text in " & UCase(bChecked) & ".\n" & _
"As a precaution, any changes made within the grammar and spelling dialog box have not been retained please correct any mistakes manually.", _
vbCritical, "Error Message Box: Spelling and Grammar Check NOT Complete:", , , 0
SpellIt = oldStr
End Function

===================================

This is called by clicking a button on a modal pop-up form as shown below:

Private Sub cmdSP_Click()

If IsNull(Me.txtNotes) Or Me.txtNotes = "" Then
Else
Me.txtNotes = SpellIt(Me.txtNotes, "Notes")
End If

End Sub

====================================

While developing this solution at home it worked perfectly on my Windows XP PC, however when i tried the database onsite users reported that the application would hang whenever the spellcheck button was clicked.

I have discovered that access is not hanging but is in fact waiting for the user to interact with the word spelling and grammar dialog box which is hidden under the access database window. If i open task manager i see an entry for the spelling and grammar dialog in the applications tab - and if clicked it will then float above the open access form as it should in the first place.

The most frustrating part of this problem is that today while on site i experimented with the spellit function by changing the order of .activate and .visible = true/false and reached the point where the above code word properly - OH JOY!!

But alas no!
Having thought I'd solved the problem I re-secured the database (locked code from viewing, re-setup my custom ribbon, etc) and then started the application again and tried to use the spell check functionality - the same issue occured - the dialog box was hiding in the background.

However if i then open the calling form in design mode, close it and then reopen it, the spelling dialog is shown as it should be!!!

I am so lost with this behaviour can anyone please suggest a solution.

Thanks.

 

[magicmandan]

Having just tried to reproduced the steps involved in it working properly and then not working i have found the following situation...


I open the database, navigate and open an approriate form which has the spell check functionality - click on the button and the dialog is not shown - i then have to open task manager and double click on the spelling and dialog item to interact with it.

If i then switch the calling form to design mode - open the VBA editor - close the VBA editor - close the form - and then re-open the form and press the spell check button it actually works as it should!!!!!!!

Why oh why is this happening??

 

[HughLerwill]

Time is short and I've had no time to test properly but may be;
...
.Visible = True
.Activate
AppActivate "Document1 - " & .Caption
.Visible = False

.Dialogs(wdDialogToolsSpellingAndGrammar).Show
...
will get the dialog 'on top'

 

[HughLerwill]

But I was testing (a little) in VB6 and you may not have AppActivate available in Access VBA, see you later...

 

[magicmandan]

Thanks Hugh,

Sorry for the delay in responding but had to wait until i was back on site as the original code words fine on home PC.

AppActivate is indeed available for VBA. And after a couple of tries it eventually worked.

If anyone else could shed some light as to why the original code did not give focus to the spelling dialog (unless i had previously opened the VBA editor) it would be helpful.

Thanks again.