Calling a Subroutine

  • Thread starter Thread starter Sash
  • Start date Start date
S

Sash

I have 10 files that I need to export in a certain format. The format is
consistent for each of the files. My thought was to create a form and
onclick of the button, I would open the recordset for that client then call a
subroutine to export the data. Currently, I have a program that exports the
data perfectly, but I've repeated it for each client....this is no good when
I have to make a change!!!

Would this work and if so, I'm not sure how to do this. Would I set up a
Public Function? I currently have the export program set up as Public
Function RB_Export(), but I keep getting and error when I call it that it's
not a function. I'm not very familiar with Modules in Access. Any
help/suggestions would be greatly appreciated!!!
 
Public functions must be in code modules, not in form or report code. The
purpose of modules is to hold public code, i.e. code that can be called from
any form or report.

Create a new module under some name, open it through the DB window, and cut
and paste your public function from wherever it now resides into the new
module.
 
Sorry for not being clear. I did create a new module and pasted my code.
The module name is RB_Export. When I open the module I do the following....

Public Funtion RB_Export
My existing code
End Function

The form MainMenu contains buttons for each client. On click of a button,
say "Client 1", I open Client 1's recordset and call RB_Export. The specific
error message is....

Compile Error: Expected variable or procedure, not module
 
I have 10 files that I need to export in a certain format. The format is
consistent for each of the files. My thought was to create a form and
onclick of the button, I would open the recordset for that client then call a
subroutine to export the data. Currently, I have a program that exports the
data perfectly, but I've repeated it for each client....this is no good when
I have to make a change!!!

Would this work and if so, I'm not sure how to do this. Would I set up a
Public Function? I currently have the export program set up as Public
Function RB_Export(), but I keep getting and error when I call it that it's
not a function. I'm not very familiar with Modules in Access. Any
help/suggestions would be greatly appreciated!!!
Click to expand...

I'm perplexed that you have to add a new *PROGRAM* for each client. Surely you
don't have a different form for each client do you??

You should be able to just adapt the program to be more generic, passing the
client name or other client-variable data from a recordset field value rather
than hardcoding it, but since I can't visualize your structure I'm not sure
what you would need to change.
 
I renamed the function MM_Export and am now getting into the code, but
doesn't recognize the recordset that I open from the button.
 
For your public function to have access to your recordset, the recordset must
be declared as public in the Declarations section of the module. Form code
will still be able to open it.
 
Yes, this is my reason for posting. I have one form with several buttons,
one button per client. On the click of the button I'm trying to open the
recordset specific to that client and then call the "generic" program. I
realized very quickly that duplicating the program was stupid. My last post,
I'm getting to the Public Function now, but it's not realizing the recordset.
 
Yes, this is my reason for posting. I have one form with several buttons,
one button per client. On the click of the button I'm trying to open the
recordset specific to that client and then call the "generic" program. I
realized very quickly that duplicating the program was stupid. My last post,
I'm getting to the Public Function now, but it's not realizing the recordset.
Click to expand...

I'm thinking that your entire database design may be on the wrong track. So if
you add a new client, or a client moves to Tahiti, you need to redesign your
form? perhaps redesign your tables?

Please post a description of your table structure and the entire VBA in the
Click event of one of your client buttons.
 
My database is used to create a file to import into another system.
Basically, I receive a flat file from each customer that needs to be
reformatted to go into another system. Because each file is different, e.g.
some files have the patient name separated and some files require that I
parse the patient name into first, middle and last I have a separate table
for each client. If something changes with their flat file, I can address
this in their table without affecting the other clients.

I restructured my program so that when the user clicks a button on the form
for a client, I select the appropriate recordset and send the data through
the program (actual program too long to post), but below is what happens when
they click a button on the form.

If Me!Frame50.Value = 2 Then
strClient = "ABCHospital"
strSQL = "SELECT * from ABCHospital ORDER BY ID"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Close #1
Open "f:\Output\ABCHospital" & Format(Date, "mmddyyyy") & ".txt" For
Output As #1
End If

By restructuring this is working great for the client tables that contain
all the fields. My new dilemma is what if ABCHospital only has 10 out of 50
possible fields. Another Example:

Dim HD As String

HD = "HD" & Chr(9) & "111" & Chr(9)
HD = HD & rs.Fields("PTNUM") & Chr(9) & rs.Fields("PTACCT") & Chr(9)
& "A"
Debug.Print HD
Print #1, HD

If ABCHospital does not provide me with PTACCT, the program will bomb. Hope
this is making sense. Any suggestions on what to do if the field doesn't
exist in my recordset? I really need to stick with using one program for
creating my output.

Thank you!
Sash
 
My database is used to create a file to import into another system.
Basically, I receive a flat file from each customer that needs to be
reformatted to go into another system. Because each file is different, e.g.
some files have the patient name separated and some files require that I
parse the patient name into first, middle and last I have a separate table
for each client. If something changes with their flat file, I can address
this in their table without affecting the other clients.
Click to expand...

Ow. So not only is the database not normalized - by its functional design it
CAN'T be normalized. That's a pain!
I restructured my program so that when the user clicks a button on the form
for a client, I select the appropriate recordset and send the data through
the program (actual program too long to post), but below is what happens when
they click a button on the form.

If Me!Frame50.Value = 2 Then
strClient = "ABCHospital"
strSQL = "SELECT * from ABCHospital ORDER BY ID"
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
Close #1
Open "f:\Output\ABCHospital" & Format(Date, "mmddyyyy") & ".txt" For
Output As #1
End If
Click to expand...

And... at this point... you have no knowledge or much control over what fields
are implied by the *? Or does your massaging at least get the different
hospitals' tables into the same "shape", common fieldnames and fieldtypes?

By restructuring this is working great for the client tables that contain
all the fields. My new dilemma is what if ABCHospital only has 10 out of 50
possible fields. Another Example:

Dim HD As String

HD = "HD" & Chr(9) & "111" & Chr(9)
HD = HD & rs.Fields("PTNUM") & Chr(9) & rs.Fields("PTACCT") & Chr(9)
& "A"
Debug.Print HD
Print #1, HD

If ABCHospital does not provide me with PTACCT, the program will bomb. Hope
this is making sense. Any suggestions on what to do if the field doesn't
exist in my recordset? I really need to stick with using one program for
creating my output.
Click to expand...

About all I can suggest is to either put dummy, null fields in all the
different hospital tables (so that every table at least has a PTACCT field,
even if there's nothing in it); or do some really sophisticated error trapping
in your export routine so that such errors don't trip it.
 
Back
Top