Solved

Merge Data from Access for Email Merge in Word

Posted on 2008-10-31
39
277 Views
Last Modified: 2013-11-28
I am putting this data out in a report in Access and am using two sub reports on my report because there are many lines of detail for one invoice.  I wish to email out a support sheet with this same information to the customers that are internal to our campus.  

Is there a way for me to do a merge and get the sub report info into a Word doc/email?  
If not is there some way for me to email each report sheet to the correct email address?  
I have only figured out how to email the whole batch to one person.

If neither of those are possible, what would be the best way to create a temporary table that has as a flat file the data I need with like 7 different fields for the description of the charge and 7 fields for the corresponding amounts of the charges.  

Hope someone can solve this one.  
Thanks, Pat

0
Comment
Question by:PatKung
  • 21
  • 17
39 Comments
 
LVL 10

Expert Comment

by:LennyGray
ID: 22853441
1.) Create a query that shows your data. Close Access
2.) Open word
3.) Select the email mailmerge wizard
4.) Use that Access query as your data source
5.) Create a word document that shows the data in the format that you want (embedded, list, etc.)
6.) The rest is managed by the wizard.

Good Luck!!

Lenny Gray
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 22853456
Does it have to be a word doc? Could it be an Excel SS with multipe sheets/tabs?
0
 

Author Comment

by:PatKung
ID: 22854236
LennyGray,
The problem is that the query would show several rows for one persons email merge, but the merge would put only one row of data per email.  The data is coming from two tables with a one to many relationship.  
If there was some way on the Word document to have an If statement for the 2nd through 7th rows of possible detail data that would keep it on this record if the MainID was the same for the next row of data.  If not it would go to the next page and put the data for that person on it and so on.  

Jimpen, Excel multiple sheets/tabs is not a good option.  

Both - Here is a snagit shot of the document that might make more sense and a fake file of data to show what I am trying to do.  


EmailMergeSupportSheet.jpg
FakeDataForMerge.xls
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 10

Expert Comment

by:LennyGray
ID: 22854840
You can do it but it will be a bit kludgy.

All you need to do is create a table that holds up to 7 different explanations and amounts (e.g. Explanation1, Amount1, Explanation2, Amount2, etc.) plus all of the other items to print (including totals). You can create a query to generate the values into that table (make sure that you delete that table every time and recreate the table again at report time).

There is another way but it is quite sophisticated. I did it last year using IncludeText in Word. But it would be a huge committment on my part to create that code.

There is an expert on this site who I had hired to generate the code for me. His name is Graham Skan and he is in the UK (I am in the US). Here is his email address: graham-skan@naturalists.co.uk

I would recommend my kludgy solution if your need is casual or minimal. If your need is a part of a large application or for large amounts of data where paying for some custom code is necessary, email Graham. He is a delightful, professional and skilled programmer. I use him a lot.

Good Luck,
Lenny Gray
0
 

Author Comment

by:PatKung
ID: 22859074
Lenny,

I will try to use your kludgy solution but not sure how to load the data into the table as a flat file.  How do I get the first sub record to go into the first explaination and first amt and so on through the 7th or how ever many there are?  There may be one or more.  I can create the table that is not the problem, but loading it is the question.  

Thanks, Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22859227
You have to write a looping sub that reads the record and checks if the previous record id is the same or different. If it is the same, it writes the value into the next set. If it is diffreent, you write the expanded record and start a new one.

Lenny
0
 

Author Comment

by:PatKung
ID: 22871754
Lenny,

I am not very good with code, is there someplace to see a sample of what you are saying I should do?

Thanks, Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22872892
send your mdb today and I'll do the code, if you can wait a few days.

Lenny
0
 

Author Comment

by:PatKung
ID: 22873280
Lenny,
That would be great!  I will send it first thing in the morning.  
Thanks, Pat
0
 

Author Comment

by:PatKung
ID: 22877127
Lenny,
Have cleaned everything out but what you would need.  Really appreciate this.  I am sure I will be using this code and logic again.  Thanks, Pat Kun
InvoiceTracking-MinimumNeeded.mdb
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22919058
Hi Pat -

I have about 80% of it finished. A death in the family took my time this weekend. I hope that you can wait for the final code.

