Solved

Merge Data from Access for Email Merge in Word

Posted on 2008-10-31
39
276 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
Comment Utility
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.
Comment Utility
Does it have to be a word doc? Could it be an Excel SS with multipe sheets/tabs?
0
 

Author Comment

by:PatKung
Comment Utility
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
 
LVL 10

Expert Comment

by:LennyGray
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
send your mdb today and I'll do the code, if you can wait a few days.

Lenny
0
 

Author Comment

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

Author Comment

by:PatKung
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I am sure I will.

Thanks for all the help.
Pat
0
 

Author Comment

by:PatKung
Comment Utility
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
Comment Utility
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
Comment Utility
You went above and beyond!  I will learn a lot from this code of yours.
Thanks,Pat
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 10

Expert Comment

by:LennyGray
Comment Utility
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
Comment Utility
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
Comment Utility
Dim UserResponse as Integer
0
 

Author Comment

by:PatKung
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I will look forward to hear about the solution. I am here if you need me.
0
 

Author Comment

by:PatKung
Comment Utility
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
Comment Utility
Can you send me the mdb with data?
0
 

Author Comment

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

Author Comment

by:PatKung
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
The fix was relatively simple. Whenever you perform a movenext, check for an EOF.

Happy to help.
InvoiceTrackingFixed.zip
0
 

Author Comment

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

Expert Comment

by:LennyGray
Comment Utility
no problem.....post any time!

Lenny
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now