Module Question

[Sash]

From a form, I reference a module that allows the user to select files,
renames them, kicks of a batch file that runs Monarch to strip data and load
back into database. This works great, but real basic question....

How do I take the user from the Module, back to the code in my form. I need
to do this because once the aforementioned is complete, I need to run the
data through a program that generates a text file to be loaded into another
system.

So I'd be going from Form -> Module -> Form. Oh and I created the module
because I have 5 clients who I need to process the files in this manner and
the program to accomplish the above is too big to copy over and over...plus
that would be a nightmare to maintain.

Hope this makes sense. Thank you for any input. This Newsgroup is the best
group of knowledgeable people and have save me many many time.

 

[Clifford Bass]

Hi Sash,

What do you mean by "I reference a module"? What is this module? Code
you have written in a regular Access module? Something different?

If you are talking about a subroutine or function you have written in a
regular Access module, just use the name of the subroutine/function. So if
you have this in a module:

Public Sub MySubroutine()

' Code that does something

End Sub

Or

Public Function MyFunction() As Integer

' Code that does something

' Assign a value to MyFunction, which will be its return value
MyFunction = intSomeIntegerValue

End Function

And you have a button on your form with an On Click event, just use the
subroutine/function name:

Private Sub Button1_OnClick()

' Code that does something

' Run the code in MySubroutine
MySubroutine

' Get a value back from MyFunction
intReturnValue = MyFunction

' More code that does something

End Sub

Hope this helps,

Clifford Bass

 

[Sash]

Let me take another stab. Right now on my form I have (this is very
general). I have a form and code that I build in an Access Module.

Private Sub Frame50_AfterUpdate

Code to identify client selected and if client is 222, 240, 260, 322, 340,
360 then

SpecialProcess() 'This special prococess runs a program to process files,
'in a unique way but returns nothing

X - if it's a different client proceeed through a bunch of code on this form

My problem is how do I get the SpecialProcess program to go back to point X
in the form.

 

[Clifford Bass]

Hi Sash,

What type of program precisely is SpecialProcess()? VBA code elsewhere
in the database? I see no reason that the code following it should not be
executed afterwards as would be expected? Have you placed a break point in
the code on the SpecialProcess() line, then tested to see what would happen.
To add a break point, click in the left margin of the code window. A large
dot will appear. When testing, Access will stop at that point and display
the code window. Press Shift-F8 to execute the current line, which should
run SpecialProcess(). When SpecialProcess() is done it should show the code
again and highlight the following code line. If SpecialProcess() is
asynchronous, that is it starts up and returns immediately, even though still
running, the code following it may get executed right away, before anything
much has happened in SpecialProcess(). To check for that, add another break
point, this time on the line immediately after SpecialProcess(). Does the
code break there while SpecialProcess() is still running?

If none of this helps, you are going to have to be more specific.

Hope that helps,

Clifford Bass

 

[Sash]

One more try. I'm sorry, but it's just way too much code to copy and paste.

MAIN FORM:

Public Sub Frame50_AfterUpdate

** After clicking a button in a frame, my code to identifies what client was
selected. If the client is 222, 240, 260, 322, 340,
360

then I go to....

SpecialProcess()
** This "special process" has the user select file 1, file 2 and file 3 and
renames each file and runs a batch file that kicks off Monarch to strip data
from the 3 reports and import them into the database. (No other client files
requires such an in-depth process) This process is called from the form
soley to select the files, run Monarch and import the files.

After the data is importedt, I take the data imported (whether though my
"special process" or a simple import) and run it though an intricate program
to process the data into a very specific User Identified Format to be
imported into another system. I archive the file and create an import file.

So I'm trying to figure out how I can go from selecting and identifying the
client on the form, to my module that selects multiple files and imports
them, then back to the form to complete processing the file.

Thank you so much for assisting me and I hope this is a better explanation.

 

[Clifford Bass]

Hi Sash,

I do not need all the code, what you supply is fine. What I do not
understand is why it does not work as you desire. When you execute code in
another module from within a subroutine, by calling a subroutine in that
other module, the code execution, barring errors in the called subroutine,
will resume at the line after the call to the subroutine in the first
subroutine. So, if you have this:

Public Sub Frame50_AfterUpdate

If [client is one of 222, 240, 260, 322, 340, 360] Then
SpecialProcess
' Code to complete the processing of the file
End If

End Sub

The "Code to complete the processing of the file" will run after
SpecialProcess. In case you do not know this; as you can see, to run the
SpecialProcess subroutine, you simply place its name on a separate line
within your code. If SpecialProcess has parameters you include those on the
line:

SpecialProcess Parameter1, Parameter2

That is all you have to do. I have been assuming that this is what
you were doing because of your code sample. Is that what you have tried? If
so, are you saying that it does not work?

Clifford Bass

 

[Sash]

Clifford,

Thank you so much for all your time. This is what I was doing, but somehow
it wasn't making it back to the form. I've taken a different approach
because my change was due to the customer Monday. I'm wondering if it's
because the place where I call the module is in a Case statement. I'll
research further because this would be very helpful in the future.

Thanks again,
Robin

 

[Clifford Bass]

Hi Robin,

You are welcome. There is something specific going on there that is
causing it not to "return". I would suggest placing a break point early in
the process, probably near the beginning, and when it breaks stepping through
the code to see exactly what is happening and why. If you are not familiar
with break points and stepping through code try searching for "debug" and
"trace" in the VBA Editor online help.

Good Luck!

Clifford Bass