Lenny
0
 

Author Comment

by:PatKung
ID: 22920881
Lenny,
Sorry to hear about the death in your family.  No problem waiting.  

Thanks for what you are doing.

Pat
0
 
LVL 10

Accepted Solution

by:
LennyGray earned 500 total points
ID: 22925640
Hi Pat -

Attached is the mdb. It is a flexible application that permits you to pass an unlimited number of columns and invoices.

You may have to change the Word Document from time-to-time. The input dataset for the Word merge document is: TempSupportSheetInfoAllDetail

Good Luck!!

Lenny Gray
InvoiceTracking-MinimumNeeded.mdb
0
 

Author Comment

by:PatKung
ID: 22925834
Lenny,
Wow!  That looks like a lot of work you did there.  

Is there some reason why it couldn't have just been written to the table I had setup to put it in?  Does this have to be written to a Word doc to do this type of thing?  The reason I ask is if my Word Merge document is setup for 7 possible entries and the Data has less than that there will probably be an error everytime it is not exactly 7.  If it is dumped into the table that is already setup for 7 and if there are less then the fields will be there but just null.  

If this is true then I will try to adjust your code to drop it into the Access table.

Thanks, Pat

Thanks, Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22925916
The table that you defined is a fixed schema layout. The table that I used for the output is a variable schema layout.

You can change my code using your table name if you wish. It is defined only in one place in my code anyway.

If you want to always have a fixed number of 7 events, then just change the calculation in my code for the variable for the maximum number of columns. I used a domain function to calculate the result but you could simple set the result as a constant by commenting out my calculation and replacing the variable value to 7. Again, this is a change in one place only within the code.

The program will run with no problems, if you make those changes.

I hope that you enjoy the code and find good use for it.

Best Regards,
Lenny Gray
0
 

Author Comment

by:PatKung
ID: 22925997
I am sure I will.

Thanks for all the help.
Pat
0
 

Author Comment

by:PatKung
ID: 22926305
Lenny,

I put in Option Explicit and am trying to define the variables that were not defined.  
What is  UserResponse in the line below?
UserResponse = MsgBox(sMsg, iDgDef, sTitle)

Have put the code around it attached below.
Thanks, Pat

ErrorHandler:
    If Err = 3265 Then
        sMsg = "An input table that could not be found." & vbCrLf & vbCrLf & "Hit ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        UserResponse = MsgBox(sMsg, iDgDef, sTitle)
        DesignTheOutputTable = True       'error found
        Set fld = Nothing
        Set db = Nothing
    ElseIf Err = 7874 Then
        '************************************************************
        '* you are deleting a table that does not exist. This is OK *
        '* because it may be the first time that this is being run. *
        '************************************************************
        Resume Next
    Else
        sMsg = "Unexpected error!" & vbCrLf & vbCrLf & "Please contact Pat Kung x6872 and report Microsoft Access Error Code: " & Err.Number & "." & vbCrLf & vbCrLf & "Error Description is: " & Err.Description & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        Call MsgBox(sMsg, iDgDef, sTitle)
        DesignTheOutputTable = True       'error found
        Set fld = Nothing
        Set db = Nothing
    End If

Open in new window

0
 

Author Comment

by:PatKung
ID: 22926565
Lenny,
Not sure why I was thinking you were writing the data to a Word Table.  What you have should work fine.  Thanks, Pat
0
 

Author Closing Comment

by:PatKung
ID: 31512162
You went above and beyond!  I will learn a lot from this code of yours.
Thanks,Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22927475
Pat -

I am sorry about the lack of definitions for the variables. I always use my toolkits and they have the variable types predefined.

Here are the ones that you need:
    Public Const sTitle As String = "Data management System"    ' MsgBox sTitle.
'**********************
'*   DEFINITIONS  *
'**********************
    Public iDgDef As Integer
    Public sMsg As String

Open in new window

0
 

Author Comment

by:PatKung
ID: 22931888
Lenny,

I had already figured those out, it is    UserResponse    that I am not sure how to define.

When I try to compile it complains about       UserResponse  

Thanks, Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22935861
Dim UserResponse as Integer
0
 

Author Comment

