How to handle unknown number of variables?

[PNGBill]

Hi Forum, Access 2000

I am working with sending emails to customers on their loan account balances.

Have sorted out an email to cover a Loan which by definition is One Record.

Now I want to email a Customer Balance which may have more then one Loan Record.

How do you handle this scenario in vba as you can't just load the code with heaps of variables just in case they are needed.

Is this where you use an Array

I am testing Arrays with some success and a little failure but of course, how do you handle the data in the email message as again, it can have a variable number of lines/records.
Using ReDim Arrayname(intLower To intUpper) to set the Array size to suit the record number - Is this how you would do it??

I could use a table to hold the data but then we still have the issue of the email text / body.

Any advice on what direction to approach this issue would be most appreciated.

Thanks,
Bill

 

[boblarson]

Why do you need variables for this?

Are you building a body of the email with the information there? If so, just iterate through a recordset and build the string that way. That's what I do with Expired Insurance Coverages here in my job.

 

[mikeyanagita]

an array is the only way.

And you probably don't need to redim. Just set the array size to whatever the maximum amount of variables there will be. And when you loop through it later, just tell the loop to stop when it hits a blank element.

 

[PNGBill]

Why do you need variables for this?

Are you building a body of the email with the information there? If so, just iterate through a recordset and build the string that way. That's what I do with Expired Insurance Coverages here in my job.

As you loop through the recordset you add a line to the string for the email.

I think I can savi this. Have to go out for a while and will try it out when I return.
Thanks Bob

 

[PNGBill]

an array is the only way.

And you probably don't need to redim. Just set the array size to whatever the maximum amount of variables there will be. And when you loop through it later, just tell the loop to stop when it hits a blank element.

Am I correct that with Bob's suggestion, I won't need the Array.
If using Array I could set it at (6,8) because 8 is the number of "fields" and 6 would be a Max of loans. 1 and 2 is 99.9% but of course....

But with Bob's idea I just build up the email string with the loop.

Will try soon and know for sure then

 

[boblarson]

As you loop through the recordset you add a line to the string for the email.

I think I can savi this. Have to go out for a while and will try it out when I return.
Thanks Bob

Yep, let me know if you need an example. If you do I'll try to pull something together from our current system.

 

[PNGBill]

Think I have it worked out.
This code returns a msgbox with a header and multiple lines of Loan Acc Data depending on how many loans there are.

Two variables req'd.
One to collect the record data and the other to accumulate this data and to hold the column labels as the first line.

I will just have this variable, EmailBody, as part of the full email string where it fits into the syntax of the message and all should work well.

Will test on actual email code tomorrow - run out of time tonight.

Code is:

Private Sub CmdEmailAccBal_Click()
On Error GoTo Err_CmdEmailAccBal_Click
     
    Dim dbs As DAO.Database, rst As DAO.Recordset
    Dim strSQL As String
    Dim EmailText As String
    Dim EmailBody As String
   
    Dim MembID As String                    'Variable to hold Member ID
       
    MembID = Me.ADPK                    'set forms current Member ID to be variable value
    
            'SQL to Collect Loan Data for All Current Loans - Issued but not completed
 Removed SQL from code as it is quite big and not really important for the code Critique - I think. Includes all eight fields where as this sample only uses 3.
       
                   'Open Recordset
    Set dbs = DBEngine(0)(0)
    Set rst = dbs.OpenRecordset(strSQL)
    
    EmailBody = "Loan Number    Overdue Amount     Total to Pay " & Chr(13) & Chr(10)
   
    Do Until rst.EOF
    
    EmailText = "          " & rst!LoanID & "               K" & rst!LoanOverdueAmt & "                     K" & rst!LoanTotalToPay & " " & Chr(13) & Chr(10)

    EmailBody = EmailBody & EmailText
    
    rst.MoveNext
    
    Loop
    
    MsgBox EmailBody

   'Close database variables
    rst.Close
    dbs.Close

Exit_CmdEmailAccBal_Click:
    Exit Sub

Err_CmdEmailAccBal_Click:
    MsgBox Err.Description
    Resume Exit_CmdEmailAccBal_Click
    
End Sub

Appreciate any advice if above is not the best way to approach the issue.

 

[PNGBill]

I have the email populating with the correct data but I am using spaces in the string to get the columns to line up.

This works fine for constant length data but when the amounts can vary from 9,000.00 to .01 you have a field length diff of 4 chrs and a , plus the date can vary from 31st of a month to the 1st of a month which is another 1 chr.

Overall with a number of such fields, by the time we get to the last column the alignment can be 5 chrs out

What sort of "magic wand" can I wave over this issue to make my email look a little more Professional and not appear to be done by a learner in a remote third world city.

 

[bluetongue]

try something like Mystr = Right(" " & Format(5459.4, "##,##0.00"), 9) ' Returns "5,459.40".

 

[bluetongue]

oops

Mystr = Right(" " & Format(5459.4, "##,##0.00"), 9)

ignoore the comment6 ' Returns "5,459.40".

 

[boblarson]

Bill:

I use HTML and send using HTML mail. But it depends on which you and your people receiving are using. If they have HTML then you can format it using HTML tables.

Or you can use the Tab Character: Chr(9) if you want to concatenate a few of those in, but that can be a bit difficult. I sometimes will use SPACE(x) where x is a number and then I substitute x with a number and subtract the length of the previous part so that it becomes standard.

Like this

variable & Space(45 - Len(variable)) & variable2

Something like that.

 

[PNGBill]

Thanks Bob, used Space() - here is an extract.

EmailText = LoanNumberFormat(rst!LoanID) & Space(15 - Len(LoanNumberFormat(rst!LoanID))) & Format(rst!LoanPrincipal, "Currency")

Tidied the email up a lot but still not fully allowing for Currency and Date

eg 2/03/2005 is shorter then 11/03/2005 even after the Space function.
Also, K5.74 doesn't line up with K74.57

I guess the Len() function isn't an exact science as not all chrs take up the same space.

I suppose I could have the dates be mad into string and include a leading zero, if single digit day. We seldom have more then two records and 80% or more would be one record only so the email doesn't look too bad.