Create a Macro that will Run a Module in Access 2002

[JS]

I'm trying to create a macro that will run my module in Access 2002. In
Access '97 I was able to create a macro that would open my module and run the
module using the GoContinue command. Access 2002 doesn't have the GoContinue
command and I haven't been able to use this macro since. I have tried many
different commands but no luck. Any suggestions would be appreciated!

 

[Steve Schapel]

JS,

There is no GoContinue "command" in Access 97 either. Maybe this was a
user-defined function within the specific Access 97 application you were
using?

There is a RunCode macro action which may be applicable to your needs.

 

[JS]

Steve,

I created a procedure code that imports data from Word into a table in
Access. In '97 I was able to use the GoContinue (F5) to bring up the dialog
box. I created a button that opened the module, ran the module (F5) and then
it hid the background only displaying the dialog box to type the file that I
was importing from Word.

The problem is the procedure does not have a function name. I'm using the
GetWordData procedure so I can't use the RunMacro code because of that. I
don't know how to write the VB to get the procedure to run off of a button.
The code I got for this procedure was from an issue in Microsoft Access
Element K journal back in 2001. I was able to modify the code changing the
field names.

Thanks,
JS

 

[Steve Schapel]

JS,

I am sorry, I'm afraid I can't help with this. I have never heard of
GoContinue or GetWordData, and I can't really imagine what you are doing
based on the information you have given. All I can suggest in general
terms is that you could look at using the RunCode action in a macro if
you are trying to trigger a VBA function. The
microsoft.public.access.externaldata newsgroup may also be a suitable
forum to seek help.

 

[JS]

Steve,

Basically to run this now, I open the module and press F5 to run. When the
module is run, a dialog box pops up with a message asking what form do you
want to import (from Word) and then pulls the info from file I typed in the
pop up box and imports it to an Access table (tblContracts). This is a
sample of the code I have that I'm trying to run from a button.


Option Compare Database
Option Explicit

Sub GetWordData()
Dim appWord As Word.Application
Dim doc As Word.Document
Dim rst As DAO.Recordset
Dim strDocName As String
Dim blnQuitWord As Boolean

On Error GoTo ErrorHandling

strDocName = "C:\Contracts\" & _
InputBox("Enter the name of the Word contract " & _
"you want to import:", "Import Contract")

Set appWord = GetObject(, "Word.Application")
Set doc = appWord.Documents.Open(strDocName)

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\My Documents\" & _
"Healthcare Contracts.mdb;"
rst.Open "tblContracts", cnn, _
adOpenKeyset, adLockOptimistic

With rst
.AddNew
!FirstName = doc.FormFields("fldFirstName").Result
!LastName = doc.FormFields("fldLastName").Result
!Company = doc.FormFields("fldCompany").Result
!Address = doc.FormFields("fldAddress").Result
!City = doc.FormFields("fldCity").Result
!State = doc.FormFields("fldState").Result
!ZIP = doc.FormFields("fldZIP1").Result & _
"-" & doc.FormFields("fldZIP2").Result
!Phone = doc.FormFields("fldPhone").Result
!SocialSecurity = doc.FormFields("fldSocialSecurity").Result
!Gender = doc.FormFields("fldGender").Result
!BirthDate = doc.FormFields("fldBirthDate").Result
!AdditionalCoverage = _
doc.FormFields("fldAdditional").Result
.Update
.Close
End With
doc.Close
If blnQuitWord Then appWord.Quit
cnn.Close
MsgBox "Contract Imported!"

Cleanup:
Set rst = Nothing
Set cnn = Nothing
Set doc = Nothing
Set appWord = Nothing
Exit Sub
ErrorHandling:
Select Case Err
Case -2147022986, 429
Set appWord = CreateObject("Word.Application")
blnQuitWord = True
Resume Next
Case 5121, 5174
MsgBox "You must select a valid Word document. " _
& "No data imported.", vbOKOnly, _
"Document Not Found"
Case 5941
MsgBox "The document you selected does not " _
& "contain the required form fields. " _
& "No data imported.", vbOKOnly, _
"Fields Not Found"
Case Else
MsgBox Err & ": " & Err.Description
End Select
GoTo Cleanup
End Sub

 

[Steve Schapel]

JS,

I believe this should work just the same in Access 2002 as it did in
Access 97. Certainly the functionality of the F5 key hasn't changed, so
I am puzzled as to what the problem is. Anyway, here are a couple of
suggestions. You could change the first line of the code from...
Sub GetWordData()
to...
Public Function GetWordData
.... and then End Function at the end instead of End Sub. Then you could
use this in a RunCode macro.
Probably the easiest approach, though, would be to copy/paste this code
to the Click event of a Command Button on a form.

 

[JS]

Thank you, thank you! I got it to work with your help! You have no idea how
much time I have spent on researching. No one at my location has been able
to assist. You made my day.

 

[Steve Schapel]

JS,

I am happy to know you have got it working now.