by:PatKung
ID: 22978868
Lenny,
I am not sure what exactly to change to have it not create the table but just use the one I had in there and fill it.  
I am pretty certain I just don't use the "DesignTheOutputTable" function, but not real sure how to adjust the "FillTheOutputTable" function to get it to just fill the existing table "tbl_07_TempSupportSheetInfoAllDetail".  

Would really appreciate any input you could give me.
Thanks, Pat


Public Function DesignTheOutputTable() As Boolean
Dim db As DAO.Database
Dim tdf As TableDef
Dim fld As DAO.Field
 
Dim iTheColumnCount As Integer
Dim i As Integer
 
Dim strTheWordTableName As String
Dim sqlString As String
Dim strTheDescriptionFieldName As String
Dim strTheCurrencyFieldName As String
 
Dim sMsg As String
Dim iDgDef As Integer
Dim sTitle As String
 
 
On Error GoTo ErrorHandler
    
    Set db = CurrentDb
 
    '***********************************************
    '* Determine the number of columns that you    *
    '* will create. Each time it will be different.*
    '***********************************************
    iTheColumnCount = DLookup("MaxOfCountOfChgID", "qryMaximumColumnCount")
    
    '*******************************************************************
    '* This is the table that you will use as the data source for Word *
    '* You can change the table name to whatever name you decide upon. *
    '*******************************************************************
    strTheWordTableName = "TempSupportSheetInfoAllDetail"
    
    '***********************************************************
    '* Delete the temporary table that supplies Word with data *
    '***********************************************************
    DoCmd.DeleteObject acTable, strTheWordTableName
 
    '***********************************************************
    '* Create the temporary table that supplies Word with data *
    '***********************************************************
    Set tdf = db.CreateTableDef(strTheWordTableName)
 
    '***********************************************
    '* Create the column - translations of column  *
    '* attributes are at the bottom of this module *
    '***********************************************
    With tdf
        .Fields.Append .CreateField("strEmail", dbText)
        .Fields.Append .CreateField("strTypeInv", dbText)
        .Fields.Append .CreateField("strInvNum", dbText)
        .Fields.Append .CreateField("intFY", dbInteger)
        .Fields.Append .CreateField("EventDates", dbText)
        .Fields.Append .CreateField("strEventNum", dbText)
        .Fields.Append .CreateField("strEventName", dbText)
        .Fields.Append .CreateField("Contact", dbText)
        
        For i = 1 To iTheColumnCount
            '******************************************************
            '* Change the column names to reflect the item number *
            '******************************************************
            strTheDescriptionFieldName = "strChgDesc" & i
            strTheCurrencyFieldName = "curChgAmt" & i
            
            '****************************************************
            '* create the description and currency column names *
            '****************************************************
            .Fields.Append .CreateField(strTheCurrencyFieldName, dbCurrency)
            .Fields.Append .CreateField(strTheDescriptionFieldName, dbText)
        '***********************
        '* loop until finished *
        '***********************
        Next i
    End With
    '*******************************
    '* Refresh the table structure *
    '*******************************
    db.TableDefs.Append tdf
               
    DesignTheOutputTable = False       ' no error found
    db.Close
    Set fld = Nothing
    Set db = Nothing
 
    Call FillTheOutputTable
 
    Exit Function
 
ErrorHandler:
    If Err = 3265 Then
        sMsg = "An input table that could not be found." & vbCrLf & vbCrLf & "Hit ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        'UserResponse = MsgBox(sMsg, iDgDef, sTitle)
        DesignTheOutputTable = True       'error found
        Set fld = Nothing
        Set db = Nothing
    ElseIf Err = 7874 Then
        '************************************************************
        '* you are deleting a table that does not exist. This is OK *
        '* because it may be the first time that this is being run. *
        '************************************************************
        Resume Next
    Else
        sMsg = "Unexpected error!" & vbCrLf & vbCrLf & "Please contact Pat Kung x6872 and report Microsoft Access Error Code: " & Err.Number & "." & vbCrLf & vbCrLf & "Error Description is: " & Err.Description & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        Call MsgBox(sMsg, iDgDef, sTitle)
        DesignTheOutputTable = True       'error found
        Set fld = Nothing
        Set db = Nothing
    End If
 
