Code Organization Advice

[dan_l]

Hi,

This is all an Access based project, but this really more about the VBA side.

Here's what I've got:
-Class that manages a series of reports out of access.
-A Class that emails the files exported to a bunch of users.

General program flow:
A header procedure loops through a table containing report parameters (query names, table names, etc), and does all the work via the first class. The first class passes some details over to the second class which generates emails, adds attachments, etc.

So. Each email body is a little different. I try to be as generic as possible but a lot of them require customization. So I'd say of 15 to 20 outputs, about half of them have a unique email.

My question:
How do I best store these texts?

Right now I've got the email written in a horrible, maintenance intensive function:

function GetEmailString(sRepName as string)
dim sresult as string
select case srepname
   case report1
        sresult = Report1Text
   case report2
        sresult = Report2Text
end select

get emailstring = sresult
end function


function report1text()
      report1text = "The quick brown fox jumped over the lazy dog"
end function

function report2text()
     report2text = "blah blah blah"
end function

But I think there has to be a better way. How would you guys approach this?

 

[Chihiro]

Personally, I'd store text in Table and query using key/ID.

 

[dan_l]

Hmmmmm.....

Can I store HTML in the table?

 

[Chihiro]

Can I store HTML in the table?

While you can. I wouldn't recommend it, as it can cause a lot of headaches down the line. Rather store texts into appropriate column/field and construct HTML string via code.

 

[Chihiro]

Alternately, instead of storing constant string as Function. Just declare it as constant.

Public Const report1text As String = "The quick brown fox jumped over the lazy dog"

 

[dan_l]

Yeah I played with that idea for a while. I came across a few limitations to doing that. This conversation has given me some ideas though.

Thanks.

 

[Hui]

If you want to store the formatting why not use an extra field in the Table
then add a number to define each format combination
eg: using a 16 bit character you can store store 16 fields in 1 character 0-9 & a-f

1st bit bold
2nd bit italics
3rd bit underlined
etc

then use a translation table to reform the format at the end

 

[dan_l]

Emmmmm...

Do you have a example of this? Like

00001 Hello /00001 = hello

?

If you want to store the formatting why not use an extra field in the Table
then add a number to define each format combination
eg: using a 16 bit character you can store store 16 fields in 1 character 0-9 & a-f

1st bit bold
2nd bit italics
3rd bit underlined
etc

then use a translation table to reform the format at the end

 

[Hui]

Can you go back a step and explain what you are doing and why?
Maybe attach a sample file

 

[dan_l]

My samples are back in the office! I'll get it to you tomorrow.

So basically:
I've got a single access database. There's some fairly simple code that runs a bunch of stored queries, creates some new tables, and then sends the tables out to various users. So for each output table, there's a separate email that that goes out.

Anyway, the text of the email can be different for each email.

"Dear people, here's your stupid data"

"Dear other people, here's some more data, please do something about it"

Whatever. The complexity of these strings can vary a little bit. A few have simple tables set up. Some are super generic.

From the class that's driving the report, there's properties like .EmailSubject, .EmailBody, .EmailTo. .EmailBody actually calls the function above to determine what should be in the email body.

So the question is: how do I organize all of these various email texts. What I have there is pretty maintenance intensive. I'm trying to see if there's a better way.

 

[Hui]

So setup a macro to
Run a query and extract the data to an Excel file
Save the file
Setup an email and attach the file

you should be able to find examples of each of these steps here or on the web
Then just put it all together

The Queries, People and Email text could all be stored in a control file where the macro resides
The macro could be setup to run all queries in a list or have a field to enable or disable that particular query if required

 

[dan_l]

Yes. I've got it. It's been in use for about 6 months now.

But the quirk is, I have this horrible kind of setup for managing the email texts and I'm trying to think of a way to do it:

function GetEmailString(sRepName as string)
dim sresult as string
select case srepname
   case report1
        sresult = Report1Text
   case report2
        sresult = Report2Text
end select

get emailstring = sresult
end function


function report1text()
      report1text = "The quick brown fox jumped over the lazy dog"
end function

function report2text()
     report2text = "blah blah blah"
end function

The above is pretty simple. Some get more complicated. IE

"Dear user, your report is value is " & lvalue

or One of the more complex ones inserts a chart that was exported earlier in the routine.


So, I guess on a "best practice" basis, I'm not sure what the right answer is on how to keep all of these email texts stored efficiently. Unless of course that's just the best way at this point.

 

[Chihiro]

It's pretty difficult to give help without knowing your process flow.

From starting point of master module, how does the process diverges and if there's any common process among sub procedures etc.

Typically, I'd store string variables in a table and retrieve them through function. Instead of storing them as constant in a function itself.

Your second set of function is unnecessary. If you must, just keep them in single function.

function GetEmailString(sRepName as string)
dim sresult as string
select case srepname
  case report1
        sresult = "The quick brown fox jumped over the lazy dog"
  case report2
        sresult = "Blah blah blah"
end select

get emailstring = sresult
end function