End Function
 
 
Public Function FillTheOutputTable()
Dim db As DAO.Database
Dim rstInput As DAO.Recordset
Dim rstOutput As DAO.Recordset
 
Dim strPrevInvNum As String
Dim strInvNum As String
Dim strTheDescriptionFieldName As String
Dim strTheCurrencyFieldName As String
 
Dim iTheColumnCount As Integer
Dim i As Integer
Dim sMsg As String
Dim iDgDef As Integer
Dim sTitle As String
 
On Error GoTo CalcErrorHandler
 
    DoCmd.SetWarnings False
       
    Set db = CurrentDb()
    Set rstInput = db.OpenRecordset("qryInputTable", dbOpenDynaset)
    Set rstOutput = db.OpenRecordset("tbl_07_TempSupportSheetInfoAllDetail")
    
    If rstInput.EOF Then
        sMsg = "Fatal Error! " & vbCrLf & vbCrLf & "There is no data to work with.  Please Contact Pat Kung immediately." & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        Call MsgBox(sMsg, iDgDef, sTitle)
        Exit Function
    End If
    
    '***********************************************
    '* Determine the number of columns that need to*
    '* be filled. Each time it will be different.  *
    '***********************************************
    iTheColumnCount = DLookup("MaxOfCountOfChgID", "qryMaximumColumnCount")
    
   
    '************************
    '* initialize variables *
    '************************.
    strPrevInvNum = rstInput!strInvNum
    i = 1
                    
    '*******************************
    '* read the first input record *
    '*******************************
    rstInput.MoveFirst
    
    '*******************************************
    '* loop through the entire input recordset *
    '*******************************************
    Do Until rstInput.EOF
    
        strInvNum = rstInput!strInvNum
        
        '***************************************
        '* Pass the data to the result dataset *
        '***************************************
        rstOutput.AddNew
        rstOutput!strEmail = rstInput!strEmail
        rstOutput!strTypeInv = rstInput!strTypeInv
        rstOutput!strInvNum = rstInput!strInvNum
        rstOutput!intFY = rstInput!intFY
        rstOutput!EventDates = rstInput!EventDates
        rstOutput!strEventNum = rstInput!strEventNum
        rstOutput!strEventName = rstInput!strEventName
        rstOutput!Contact = rstInput!Contact
        '********************************************************************
        '* There will always be at least one description and currency value *
        '********************************************************************
        rstOutput!strChgDesc1 = rstInput!strChgDesc
        rstOutput!curChgAmt1 = CCur(rstInput!curChgAmt)
        
        '********************
        '* Read next record *
        '********************
        rstInput.MoveNext
        strPrevInvNum = rstInput!strInvNum
 
        Do While strInvNum = strPrevInvNum
 
        '*********************************************
        '* check if there are other columns to write *
        '*********************************************
            i = i + 1
            '*****************************
            '* Write other column values *
            '*****************************
            strTheCurrencyFieldName = "curChgAmt" & i
            strTheDescriptionFieldName = "strChgDesc" & i
            rstOutput.Fields(strTheDescriptionFieldName).Value = rstInput!strChgDesc
            rstOutput.Fields(strTheCurrencyFieldName).Value = CCur(rstInput!curChgAmt)
            
            '********************
            '* Read next record *
            '********************
            rstInput.MoveNext
            
            If rstInput.EOF Then
                rstOutput.Update
                GoTo EndOfJob
            End If
            
            strPrevInvNum = rstInput!strInvNum
        Loop
            
        rstOutput.Update
        i = 1
        
    Loop
                                   
EndOfJob:
            rstOutput.Close
            rstInput.Close
            Set rstOutput = Nothing
            Set rstInput = Nothing
            Set db = Nothing
            
            sMsg = "Record Creation Complete! " & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
            iDgDef = vbOKOnly + vbInformation
            Beep
            Call MsgBox(sMsg, iDgDef, sTitle)
            Exit Function
        
        
CalcErrorHandler:
    sMsg = "Fatal Error! " & vbCrLf & vbCrLf & "The record creation module has failed.  Please Contact IT immediately." & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
    iDgDef = vbOKOnly + vbCritical
    Beep
    Call MsgBox(sMsg, iDgDef, sTitle)
 
End Function

Open in new window

0
 
LVL 10

Expert Comment

by:LennyGray
ID: 22981810
To play it safe and keep your options open fopr the future do this:

'*  iTheColumnCount = DLookup("MaxOfCountOfChgID", "qryMaximumColumnCount")
iTheColumnCount = 7

'* strTheWordTableName = "TempSupportSheetInfoAllDetail"
strTheWordTableName = "tbl_07_TempSupportSheetInfoAllDetail-OriginalSample"


Even though the table gets created every time, it will look the same every time. This is safer than appending into a table that you would need to remember to delete all records from before appending.

Lenny
0
 

Author Comment

by:PatKung
ID: 23433711
LennyGray
This was working fine and all of a sudden it is getting an error.  It appears it is getting it when it runs out of records and hits EOF.  If I just say OK and go on it does fine.  But I am not sure how to fix the problem.
Error:  Fatal Error!  The record createion module has failed.  Please Contact IT immediatly.  Click on "OK" to continue.

Any help would be appreciated.  
Thanks, Pat
Option Compare Database
Option Explicit
 
Public Function DesignTheOutputTable() As Boolean
Dim db As DAO.Database
Dim tdf As TableDef
Dim fld As DAO.Field
 
Dim iTheColumnCount As Integer
Dim i As Integer
 
Dim strTheWordTableName As String
Dim sqlString As String
Dim strTheDescriptionFieldName As String
Dim strTheCurrencyFieldName As String
 
Dim sMsg As String
Dim iDgDef As Integer
Dim sTitle As String
 
 
On Error GoTo ErrorHandler
    
    Set db = CurrentDb
 
    '***********************************************
    '* Determine the number of columns that you    *
    '* will create. Each time it will be different.*
    '***********************************************
    '*  iTheColumnCount = DLookup("MaxOfCountOfChgID", "qryMaximumColumnCount")
    
    iTheColumnCount = 7
    
    
    '*******************************************************************
    '* This is the table that you will use as the data source for Word *
    '* You can change the table name to whatever name you decide upon. *
    '*******************************************************************
    '*  strTheWordTableName = "TempSupportSheetInfoAllDetail"
    
    strTheWordTableName = "tbl_07_TempSupportSheetInfoAllDetail"
    
    '***********************************************************
    '* Delete the temporary table that supplies Word with data *
    '***********************************************************
    DoCmd.DeleteObject acTable, strTheWordTableName
 
    '***********************************************************
    '* Create the temporary table that supplies Word with data *
    '***********************************************************
    Set tdf = db.CreateTableDef(strTheWordTableName)
 
    '***********************************************
    '* Create the column - translations of column  *
    '* attributes are at the bottom of this module *
    '***********************************************
    With tdf
        .Fields.Append .CreateField("strEmail", dbText)
        .Fields.Append .CreateField("strTypeInv", dbText)
        .Fields.Append .CreateField("strInvNum", dbText)
        .Fields.Append .CreateField("intFY", dbInteger)
        .Fields.Append .CreateField("EventDates", dbText)
        .Fields.Append .CreateField("strEventNum", dbText)
        .Fields.Append .CreateField("strEventName", dbText)
        .Fields.Append .CreateField("Contact", dbText)
        .Fields.Append .CreateField("curChgTotal", dbCurrency)
        
        For i = 1 To iTheColumnCount
            '******************************************************
            '* Change the column names to reflect the item number *
            '******************************************************
            strTheDescriptionFieldName = "strChgDesc" & i
            strTheCurrencyFieldName = "curChgAmt" & i
            
            '****************************************************
            '* create the description and currency column names *
            '****************************************************
            .Fields.Append .CreateField(strTheCurrencyFieldName, dbCurrency)
            .Fields.Append .CreateField(strTheDescriptionFieldName, dbText)
        '***********************
        '* loop until finished *
        '***********************
        Next i
    End With
    '*******************************
    '* Refresh the table structure *
    '*******************************
    db.TableDefs.Append tdf
               
    DesignTheOutputTable = False       ' no error found
    db.Close
    Set fld = Nothing
    Set db = Nothing
 
    Call FillTheOutputTable
 
    Exit Function
 
ErrorHandler:
    If Err = 3265 Then
        sMsg = "An input table that could not be found." & vbCrLf & vbCrLf & "Hit ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        'UserResponse = MsgBox(sMsg, iDgDef, sTitle)
        DesignTheOutputTable = True       'error found
        Set fld = Nothing
        Set db = Nothing
    ElseIf Err = 7874 Then
        '************************************************************
        '* you are deleting a table that does not exist. This is OK *
        '* because it may be the first time that this is being run. *
        '************************************************************
        Resume Next
    Else
        sMsg = "Unexpected error!" & vbCrLf & vbCrLf & "Please contact Pat Kung x6872 and report Microsoft Access Error Code: " & Err.Number & "." & vbCrLf & vbCrLf & "Error Description is: " & Err.Description & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        Call MsgBox(sMsg, iDgDef, sTitle)
        DesignTheOutputTable = True       'error found
        Set fld = Nothing
        Set db = Nothing
    End If
 
End Function
 
Public Function FillTheOutputTable()
Dim db As DAO.Database
Dim rstInput As DAO.Recordset
Dim rstOutput As DAO.Recordset
 
Dim strPrevInvNum As String
Dim strInvNum As String
Dim strTheDescriptionFieldName As String
Dim strTheCurrencyFieldName As String
 
Dim iTheColumnCount As Integer
Dim i As Integer
Dim sMsg As String
Dim iDgDef As Integer
Dim sTitle As String
 
On Error GoTo CalcErrorHandler
 
    DoCmd.SetWarnings False
       
    Set db = CurrentDb()
    Set rstInput = db.OpenRecordset("tbl_06_TempSupportSheetInfo", dbOpenDynaset)
    Set rstOutput = db.OpenRecordset("tbl_07_TempSupportSheetInfoAllDetail")
    
    If rstInput.EOF Then
        sMsg = "Fatal Error! " & vbCrLf & vbCrLf & "There is no data to work with.  Please Contact Pat Kung immediately." & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
        iDgDef = vbOKOnly + vbCritical
        Beep
        Call MsgBox(sMsg, iDgDef, sTitle)
        Exit Function
    End If
    
    '***********************************************
    '* Determine the number of columns that need to*
    '* be filled. Each time it will be different.  *
    '***********************************************
    '*  iTheColumnCount = DLookup("MaxOfCountOfChgID", "qryMaximumColumnCount")
    iTheColumnCount = 7
   
    '************************
    '* initialize variables *
    '************************.
    strPrevInvNum = rstInput!strInvNum
    i = 1
                    
    '*******************************
    '* read the first input record *
    '*******************************
    rstInput.MoveFirst
    
    '*******************************************
    '* loop through the entire input recordset *
    '*******************************************
    Do Until rstInput.EOF
    
        strInvNum = rstInput!strInvNum
        
        '***************************************
        '* Pass the data to the result dataset *
        '***************************************
        rstOutput.AddNew
        rstOutput!strEmail = rstInput!strEmail
        rstOutput!strTypeInv = rstInput!strTypeInv
        rstOutput!strInvNum = rstInput!strInvNum
        rstOutput!intFY = rstInput!intFY
        rstOutput!EventDates = rstInput!EventDates
        rstOutput!strEventNum = rstInput!strEventNum
        rstOutput!strEventName = rstInput!strEventName
        rstOutput!Contact = rstInput!Contact
        '********************************************************************
        '* There will always be at least one description and currency value *
        '********************************************************************
        rstOutput!strChgDesc1 = rstInput!strChgDesc
        rstOutput!curChgAmt1 = CCur(rstInput!curChgAmt)
        rstOutput!curChgTotal = CCur(rstInput!curChgAmt)
        '********************
        '* Read next record *
        '********************
        rstInput.MoveNext
        strPrevInvNum = rstInput!strInvNum
 
        Do While strInvNum = strPrevInvNum
 
        '*********************************************
        '* check if there are other columns to write *
        '*********************************************
            i = i + 1
            '*****************************
            '* Write other column values *
            '*****************************
            strTheCurrencyFieldName = "curChgAmt" & i
            strTheDescriptionFieldName = "strChgDesc" & i
            rstOutput.Fields(strTheDescriptionFieldName).Value = rstInput!strChgDesc
            rstOutput.Fields(strTheCurrencyFieldName).Value = CCur(rstInput!curChgAmt)
            rstOutput!curChgTotal = rstOutput!curChgTotal + CCur(rstInput!curChgAmt)
            '********************
            '* Read next record *
            '********************
            rstInput.MoveNext
            
            If rstInput.EOF Then
                rstOutput.Update
                GoTo EndOfJob
            End If
            
            strPrevInvNum = rstInput!strInvNum
        Loop
            
        rstOutput.Update
        i = 1
        
    Loop
                                   
EndOfJob:
            rstOutput.Close
            rstInput.Close
            Set rstOutput = Nothing
            Set rstInput = Nothing
            Set db = Nothing
            
            sMsg = "Record Creation Complete! " & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
            iDgDef = vbOKOnly + vbInformation
            Beep
            Call MsgBox(sMsg, iDgDef, sTitle)
            Exit Function
        
        
CalcErrorHandler:
    sMsg = "Fatal Error! " & vbCrLf & vbCrLf & "The record creation module has failed.  Please Contact IT immediately." & vbCrLf & vbCrLf & "Click on ""OK"" to continue."
    iDgDef = vbOKOnly + vbCritical
    Beep
    Call MsgBox(sMsg, iDgDef, sTitle)
 
 
End Function

Open in new window

0
 
LVL 10

Expert Comment

by:LennyGray
ID: 23434178
Put a stop in the code at line 254.

In the immediate window, type ?err.description and hit enter.

Look at the error description and see why you are getting the error. If the error is not importnat, then at the immediate window type ?err.number and hit error.

Trap that error number at the first line after the error handler lable and type:

If Err = (whatever the numebr is) Then
    Resume Next
Else
      ...then insert the code starting at line 245 and ending at line 257
endif
0
 

Author Comment

by:PatKung
ID: 23434370
It is a Run-time error "438" Object doesn't support this property or method.

Now how do I tell what caused it?

Thanks, Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 23434586
You will have to put a stop at the beginning of the program and trace the steps. When you discover it, let me know.

It is probably a data type being pushed into a text or currency field.
0
 

Author Comment

by:PatKung
ID: 23434761
I will not get to work on this until Friday, but appreciate the input.  At least I will know where to start.  
Will let you know what I find.

Thanks, Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 23434794
I will look forward to hear about the solution. I am here if you need me.
0
 

Author Comment

by:PatKung
ID: 23491812
LennyGray,
It is erroring out when it moves to the next record and there isn't a next record.  I tried doing what you said but I probably don't know what I am doing and it didn't work.  
Could you help?  
Thanks, Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 23492132
Can you send me the mdb with data?
0
 

Author Comment

by:PatKung
ID: 23492431
Yes, I am in meeting so it may be in a little bit.
Thanks, Pat
0
 

Author Comment

by:PatKung
ID: 23497252
LennyGray,
Have included in the zip file the fake data database and a word document, both need to be in the same directory.  If you select on the main form that comes up, "Generate Invoice Process", on the next form choose Type Invoice "Internal", Click "Select Invoices To Generate", both invoices should have an "x" in the first column "strSelect", close that, click the "Send Support Sheet Emails" or the "Print Support Sheet".  They both have problems as they are based on the same code to generate the data in the table they are both using.  
Thanks for your help. Pat Kung

InvoiceTracking.zip
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 23497384
Hi Pat -

I will look at this tonight and your solution will be sent to you then. Sorry for the delay.

Lenny
0
 

Author Comment

by:PatKung
ID: 23501671
That works fine.  I will be back at work tomorrow and it will be good timing.
Thanks for your help.  Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 23504943
The fix was relatively simple. Whenever you perform a movenext, check for an EOF.

Happy to help.
InvoiceTrackingFixed.zip
0
 

Author Comment

by:PatKung
ID: 23511990
LennyGray,
Worked great!!   Thanks so much for your help and I learned something also.  :)
Pat
0
 
LVL 10

Expert Comment

by:LennyGray
ID: 23513689
no problem.....post any time!

Lenny
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The Selection object is designed for user interaction. It has a Range property, so it can be used in most places that a Range object can. Recorded macros must use the Selection because they are simply copying what the user is doing. A Range prope…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question