Import, compare, edit and upload data

I need to develop an Access application that involves importing Access tables/text files and comparing the imported data with existing data.

The user receives weekly data in the form of Access tables in one .mdb file. The 3 tables they receive are:

1 Customer Details and Account Info
2 Billing info
3 Account Balance info

Table 1 contains general customer details (name, address, etc.) and their account information (type, tariffs, active, cancelled, etc.). Every week the user receives all records, ie the accounts he had from the last week and any new accounts. Previous accounts may have certain info changed (address changes, account status changes, etc.). The user needs to be able to automatically import the weekly data and then compare it with the existing data from last week. He must be able to see separately i) all the records that have changed and ii) the new records. He should be able to edit these records and then add/update them to his main data.

Table 2 contains bills info that have been generated to each account. Again, every week the user receives all records, ie all bills generated from day 1 (plus new bills). Previous records may have small changes. The user needs to be able to automatically import the weekly data and then compare it with the existing data from last week. He must be able to see separately i) all the records that have changed and ii) the new records. He should be able to edit these records and then add/update them to his main data.

Table 3 contains the Account balance info. Each account's balance changes regularly so the weekly data must update the data that the user already has. The user needs to be able to automatically import the weekly data and see all the new accounts added. He should be able to edit these records and then add them to his main data. With this table, as well as keeping the updated info, the user wants to keep the balances for the last 3 months, so that he can see the balance changes during the last 3 months.


Can you please give me some good ideas of how to go about doing this and some code examples of importing the data, comparing the data, displaying diferences, editing the data, uploading the edited data to main tables, keeping the last 3 months data for table 3, etc.

Thanks!
AnnuAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chuck WoodCommented:
Hi Annu,

This is a fairly complex application. I suggest you break it down into the features you want. For example:

1. The user receives a database weekly with updates, which includes three tables. Feature: The user can import the data into the application.  Question: Could the user place the update database into a defined place (forlder) and link to the tables from the application? (This would save space in the application an eliminate the need to clear out data each week.)

2. Feature: The user can compare the existing Customer Details and Account Info data to the new data, viewing records where data has changed and new records.

3. Feature: The user can edit the existing Customer Details and Account Info records, replace them with the changed records where desired, and import new records into the existing table where desired.

4. Feature: The user can compare the existing Billing Info data to the new data, viewing records where data has changed and new records.

5. Feature: The user can edit the existing Billing Info records, replace them with the changed records where desired, and import new records into the existing table where desired.

6. Feature: The user can view new records of the Account Balance Info data.

7. Feature: The user can import the new Account Balance Info records into the existing table.

Does this look to be about what you are planning?

Chuck
0
AnnuAuthor Commented:
Thanks  Chuck.

Ans:
1 Yes
2 Yes
3 Yes
4 Yes
5 Yes
6 Yes
7 Yes, but he must be able to keep old data for upto 3 months

So, Yes, I guess :)
0
Chuck WoodCommented:
OK. Then you would add:

8. Feature: The user can delete all the Account Balance Info records over three months old.

On 1. Was this Yes to the feature or yes to the question or both?
(Question: Could the user place the update database into a defined place (forlder) and link to the tables from the application?)

Assuming it was Yes to both:

Implement Feature 1 by:
1. Create a new Access Database.
2. Decide on a folder for the weekly update database.
3. Move or copy the latest weekly update database into the folder.
4. In the Tables objects area of your new database:
    a. Select File menu => Get External Data => Link Tables.
    b. Navigate to the folder where the weekly update database is located and select it.
    c. Click the Select All button then click the OK button.
5. In the Queries objects area of your new database:
    a. Click the New button at the top of your database window.
    b. Select Design view and click the OK button.
    c. Select the first of the linked tables and click the Add button.
    d. Click the Close button.
    e. Double-click the title of the table you just added to select all of the fields.
    f. Click on the group of highlighted fields and drag them down to the first Field row in the field list just below.
    g. Select Query menu => Make Table Query.
    h. Type in the name of the linked table plus Local (e.g. if your linked table is tblCustomers, name your new table tblCustomersLocal). Or you can use your own naming convention.
    i. Click the OK button.
    j. Select Query Menu => Run.
    k. When the dialog box informs you that your are about to paste xx rows into a new table, click the OK button.
    l. Repeat a through k for each of the other two tables.

You now have a database that has linked tables from the weekly update database and an initial set of data in three local tables. If this satisfies your first feature (or if you have any problems) please let me know.

Chuck
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AnnuAuthor Commented:
Chuck, I had a little re-think about Feature 1.
I think it would be better if the user could import the weekly data into temporary holding tables that are in the main application. This makes the whole application self-contained and we can then 'move' that application where ever we want. Linking the weekly tables from a defined folder would mean changing code/parameters if they had to move the application/folder, etc.

So can we import the weekly tables to temp tables in the main application, then empty them when finished. Thanks and sorry for changing my mind!
0
AnnuAuthor Commented:
Also, the user needs to have 'buttons' some where to do this.
0
Chuck WoodCommented:
No problem. That is the purpose of clearly defining each feature.
Here is the rewrite for feature 1:

Implement Feature 1 by:
1. Create a new Access Database..
2. In the Tables objects area of your new database:
    a. Select File menu => Get External Data => Import
    b. Navigate to the folder where the weekly update database is located and select it.
    c. Click the Select All button then click the OK button.
3. In the Queries objects area of your new database:
    a. Click the New button at the top of your database window.
    b. Select Design view and click the OK button.
    c. Select the first of the imported tables and click the Add button.
    d. Click the Close button.
    e. Double-click the title of the table you just added to select all of the fields.
    f. Click on the group of highlighted fields and drag them down to the first Field row in the field list just below.
    g. Select Query menu => Make Table Query.
    h. Type in the name you whish to use for your new table.
    i. Click the OK button.
    j. Select Query Menu => Run.
    k. When the dialog box informs you that your are about to paste xx rows into a new table, click the OK button.
    l. Close the query without saving it.
    m. Repeat a through l for each of the other two tables.
4. In the Forms objects area of your new database:
    a. Create a new form.
    b. Add a button to that form.

When you have this done, let me know the name of the tables you imported from the weekly update database and I will work with you on writing the code behind the button.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

I need to get this project ready by Wednesday, but I can only work on it on the weekends and early morning weekdays (before 10am).

So I will do most of my work this weekend. Will you be available for help duirng the weekend? If not, could you put up the steps for the rest of the features and I wll do as much as possible over the weekend. That would be very kind of you.

I am based in the UK.

Kind regards.
0
AnnuAuthor Commented:
OK Chuck, so far so good. I have completed the steps.

Tables imported:
1 Assignments
2 Bills
3 Balances

Corresponding tables created by me:
1 tblAssignments
2 tblBills
3 tblBalances

Thanks!
0
Chuck WoodCommented:
Hi Annu,

Sorry I didn't get back to you sooner but I was swamped with work on Friday. If you have created the form with the button on it, you can continue with feature 1 by:

5. Create three temporary tables by copying a table and pasting it with 'Structure Only.'
   a. tblAssignmentsTemp
   b. tblBillsTemp
   c. tblBalancesTemp

6. Create a new module (I call mine basFileOpenSave) and paste the code below into it (this code provides a standard File Open or File Save dialog box).

Option Compare Database
Option Explicit

'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
' Code courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996

Type tagOPENFILENAME
    lStructSize As Long
    hwndOwner As Long
    hInstance As Long
    strFilter As String
    strCustomFilter As String
    nMaxCustFilter As Long
    nFilterIndex As Long
    strFile As String
    nMaxFile As Long
    strFileTitle As String
    nMaxFileTitle As Long
    strInitialDir As String
    strTitle As String
    Flags As Long
    nFileOffset As Integer
    nFileExtension As Integer
    strDefExt As String
    lCustData As Long
    lpfnHook As Long
    lpTemplateName As String
End Type

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Function ahtCommonFileOpenSave( _
            Optional ByRef Flags As Variant, _
            Optional ByVal InitialDir As Variant, _
            Optional ByVal Filter As Variant, _
            Optional ByVal FilterIndex As Variant, _
            Optional ByVal DefaultExt As Variant, _
            Optional ByVal FileName As Variant, _
            Optional ByVal DialogTitle As Variant, _
            Optional ByVal hwnd As Variant, _
            Optional ByVal OpenFile As Variant) As Variant
' This is the entry point you'll use to call the common
' file open/save dialog. The parameters are listed
' below, and all are optional.
'
' In:
' Flags: one or more of the ahtOFN_* constants, OR'd together.
' InitialDir: the directory in which to first look
' Filter: a set of file filters, set up by calling
' AddFilterItem. See examples.
' FilterIndex: 1-based integer indicating which filter
' set to use, by default (1 if unspecified)
' DefaultExt: Extension to use if the user doesn't enter one.
' Only useful on file saves.
' FileName: Default value for the file name text box.
' DialogTitle: Title for the dialog.
' hWnd: parent window handle
' OpenFile: Boolean(True=Open File/False=Save As)
' Out:
' Return Value: Either Null or the selected filename
Dim OFN As tagOPENFILENAME
Dim strFileName As String
Dim strFileTitle As String
Dim fResult As Boolean
    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = CurDir
    If IsMissing(Filter) Then Filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(Flags) Then Flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = ""
    If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
    If IsMissing(OpenFile) Then OpenFile = True
    ' Allocate string space for the returned strings.
    strFileName = Left(FileName & String(256, 0), 256)
    strFileTitle = String(256, 0)
    ' Set up the data structure before you call the function
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = hwnd
        .strFilter = Filter
        .nFilterIndex = FilterIndex
        .strFile = strFileName
        .nMaxFile = Len(strFileName)
        .strFileTitle = strFileTitle
        .nMaxFileTitle = Len(strFileTitle)
        .strTitle = DialogTitle
        .Flags = Flags
        .strDefExt = DefaultExt
        .strInitialDir = InitialDir
        ' Didn't think most people would want to deal with
        ' these options.
        .hInstance = 0
        '.strCustomFilter = ""
        '.nMaxCustFilter = 0
        .lpfnHook = 0
        'New for NT 4.0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With
    ' This will pass the desired data structure to the
    ' Windows API, which will in turn it uses to display
    ' the Open/Save As Dialog.
    If OpenFile Then
        fResult = aht_apiGetOpenFileName(OFN)
    Else
        fResult = aht_apiGetSaveFileName(OFN)
    End If

    ' The function call filled in the strFileTitle member
    ' of the structure. You'll have to write special code
    ' to retrieve that if you're interested.
    If fResult Then
        ' You might care to check the Flags member of the
        ' structure to get information about the chosen file.
        ' In this example, if you bothered to pass in a
        ' value for Flags, we'll fill it in with the outgoing
        ' Flags value.
        If Not IsMissing(Flags) Then Flags = OFN.Flags
        ahtCommonFileOpenSave = TrimNull(OFN.strFile)
    Else
        ahtCommonFileOpenSave = vbNullString
    End If
End Function

Function ahtAddFilterItem(strFilter As String, _
    strDescription As String, Optional varItem As Variant) As String
' Tack a new chunk onto the file filter.
' That is, take the old value, stick onto it the description,
' (like "Databases"), a null character, the skeleton
' (like "*.mdb;*.mda") and a final null character.

    If IsMissing(varItem) Then varItem = "*.*"
    ahtAddFilterItem = strFilter & _
                strDescription & vbNullChar & _
                varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String
Dim intPos As Integer
    intPos = InStr(strItem, vbNullChar)
    If intPos > 0 Then
        TrimNull = Left(strItem, intPos - 1)
    Else
        TrimNull = strItem
    End If
End Function

7. Create a Reference (code window => Tools menu => References) to Microsoft ActiveX Data Objects 2.x Library.

8. In the same module, paste the following code at the bottom of the module (this code will be used later to clear  your temp tables).

Public Sub ClearTable(Table As String)
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    ' clear the table
    Dim strSQL As String
    strSQL = "DELETE * FROM " & Trim(Table)
    cnn.Execute strSQL
End Sub


9. In the code behind the button (cmdButtonName_Click), paste the following code.

    ' set the filter
    Dim strFilter As String
    strFilter = ahtAddFilterItem(strFilter, "Access files (*.mdb)", "*.mdb")
    strFilter = ahtAddFilterItem(strFilter, "All files (*.*)", "*.*")
    ' select filter 1 (Excel file) when the dialog opens
    Dim intFilterIndex As Integer
    intFilterIndex = 1
    ' set up the initial directory
    Dim strInitDir As String
    strInitDir = "C:\" ' replace with your initial directory
    ' set up the default extension
    Dim strDefaultExt As String
    strDefaultExt = "mdb"
    ' set up the default file name
    Dim strFileName As String
    strFileName = "C:\Example.mdb"
    ' open the dialog box and get the file path and name
    Dim strFile As String
    strFile = ahtCommonFileOpenSave(, strInitDir, strFilter, intFilterIndex, _
        strDefaultExt, strFileName, , , False)
    ' if there is a file path and name returned,
    If Not IsNull(strFile) And Len(Trim(strFile)) > 0 Then
        Dim cnn As ADODB.Connection
        Set cnn = CurrentProject.Connection
        ' clear the assignments temp table
        ClearTable "tblAssignmentsTemp"
        ' import all the data from the weekly update assignments table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblAssignmentsTemp SELECT * FROM [" & strFile & "].Assignments"
        cnn.Execute strSQL
        ' clear the bills temp table
        ClearTable "tblBillsTemp"
        ' import all the data from the weekly update bills table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblBillsTemp SELECT * FROM [" & strFile & "].Bills"
        cnn.Execute strSQL
        ' clear the balances temp table
        ClearTable "tblBalancesTemp"
        ' import all the data from the weekly update balances table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblBalancesTemp SELECT * FROM [" & strFile & "].Balances"
        cnn.Execute strSQL
    End If

This should finish up feature 1. Please let me know when you have this working, or if you have problems.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck. Stil testing. Little problem, though. In the code below have you repeated this statement: Dim strSQL As String? Can you just use it once and 'overwrite' the strSQL value each time without the Dims?

If Not IsNull(strFile) And Len(Trim(strFile)) > 0 Then
        Dim cnn As ADODB.Connection
        Set cnn = CurrentProject.Connection
        ' clear the assignments temp table
        ClearTable "tblAssignmentsTemp"
        ' import all the data from the weekly update assignments table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblAssignmentsTemp SELECT * FROM [" & strFile & "].Assignments"
        cnn.Execute strSQL
        ' clear the bills temp table
        ClearTable "tblBillsTemp"
        ' import all the data from the weekly update bills table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblBillsTemp SELECT * FROM [" & strFile & "].Bills"
        cnn.Execute strSQL
        ' clear the balances temp table
        ClearTable "tblBalancesTemp"
        ' import all the data from the weekly update balances table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblBalancesTemp SELECT * FROM [" & strFile & "].Balances"
        cnn.Execute strSQL
    End If
0
AnnuAuthor Commented:
Chuck, I commented out the extra Dim statements and clicked the button. Nothing happened? Am I doing something wrong?

Thanks.
0
Chuck WoodCommented:
Hi Annu,

Yes, there was only supposed to be one Dim strSQL As String statement. Sorry, I was working before 6:00 AM and didn't have time to test it all.

1. Can you post the complete code for the button's Click event?

2. Did you compile the code (Code window => Debug menu => Compile ...)?

3. Do I understand correctly that you do not see the dialog box?

Chuck
0
AnnuAuthor Commented:
Below is the code for the Click event. The problem is here, I think:

If Not IsNull(strFile) And Len(Trim(strFile)) > 0 Then

becuase the execution gives no file path and name and so code after this is skipped.

Private Sub cmdImport_Click()
   
    ' set the filter
    Dim strFilter As String
    strFilter = ahtAddFilterItem(strFilter, "Access files (*.mdb)", "*.mdb")
    strFilter = ahtAddFilterItem(strFilter, "All files (*.*)", "*.*")
   
    ' select filter 1 (Excel file) when the dialog opens
    Dim intFilterIndex As Integer
    intFilterIndex = 1
   
    ' set up the initial directory
    Dim strInitDir As String
    strInitDir = "D:\My Data" ' replace with your initial directory
   
    ' set up the default extension
    Dim strDefaultExt As String
    strDefaultExt = "mdb"
   
    ' set up the default file name
    Dim strFileName As String
    strFileName = "D:\My Data\20050418.mdb"
   
    ' open the dialog box and get the file path and name
    Dim strFile As String
    strFile = ahtCommonFileOpenSave(, strInitDir, strFilter, intFilterIndex, _
        strDefaultExt, strFileName, , , False)

   
    ' if there is a file path and name returned,
    If Not IsNull(strFile) And Len(Trim(strFile)) > 0 Then
       
        Dim cnn As ADODB.Connection
        Set cnn = CurrentProject.Connection
       
        ' clear the assignments temp table
        ClearTable "tblAssignmentsTemp"
       
        ' import all the data from the weekly update assignments table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblAssignmentsTemp SELECT * FROM [" & strFile & "].Assignments"
        cnn.Execute strSQL
       
        ' clear the bills temp table
        ClearTable "tblBillsTemp"
       
        ' import all the data from the weekly update bills table to the assignments temp table
        'Dim strSQL As String
        strSQL = "INSERT INTO tblBillsTemp SELECT * FROM [" & strFile & "].Bills"
        cnn.Execute strSQL
       
        ' clear the balances temp table
        ClearTable "tblBalancesTemp"
       
        ' import all the data from the weekly update balances table to the assignments temp table
        'Dim strSQL As String
        strSQL = "INSERT INTO tblBalancesTemp SELECT * FROM [" & strFile & "].Balances"
        cnn.Execute strSQL
       
    End If

End Sub
0
AnnuAuthor Commented:
Chuck, I always get

strFile = ""

so the last IF statement does not get executed.
0
Chuck WoodCommented:
OK, I see a couple of mistakes I made:

1. Change
        strFileName = "D:\My Data\20050418.mdb"
    To:
        strFileName = "20050418.mdb"
    (take out the path and leave just the file name"

2. Change:
        strFile = ahtCommonFileOpenSave(, strInitDir, strFilter, intFilterIndex, _
            strDefaultExt, strFileName, , , False)
    To:
        strFile = ahtCommonFileOpenSave(, strInitDir, strFilter, intFilterIndex, _
            strDefaultExt, strFileName, , , True)
    (change the False at the end to True)

Try it again and see if you get a file and path in strFile.

Chuck
0
AnnuAuthor Commented:
Same problem, Chuck. I still get

strFile = ""

??
0
Chuck WoodCommented:
Do you see an Open File dialog box?

Chuck
0
AnnuAuthor Commented:
No
0
Chuck WoodCommented:
1. Did you put the code that starts with:

'--------------------------------------------------------
Option Compare Database
Option Explicit

'This code was originally written by Ken Getz.
'It is not to be altered or distributed,
'except as part of an application.
'You are free to use it in any application,
'provided the copyright notice is left unchanged.
'
'--------------------------------------------------------

in a module?

2. Did you compile the code Code windwo => Debug => Complie ...)?

Chuck
0
AnnuAuthor Commented:
Yes, I did all that. I even followed the execution through the module.

Is it something to do with this bit:

' select filter 1 (Excel file) when the dialog opens
    Dim intFilterIndex As Integer
    intFilterIndex = 1

Thanks Chuck.
0
Chuck WoodCommented:
Actually, this code:

    ' select filter 1 (Excel file) when the dialog opens
    Dim intFilterIndex As Integer
    intFilterIndex = 1

sets the filter to the first filter in this code (which is *.mdb):

   ' set the filter
    Dim strFilter As String
    strFilter = ahtAddFilterItem(strFilter, "Access files (*.mdb)", "*.mdb")
    strFilter = ahtAddFilterItem(strFilter, "All files (*.*)", "*.*")

When I adapted the code to your use, I should have changed the comment to:

    ' select filter 1 (Access file) when the dialog opens

But the comment will not change anything.

When the dialog box opens:
1. What is the title of the dialog box?
2. What is the path shown in the dialog box?
3. What is the default file shown in the dialog box?
4. Can you find and select your .MDB file?

Chuck
0
AnnuAuthor Commented:
The dialog box doesn't even open, Chuck. Nothing happens.
0
Chuck WoodCommented:
OK. Try this:

1. Find this piece of code:

    strFile = ahtCommonFileOpenSave(, strInitDir, strFilter, intFilterIndex, _
        strDefaultExt, strFileName, , , False)

2. Place your cursor in the code.

3. Press the F9 key (the code should be highlighted with what looks to me like a brown color).

4. Click the button on your form.

5. When the execution stops at the code you highlighted (the highlight should be yellow),
    a. Open the Immediate window (Ctrl + G).
    b. Type the following in the Immediate window (without the numbers, staring with the question mark), followed by pressing the Enter key in each case:
        (1) ?strInitDir
        (2) ?strFilter
        (3) ?intFilterIndex
        (4) ?strDefaultExt
        (5) ?strFileName

6. Copy the results of all of this from the Immediate window and save it into a file (Notepad, Word, etc.) on your desktop so you can find it later.

7. Press the F8 key and step into the ahtCommonFileOpenSave code.

8. Continue pressing the F8 key until you see if there is someplace in the ahtCommonFileOpenSave code that kicks you out and returns you to the cmdImport_Click code.

I will be leaving for home soon (7 minutes) but I will try to check back on this issue from home.

Chuck
0
AnnuAuthor Commented:
Didn't get kicked out of ahtCommonFileOpenSave code. Execution went through the whole code of ahtCommonFileOpenSave code. Last lines to get executed were

ahtCommonFileOpenSave = vbNullString
    End If
End Function

Values in immediate window were (I changed InitDir to D:\Test and moved 20050418.mdb there):

?strInitDir
D:\Test
?strFilter
Access files (*.mdb) *.mdb All files (*.*) *.*
?intFilterIndex
 1
?strDefaultExt
mdb
?strFileName
20050418.mdb

Thanks Chuck.
0
Chuck WoodCommented:
Hmmm...
Never saw that before.

(Sorry I didn't respond last night but my work computer logs me in here automatically and I had forgotten my password)

Apparently this function is not working:

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

1. What operating system are you using?

2. What version of access are you running?

3. Where is the comdlg32.dll located (do Start => Search => For Files or Folders...)?

Chuck
0
AnnuAuthor Commented:
BINGO!!!

I am at work now and am using WinXP with Access 2000. It works here! I can see the dialog box with my databases!

At home I was using Win2K with Access 2000. It didn't work there.

My clients have WinXP and Office 2003, so that should be OK. Any ideas about why it doesn't work on Win2K?

One more little thing: the weekly databases have a pasword on them (usually the same every week) so we need to be able to import using the password.

Thanks Chuck! Looks like we are getting there. I meet them tomorrow!
0
Chuck WoodCommented:
The only thing I can suggest is that the comdlg32.dll file or the comdlg32.ocx file is either missing, unregistered, or the wrong version.

1. Missing: You should have a COMDLG32.DLL version 5.0.3700.xxx in your C:\WINNT\system32 folder.
                 You should also have a comdlg32.ocx version 6.0.84.xx in your C:\WINNT\system32 folder.

2. Unregistered: Using notepad, paste the following command into a file named reg.cmd:
                            regsvr32 "C:\WINNT\system32\comdlg32.ocx"
                        Then double click on the file. You should see a dialog box that says:
                            DllRegisterServer in C\WINNT\system32\comdlg32.ocx succeeded.

3. Wrong version: If the first two steps don't work, obtain the correct version of the .DLL and .OCX and place them in the C:\WINNT\system32 folder.

I will have to do some research on the password issue.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck!

If you have problems with the password issue, I could get my clients to manually export the 3 tables into a new .mdb file and then import from there. This would temporarily solve our problem.
0
AnnuAuthor Commented:
Hi Chuck.

I have version 5.0.3315.3727 for my COMDLG32.DLL on my home Win2K PC. How do I update it?

Thanks.
0
AnnuAuthor Commented:
Are you stuck on the password thing Chuck? If so, please just leave it for now so that we can get the other main features done first.

Thanks.
0
Chuck WoodCommented:
Hi Annnu,

You asked: "I have version 5.0.3315.3727 for my COMDLG32.DLL on my home Win2K PC. How do I update it?"

1. If you have a comdlg32.ocx file in your C:\WINNT\system32 folder, you probably just need to register it.
                         Using notepad, copy and paste the following command into a file named reg.cmd:
                            regsvr32 "C:\WINNT\system32\comdlg32.ocx"
                        Then double click on the file reg.cmd. You should see a dialog box that says:
                            DllRegisterServer in C\WINNT\system32\comdlg32.ocx succeeded.
                        Then reboot your machine.

2. If you don't have a comdlg32.ocx file in your C:\WINNT\system32 folder, you need to find one that is already installed on a Win2K machine and copy it into the folder. Then follow the steps in item 1 above to register it.

I am working on the password thing and should have something shortly, I hope.

Chuck
0
Chuck WoodCommented:
Hi Annu,

I believe this will work to overcome the password. Find the line:
    ' if there is a file path and name returned,
And replace it and everything after it with this:

    ' if there is a file path and name returned,
    If Not IsNull(strFile) And Len(Trim(strFile)) > 0 Then
        Dim strMyUserID As String, strMyPassword As String, strcnnIn As String
        ' *** you must put your user id and password to the remote database in these variables ***
        strMyUserID = "YourUserID"  ' replace with correct user id
        strMyPassword = "YourPassword"  ' replace with correct password
        ' create the connection string
        strcnnIn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & _
            ";User ID=" & strMyUserID & ";Password=" & strMyPassword & ";"
        Dim cnnIn As New ADODB.Connection
        ' open the cnnInection to the remote database
        cnnIn.Open strcnnIn
        ' create the connection to this database
        Dim cnnOut As ADODB.Connection
        Set cnnOut = CurrentProject.cnnInection
        ' create a recordset for this database
        Dim rstOut As New ADODB.Recordset
        Dim avarRecords() As Variant
        ' open the remote database
        Dim rstIn As New ADODB.Recordset, strSQL As String
        Dim lngRow As Long, lngCol As Long
        With rstIn
            ' get the data from the assignments table
            strSQL = "SELECT * FROM Assignments"
            .Open strSQL, cnnIn, adOpenKeyset, adLockReadOnly
            If Not .EOF Then
                ' get the data and put it in the array
                avarRecords = rst.GetRows()
                ' clear the assignments temp table
                ClearTable "tblAssignmentsTemp"
                ' open the local database assignments temp table
                With rstOut
                    strSQL = "SELECT * FROM tblAssignmentsTemp"
                    .Open strSQL, cnnOut, adOpenDynamic, adLockOptimistic
                    ' loop through the input data rows
                    For lngRow = 0 To UBound(avarRecords, 2)
                        ' add a new record for each row
                        .AddNew
                            ' loop through the input data columns
                            For lngCol = 0 To UBound(avarRecords, 1)
                                ' put the input data into the field table
                                .Fields(lngCol) = avarRecords(lngCol, lngRow)
                            Next lngCol
                        ' update to save the record
                        .Update
                    Next lngRow
                    .Close
                End With
            End If
            .Close
            ' get the data from the bill table
            strSQL = "SELECT * FROM Bills"
            .Open strSQL, cnnIn, adOpenKeyset, adLockReadOnly
            If Not .EOF Then
                ' get the data and put it in the array
                avarRecords = rst.GetRows()
                ' clear the bills temp table
                ClearTable "tblBillsTemp"
                ' open the local database bills temp table
                With rstOut
                    strSQL = "SELECT * FROM tblBillsTemp"
                    .Open strSQL, cnnOut, adOpenDynamic, adLockOptimistic
                    ' loop through the input data rows
                    For lngRow = 0 To UBound(avarRecords, 2)
                        ' add a new record for each row
                        .AddNew
                            ' loop through the input data columns
                            For lngCol = 0 To UBound(avarRecords, 1)
                                ' put the input data into the field table
                                .Fields(lngCol) = avarRecords(lngCol, lngRow)
                            Next lngCol
                        ' update to save the record
                        .Update
                    Next lngRow
                    .Close
                End With
            End If
            .Close
            ' get the data from the balances table
            strSQL = "SELECT * FROM Balances"
            .Open strSQL, cnnIn, adOpenKeyset, adLockReadOnly
            If Not .EOF Then
                ' get the data and put it in the array
                avarRecords = rst.GetRows()
                ' clear the balances temp table
                ClearTable "tblBalancesTemp"
                ' open the local database balances temp table
                With rstOut
                    strSQL = "SELECT * FROM tblBalancesTemp"
                    .Open strSQL, cnnOut, adOpenDynamic, adLockOptimistic
                    ' loop through the input data rows
                    For lngRow = 0 To UBound(avarRecords, 2)
                        ' add a new record for each row
                        .AddNew
                            ' loop through the input data columns
                            For lngCol = 0 To UBound(avarRecords, 1)
                                ' put the input data into the field table
                                .Fields(lngCol) = avarRecords(lngCol, lngRow)
                            Next lngCol
                        ' update to save the record
                        .Update
                    Next lngRow
                    .Close
                End With
            End If
            .Close
        End With
        Set rstIn = Nothing
    End If

Chuck
0
Chuck WoodCommented:
Annu,

By everything after it, I mean within the Private Sub cmdImport_Click sub (just to the End Sub).

Chuck
0
AnnuAuthor Commented:
Hi Chuck.

Re COMDLG32.DLL on my home Win2K PC: I do have a comdlg32.ocx file in my C:\WINNT\system32 folder and I did what you said to register it and I got the succeed message but I still can't see the dialog box.??
0
AnnuAuthor Commented:
Hi Chuck

Just tested your code for password.

I get the following error after clicking the button:
Cannot start your application. The workgroup information file is missing or opened exclusively by another user.

The error occurs on the line: cnnIn.Open strcnnIn

By the way, the weekly databases DO NOT HAVE A User ID. They only have a password, so I put
strMyUserID = ""
in your code.

Thanks Chuck.
0
Chuck WoodCommented:
Hi Annu,

You wrote: "Re COMDLG32.DLL on my home Win2K PC: I do have a comdlg32.ocx file in my C:\WINNT\system32 folder and I did what you said to register it and I got the succeed message but I still can't see the dialog box.??"

Did you reboot your machine?

You wrote: "Cannot start your application. The workgroup information file is missing or opened exclusively by another user."

You may have to open the weekly update database before running the code. I will look for a solution to that problem.

You wrote: "By the way, the weekly databases DO NOT HAVE A User ID. They only have a password"

I _think_ you have to use:

strMyUserID = "admin"

I would try both of them.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

I can't remember whether I rebooted. I will try again. Also, if I try to replace the COMDLG32.DLL file with one from the internet (later version) windows doesn't let me; it always says 'file in use...'.

I tried using  strMyUserID = "admin"  but same problem.

I tried opening my database too, but same problem.
0
AnnuAuthor Commented:
Hi Chuck. I think you might be quite busy.

I had a meeting yesterday and I have persuaded them to give me till Monday. I just wanted to know if you can help me out to get it done by Monday. We can leave the password issue out because it is very simple for them to just transfer the tables into a non-password protected mdb file (for the time-being).

I am willing to give you another 500 points (that's 1,000 points in total) because I am really desparate.

But I must get it working by Monday morning. I can also use another WinXP PC (not my home one on which we are having problems with the COMDLG32.DLL file), so that should make things easier.

Do you think you can accept my offer? I would be really grateful if you could, Chuck.

Thanks.

0
AnnuAuthor Commented:
Hi Chuck. Please read my previous comment before you read this.

OK, I did some testing.
I moved the weekly tables to a new db that has no pasword.
Then I ran your OLD code:
-------------------------------------
' if there is a file path and name returned,
    If Not IsNull(strFile) And Len(Trim(strFile)) > 0 Then
       
        Dim cnn As ADODB.Connection
        Set cnn = CurrentProject.Connection
       
        ' clear the assignments temp table
        ClearTable "tblAssignmentsTemp"
       
        ' import all the data from the weekly update assignments table to the assignments temp table
        Dim strSQL As String
        strSQL = "INSERT INTO tblAssignmentsTemp SELECT * FROM [" & strFile & "].Assignments"
        cnn.Execute strSQL
.
.
.
----------------------------------------------------

THIS WORKS!! Now I am upto the point where the weekly tables have been imported into our main applications temp tables (tblAssignmentsTemp, etc.) Can we carry on from here?

I also noticed something in your NEW code for passwords (just to let you know), this line:

Set cnnOut = CurrentProject.cnnInection

Is this correct?

Thanks.
0
Chuck WoodCommented:
Hi Annu,

Sorry for the slow responses but we have been really hammered this week--it is end of month close for our accounting group, which I support.

About the points--the points really don't matter.

About Set cnnOut = CurrentProject.cnnInection -- it should be Set cnnOut = CurrentProject.Connnection (global replace glitch--thought I had caught them all).

About by Monday--This is not a simple database development project. I would have estimated it at two weeks (80 hours of full time work) if someone had asked me to develop it. That said, I am willing to help you if you want to go for it. I will post the basic steps for features 2 and 3 here this morning.  After that, I can help from work as time permits and from home on Saturday, probably after 11:00 Pacific Time.

Chuck
0
AnnuAuthor Commented:
Hey that's really kind of you Chuck! I will try to respond to your posts ASAP too. I really need to get this done.

And the extra points are my compliments! I will give them to you because it makes me feel good to do something 'extra' in return for your 'extra'.

So we go for the solution without the password for the time being, OK?

THANKS CHUCK, in capitals!!
0
Chuck WoodCommented:
Feature 2: The user can compare the existing Customer Details and Account Info data to the new data, viewing records where data has changed and new records.

You need to:
1. Create a Find Unmatched query that finds the records in the weekly Assignments download table that don't match the existing Assignments table in all significant respects. This will be the basis for viewing the changed and new records.

2. Create a query that finds all the existing Assignments table records that match the key fields in the query you created in step 1.

3. Create a form that has two list boxes, one whose RowSource is the first query and the second whose RowSource is the second query.

4. Create a second form that shows the two complete records when a matched but different record is clicked on the first form. Allow the existing record to be edited. Add a button that copies the new data to the existing record.

5. Create a thirdform that shows the new record when an unmatched record is clicked on the first form. Add a button that copies the new data to new record in the existing records table.

If you complete this, the solution can be quickly replicated for Features 4 and 6.

Chuck
0
Chuck WoodCommented:
Hi Annu,

I just realized that my last post pretty much covers feature 3 as well:

Feature 3: The user can edit the existing Customer Details and Account Info records, replace them with the changed records where desired, and import new records into the existing table where desired.

Just add:

6. Create a fourth form that shows and allows editing of an existing record when the existing record is clicked on the first form.

Again, if you complete this, the solution can be quickly replicated for Features 5 and 7.

If you need help with these, email me your database tables to cwood  -at-  wm  -dot-  com.

Chuck
0
Chuck WoodCommented:
Actually, I think the second, third, and fourth form can be combined into one multi-purpose form.

Chuck
0
AnnuAuthor Commented:
RE:
1. Create a Find Unmatched query that finds the records in the weekly Assignments download table that don't match the existing Assignments table in all significant respects. This will be the basis for viewing the changed and new records.

We have to identify the records that have ANYTHING different, so any field with changed/new data. How would the general structure of the query be?

Thanks.
0
Chuck WoodCommented:
You can use the Find Unmatched query wizard to create the query. If you need help, please email me your data tables.

Chuck
0
AnnuAuthor Commented:
I looked at the Find Unmatched query wizard, but not sure if it will show me records that are already there, but data in them has changed.

I sent the table structures to you by email. The unique fields in the tables are:

Assigments: MPANCore (it should be AccountID but sometimes the weekly updates have AccountID more than once - they mess things up, so my client has to sort them out)

Balances: AccountID

Bills: AccountID
0
Chuck WoodCommented:
Thanks. I got it.

Chuck
0
Chuck WoodCommented:
Hi Annu,

I sent you email with this information but I want to duplicate it here for anyone else who is looking for answers to the same questions:

I created the temp tables and added some fake data. I created an example query (qryUnmatchedAssignmentsTemp) to find all changed and new data. I also created another example query (qryUnmatechedAssignments) to find all related original data. I only used the AccountID, Name, Address, and Telephone for the temp example query match, but you can see how this works. You can add as many matching criteria as you wish.

The SQL view of these two queries are:

qryUnmatchedAssignmentsTemp:

SELECT tblAssignmentsTemp.*
FROM tblAssignmentsTemp LEFT JOIN Assignments ON (tblAssignmentsTemp.AccountId = Assignments.AccountId) AND (tblAssignmentsTemp.Name = Assignments.Name) AND (tblAssignmentsTemp.Address = Assignments.Address) AND (tblAssignmentsTemp.Telephone = Assignments.Telephone)
WHERE (((Assignments.AccountId) Is Null)) OR (((Assignments.Name) Is Null)) OR (((Assignments.Address) Is Null)) OR (((Assignments.Telephone) Is Null));

qryUnmatechedAssignments:

SELECT Assignments.*
FROM qryUnmatchedAssignmentsTemp LEFT JOIN Assignments ON qryUnmatchedAssignmentsTemp.CustomerId = Assignments.CustomerId;

Chuck
0
Chuck WoodCommented:
Do you need any help with the form?

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

Got the email. I am writing the queries now and I'll tell you as soon as I get it to work. I didn't understand what you meant by

3. Create a form that has two list boxes, one whose RowSource is the first query and the second whose RowSource is the second query.

so, yes, I suppose.
0
Chuck WoodCommented:
You're welcome Annu.

Try this:

1. Create a new form.
2. Place two list boxes on the form.
3. In the first list box:
    a. Right-click on the list box and select Properties.
    b. In the RowSource property, click in the property box and select qryUnmatchedAssignmentsTemp from
        the drop down to the right of the property.
    c. Close the Properties box.
4. In the second list box:
    a. Right-click on the list box and select Properties.
    b. In the RowSource property, click in the property box and select qryUnmatechedAssignments from
        the drop down to the right of the property.
    c. Close the Properties box.
5. Save the form.

Let me know if you have any problems.

Chuck
0
AnnuAuthor Commented:
OK Chuck, I'm just doing the queries, but the form seems easy. Tell you soon.
0
AnnuAuthor Commented:
Hi Chuck.

I created your query qryUnmatchedAssignmentsTemp, but I used EVERY field as matching criteria (37 of them!). First the query didn't run because Address is a Memo field, so I changed that to Text (255). Then I tan the query and it returned all the records of the new (temp) table!

I also tried the same query but this time only used the following for matching criteria:
RegistrationStatus
Address
Name
AccountId
CustomerId

and I got 41 records. That seems right, but not the first one I ran. Any ideas what I might be doing wrong?

Thanks.
0
Chuck WoodCommented:
Apparently, one (or more) of the fields are never the same. Here are some things to investigate:

1. Add one field at a time until I got all the records again.

2. Look at both opened tables side by side and try comparing each field to its counterpart.

3. Look at the table designs side by side and see if I can spot any differences that would create the mismatch (e.g. a number field that had two decimal places in one table and zero in the other).

Let me know how these work.

Chuck
0
AnnuAuthor Commented:
Good news Chuck. I got the 2 queries to work for the Balances tables. Here are the 2 queries (with the names that I gave them):

qryChangedNewBalances:
SELECT tblBalancesTemp.*
FROM tblBalancesTemp LEFT JOIN tblBalances ON ([tblBalancesTemp].[CustomerId]=[tblBalances].[CustomerId]) AND ([tblBalancesTemp].[AccountId]=[tblBalances].[AccountId]) AND ([tblBalancesTemp].[OverallBalance]=[tblBalances].[OverallBalance]) AND ([tblBalancesTemp].[ApplicableRate]=[tblBalances].[ApplicableRate]) AND ([tblBalancesTemp].[CreditVAT]=[tblBalances].[CreditVAT])
WHERE ((([tblBalances].[CustomerId]) Is Null)) Or ((([tblBalances].[AccountId]) Is Null)) Or ((([tblBalances].[OverallBalance]) Is Null)) Or ((([tblBalances].[ApplicableRate]) Is Null)) Or ((([tblBalances].[CreditVAT]) Is Null))
ORDER BY [tblBalancesTemp].[AccountId];

qryChangedNewBalances_Original:
SELECT tblBalances.*
FROM qryChangedNewBalances LEFT JOIN tblBalances ON [qryChangedNewBalances].[AccountId]=[tblBalances].[AccountId]
ORDER BY tblBalances.AccountId;

I am trying the Assignments table queries now again.

0
AnnuAuthor Commented:
Or shall I go on to the Forms next and get the Balances part to work?
0
AnnuAuthor Commented:
Hi Chuck. I tried to solve the Assignments problem:

Apparently, one (or more) of the fields are never the same. Here are some things to investigate:

1. Add one field at a time until I got all the records again.

I added one field at a time til I got to Telephone:
CustomerId
AccountId
Name
Address
Telephone

It was OK unitl I added Telephone. When I added Telephone I suddenley got too many records. Is it becuase Telephone is Null in many records?
0
AnnuAuthor Commented:
I have now created the form with 2 list boxes and used the Assignment queries as Row Sources.

But my Assignment queries still use only the above 4 criteria.
0
AnnuAuthor Commented:
Hi Chuck.

I think I found a clue to why the Assignments queries are giving so many records.
It depends on the fields that I include in the matching criteria. When a field is well-populated in all the records, then the results are correct. But if the field I include in the matching criteria is NOT well populated (ie many records with no entry in that field) then the query just gives me all the records which are NULL for that field.

I hope you understand. Any ideas what to do?
0
Chuck WoodCommented:
Hi Annu,

You said: "It was OK unitl I added Telephone. When I added Telephone I suddenley got too many records. Is it becuase Telephone is Null in many records?"

And: "It depends on the fields that I include in the matching criteria. When a field is well-populated in all the records, then the results are correct. But if the field I include in the matching criteria is NOT well populated (ie many records with no entry in that field) then the query just gives me all the records which are NULL for that field."

I think your choices are:
1. Don't include matching criteria where the field is not well populated.
2. Update the existing poorly populated fields with the data from the weekly update file.

I recommend number 2, if it is possible for you. An example of how to do that in a query (using arbitrarily selected fields) is:

UPDATE tblAssignmentsTemp
LEFT JOIN Assignments ON tblAssignmentsTemp.CustomerId = Assignments.CustomerId
SET tblAssignmentsTemp.Telephone = [Assignments]![Telephone], tblAssignmentsTemp.BankAccountNo = [Assignments]![BankAccountNo], tblAssignmentsTemp.JanValue = [Assignments]![JanValue];

You can add as many fields to update, as you want.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

I don't think that will work becuase the weekly update file doesn't have that info either. Some fields are very sparsley populated and they stay that way even after the weekly updates. For example, Telephone is an optional piece of information, so most of the time it is NULL, but my clients still want to know when a Telephone number changes or is added.

If I update all the important fields (the ones that they definitely need to see changes in) by changing NULL to, say, 'N/A', would that work? We could run the update just after importing the weekly tables.

What do you think?
0
Chuck WoodCommented:
Are the fields NULL in both tables, or are they zero-length strings ("") in one of the tables and NULL in the other?

If the field is NULL in only one table, you can update the field to a zero-length string this way (using Telephone as an example):

UPDATE Assignments SET Assignments.Telephone = ""
WHERE (((Assignments.Telephone) Is Null));

You might also make that field a zero-length string all the time by setting the default value to "".

I think this would be better than N/A, but you can use N/A if the zero-length string does not work for you.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

There is a mixture of NULL and zero-length strings ("") in both the tables.
So I suppose I have to convert all our NULLs to zero-length strings.

But the weekly updates (which get imported to tblAssignmentsTemp) may have NULLs in them. How do I get rid of these automatically during import? Do I just do what you said:
"You might also make that field a zero-length string all the time by setting the default value to ""."

to the tblAssignmentsTemp table?

I could do this for all the fields in the tblAssignmentsTemp table, coudn't I? That way I could include all fields for mathcing criteria.
0
AnnuAuthor Commented:
Hi Chuck.

Looks like it works if I change all Telephone NULL values to "" in both Assignments tables.

So I will work on this. Now we need to go to the next step, which is to create the second form:

4. Create a second form that shows the two complete records when a matched but different record is clicked on the first form. Allow the existing record to be edited. Add a button that copies the new data to the existing record.

Can you help again please. I am a bit lost. Thanks.
0
Chuck WoodCommented:
Hi Annu,

1. Create two global string variables in a module (create a new module if you don't have one):
 
    Public strFormRecordSource As String ' you can name it whatever you wish
    Public strSubFormRecordSource As String ' you can name it whatever you wish

2. a. Create a new form and base it on the new data table (I call it tblAssignmentsTemp) and select the fields you want to display on the form. Arrange the fields on the form however you wish.
    b. In the first forms Form_Load event handler, add:
        Me.RecordSource strFormRecordSource
    c. Lastly, remove the Record Source (the table) from the form and save the form.

3. a. Create a subform that is based on the original table (I call it Assignments), select the exact same field and arrange them pretty much the same was as the form in step 2.
    b. In the first forms Form_Load event handler, add:
        Me.RecordSource strSubFormRecordSource
    c. Lastly, remove the Record Source (the table) from the form. and save the form

4. Add the subform to the form and save the form.

We will work on the button to copy the new data to the existing record next.

Chuck

0
AnnuAuthor Commented:
Hi Chuck

For 2b above I did the following:
I opened the form with the 2 list boxes in design mode (I call this form frmForm1)
In the Events tab of the Form Properties I chose [Event Procedure] for On Load and used this code:
Private Sub Form_Load()

    Me.RecordSource strFormRecordSource
   
End Sub

For 2c I assume I have to remove the Record Source for the second form with the fields from tblAssignmentsTemp (I call it frmForm2).
0
AnnuAuthor Commented:
Chuck, I'm a bit confused with 2b, 2c, 3b, 3c.

2b, 3b: which is the 'first form'

Is 3c supposed to read:
Lastly, remove the Record Source (the table) from the subform and save the subform
??
0
AnnuAuthor Commented:
Hi Chuck.

Regarding updating the fields with "" to avoid NULLS. I can only do this if the fields are a Text data type. It does not work if the fields are Number or Currency or Date data type.

So I'm stuck again. My clients want to know if any field values change, but I can't use all fields for matching criteria now because of this problem. :(
0
AnnuAuthor Commented:
Hi Chuck.

OK, I created a Form called frmForm2 with a subform called sbfForm2.

frmForm2 On Load Event has: Me.RecordSource = strFormRecordSource

sbfForm2 On Load Event has: Me.RecordSource = strSubFormRecordSource

Is that OK?

Thanks.
0
Chuck WoodCommented:
Annu,

Sorry for the late response but I was ill yesterday and spent most of the day in bed. I am somewhat better today.

You asked: "2b, 3b: which is the 'first form'"

2b. Should say In the form's Form_Load event handler (meaning the form you created in 2.), add:
        Me.RecordSource strFormRecordSource

3b. Should say  b. In the subform's Form_Load event handler (meaining the sub form you created in 3.), add:
        Me.RecordSource strSubFormRecordSource

You said: "Regarding updating the fields with "" to avoid NULLS. I can only do this if the fields are a Text data type. It does not work if the fields are Number or Currency or Date data type.
So I'm stuck again. My clients want to know if any field values change, but I can't use all fields for matching criteria now because of this problem. :("

I recommend that you set Number and Currency fields to 0 (zero) and that you set their Default Value to zero as well. Alternatively, you could set them to Null in both tables.



You asked: "Is 3c supposed to read: Lastly, remove the Record Source (the table) from the subform and save the subform "
    Yes.

You asked: "OK, I created a Form called frmForm2 with a subform called sbfForm2.
frmForm2 On Load Event has: Me.RecordSource = strFormRecordSource
sbfForm2 On Load Event has: Me.RecordSource = strSubFormRecordSource
Is that OK?"
    That is exactly correct.

Chuck
0
AnnuAuthor Commented:
Oh, I'm really sorry you were not well. And sorry too to bother you when you should be resting to recover.

Are you OK to carry on? If not just tell me. Thanks a lot, Chuck.
0
AnnuAuthor Commented:
If you're not feeling too bad, Chuck I would be really grateful if we could finish it off today.

Thanks again.
0
Chuck WoodCommented:
Sorry, Annu, I was not able to check back yesterday. I know you wanted to finish this weekend but I wasn't up to it. I am well enough today to carry on. Please let me know what you want to do today.

Chuck
0
AnnuAuthor Commented:
Hi Chuck, nice to know you're up for it again. I missed the deadline, but I still have to complete the project so let's go for it!

I have made the form with subform as you told me. Next is the bit where you said:

"We will work on the button to copy the new data to the existing record next."

Thanks Chuck.
0
Chuck WoodCommented:
1. Create a button on your main form.
2. Within the button's On Click event:

'=== requires a Reference to Microsoft ActiveX Data Objects 2.x Library
Dim cnn As ADODB.Connection
Set cnn = Currentproject.Connection
Dim strSQL As String, intField As Integer
' select the new record in the sub form
strSQL = "SELECT * FROM tblAssignmentsTemp WHERE CustomerId=" & lstSubForm
Dim rstNew As New ADODB.Recordset, rstOld As New ADODB.Recordset
With rstNew
    .Open strSQL, cnn, adOpenKeyset, adLockReadOnly
    If Not .EOF Then
        ' select the old record in the main form
        strSQL = "SELECT * FROM Assignments WHERE CustomerId=" & lstMainForm
        With rstOld
            .Open strSQL, cnn, adOpenDynamic, adLockOptimistic
            ' for each field in the new record,
            For intField = 1 to 36
                ' set the field in the old record equal to the field in the new record
                .Fields(intField) = rstNew.Fields(intField)
                .Update
            Next intField
        End If
        .Close
    End With
    .Close
End With
Set rstNew = Nothing
Set rstOld = Nothing

3. Compile the code and save the form.

Chuck
0
AnnuAuthor Commented:
At the moment there are no records in the Form/Subform because we used:

frmForm2 On Load Event has: Me.RecordSource = strFormRecordSource
sbfForm2 On Load Event has: Me.RecordSource = strSubFormRecordSource

for the record sources. So we need to populate the Form/Subform first. Is that correct?
0
AnnuAuthor Commented:
Also, Chuck, can you check your End If and End With in last bit of code. I think there is some mistake.

Thanks.
0
Chuck WoodCommented:
Hi Annu,

You are quite correct.

1. Change the Form_Load events in the form and subform to:

In the form's Form_Load event handler:
         strFormRecordSource = "SELECT * FROM qryChangedNewBalances"
        Me.RecordSource strFormRecordSource

In the subform's Form_Load event handler:
        strSubFormRecordSource = "SELECT * FROM qryChangedNewBalances_Original"
        Me.RecordSource strSubFormRecordSource

2. In the previous button code, switch the End With ... End If to this code:

        .Close
        End With
    End If

Thanks for the constructive feedback.

Chuck
0
AnnuAuthor Commented:
Done.

When I open frmForm2 (the one with the subform in it) I only get records in the main form. No records in the subform, even if I move through all the reocrds in the main form.

Is this because of Link Child, Link Master properties not set?

Thanks.
0
Chuck WoodCommented:
No, you do not need to link the form to the subform.

Do you have queries named qryChangedNewBalances and qryChangedNewBalances_Original?

Chuck
0
AnnuAuthor Commented:
Yes, but I used qryChangedNewAssignmentsTest and qryChangedNewAssignments_Original because these are the ones I am working on at the moment.

If  run qryChangedNewAssignmentsTest  and qryChangedNewAssignments_Original on their own as queries, they work.
0
Chuck WoodCommented:
Right.

If you open the qryChangedNewAssignments_Original query, do you get any records?

Chuck
0
AnnuAuthor Commented:
Yes, I do.
0
AnnuAuthor Commented:
Sorry, Chuck. The records are there in the subform, they just don't move on when I move to the next record on the main form. Are they suposed to move with the main form?

Sorry, my fault.
0
Chuck WoodCommented:
Hi Annu,

No, the list box in the form and the list box in the sub form are not connected. If you want them to synchronize, the only way I know to do that is to link the *selections* in the form and sub form with On Click events for the list boxes.

Chuck
0
AnnuAuthor Commented:
OK, no probs.

By the way, I did not understand 'lstSubForm' in the button code below:

strSQL = "SELECT * FROM tblAssignmentsTemp WHERE CustomerId=" & lstSubForm

Thanks.
0
Chuck WoodCommented:
Hi Annu,

Yes, it should be the name of the list box on the sub form.

Chuck
0
AnnuAuthor Commented:
OK, just to recap:

I have created the following forms:

frmImport  -  this has the Import button that uses the Open File dialog box

frmForm1  -  this has the 2 list boxes on it (1st list boxe's Row Source is qryChangedNewAssignmentsTest, 2nd list boxe's Row Source is qryChangedNewAssignments_Original)

frmForm2  -  this is the form with the subform in it. It has Record Source qryChangedNewAssignmentsTest

sbfForm2  -  this is the subform within frmForm2. It has Record Source qryChangedNewAssignments_Original

qryChangedNewAssignmentsTest is the query that gives me the NEW and CHANGED records in the NEW data from tblAssignmentsTemp.

qryChangedNewAssignments_Original is the query that shows me the corresponding OLD records in our main table tblAssignments.

NOTE: I have used MPANCore as the unique field in tblAssignments and tblAssignmentsTemp.

Will the above change your code for this button we are making?

0
AnnuAuthor Commented:
Hi Chuck. I hope you are well.

I am still ploughing away at this project. I am upto the stage where I have a Form with 2 subforms on it. One subform shows all the changed/new records from the new tblAssignmentsTemp, and the other subform shows all the original recoords in the tblAssignments.

Now I need to be able to edit the new data and then copy the new data to the existing record.

The problem is that the new data IS NOT EDITABLE. The query we used to get the changed/new data was: qryChangedNewAssignmentsTest , which is based on your example that you sent me by email.

I would appreciate your help. Thanks.

0
Chuck WoodCommented:
Hi Annu,

I am doing better, thanks to my doctor.

You are correct. The query is not updateable because it is based on related tables.

I thought that you wanted to be able to change the original record or to copy the data from the new record to the original record. Is the data in the original record subform editable? If so, you can edit that data or we can develop a button to copy the new data to the original record. If not, we will have to fix that first.

Chuck
0
AnnuAuthor Commented:
Good to hear from you Chuck.

Sorry, maybe a bit of misunderstanding.

The user needs to be able to edit the NEW data and then copy this new/edited data over the original data.

They don't need to edit the original data.

So I have made the subform that shows the original data 'not editable'. I need the subform that shows the NEW data to be editable.

Hope that is OK. Thanks.
0
Chuck WoodCommented:
Hi Annu,

The way to make the new data editable is:
1. Make a copy of the subform that displays the new data.
2. Set its record source property to blank (nothing).
3. Create a button (Edit) on your original subform that displays the new data.
4. In the On Click event of the button, put this code:
    Dim strQuery As String
    strQuery = "SELECT * FROM tblAssignmentsTemp WHERE CustomerId=" & txtCustomerId
    ' (substitute the name of your new data table for tblAssignmentsTemp)
    ' (substitute the name of the field on your original subform that contains the CustomerId)
    DoCmd.OpenForm "frmMyNewForm", acNormal, , , , , strQuery
5. In the Form_Load event of the NEW form you copied from the original new data subform, put this code:
    Me.RecordSource = OpenArgs

When you click the Edit button, the new form will open and display the new data in an editible format. Once you have that working, we will work on copying the edited new data to the original data table.

Chuck
0
AnnuAuthor Commented:
Hi Chuck

I just got things clarified. They DON'T want to edit either the new records or the original records. So we just need to have the button to copy the new data to the original data.

But they want the subform that shows the NEW data to show the fields that have changed in a different colour.

How do I do that? Thanks.
0
Chuck WoodCommented:
Hi Annu,

Use this code on your main form to set a different colour for the fields that have change, changing the form and subform names as needed:

Private Sub Form_GotFocus()
    ' assuming the main form is frmForm2 and the the current data subform is sbfForm1 and
    ' the new/changed data subform is sbfForm2 and there are three text boxes:
    ' txtOne, txtTwo, and txtThree on each subform
    If Forms("frmForm2")("sbfForm2")("txtOne") <> Forms("frmForm2")("sbfForm1")("txtOne") Then _
        Forms("frmForm2")("sbfForm2")("txtOne").BackColor = 8454143
    If Forms("frmForm2")("sbfForm2")("txtTwo") <> Forms("frmForm2")("sbfForm1")("txtTwo") Then _
        Forms("frmForm2")("sbfForm2")("txtTwo").BackColor = 8454143
    If Forms("frmForm2")("sbfForm2")("txtThree") <> Forms("frmForm2")("sbfForm1")("txtThree") Then _
        Forms("frmForm2")("sbfForm2")("txtThree").BackColor = 8454143
End Sub


Chuck
0
AnnuAuthor Commented:
Hi Chuck.

I am still at it, believe it or not!

I have tried your code above in many Events to try and get the back color to change, but it just doesn't happen. I have followed the code and can see that the two text boxes have different values, but the code in the 'Then' bit doesn't change the back color.

Is it because the subforms show more then one record? But it should only change the active record's back color right?

Thanks.
0
Chuck WoodCommented:
Hi Annu,

1. Are your subforms lists of records or do they show one record at a time?
2. Do you have the correct names for the forms, subforms, and text boxes?
3. When you step through the code, does it execute the .Backcolor = 8454143 portion of the code?

Chuck
0
AnnuAuthor Commented:
1 Both my subforms are lists (datasheets) and I have made the subform control just big enough to show 3 records at a time
2 yes, checked these
3 yes, it seems to execute but the color does not change

I just tried putting a button on the Main form and when I click I run the code. I put a MsgBox in the 'Then' bit of the code and it worked; the MsgBox came up when the 2 text boxes had different values. Next I replaced the MsgBox back to your code to change the back color and again the color did not change.

Thanks.
0
Chuck WoodCommented:
Hi Annu,

Because your subforms are datasheets, my code won't work. Sorry. I thought you were using form views. I don't use datasheet views and so my experience with them is extremely limited.

You should be able to use the information in this:

http://office.microsoft.com/en-us/assistance/HA010448511033.aspx (click the Show All link on the right)

to format the background of your lines and fields.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck, but I have another problem after using the article above :(

The back color changes when I set the condition, but all the fields for the column change colour. So if one record has txtThree diferent to the corresponding txtThree in the original record, the whole column for txtThree has its back color changed.

Not much use.
0
AnnuAuthor Commented:
Hi Chuck.

I have decided to use Form views for the 2 subforms because they have to view only one record at a time anyway.

So now I am working on the button to copy the new data to the existing record. You gave me the code:

'=== requires a Reference to Microsoft ActiveX Data Objects 2.x Library
Dim cnn As ADODB.Connection
Set cnn = Currentproject.Connection
Dim strSQL As String, intField As Integer
' select the new record in the sub form
strSQL = "SELECT * FROM tblAssignmentsTemp WHERE CustomerId=" & lstSubForm
Dim rstNew As New ADODB.Recordset, rstOld As New ADODB.Recordset
With rstNew
    .Open strSQL, cnn, adOpenKeyset, adLockReadOnly
    If Not .EOF Then
        ' select the old record in the main form
        strSQL = "SELECT * FROM Assignments WHERE CustomerId=" & lstMainForm
        With rstOld
            .Open strSQL, cnn, adOpenDynamic, adLockOptimistic
            ' for each field in the new record,
            For intField = 1 to 36
                ' set the field in the old record equal to the field in the new record
                .Fields(intField) = rstNew.Fields(intField)
                .Update
            Next intField
        End If
        .Close
    End With
    .Close
End With
Set rstNew = Nothing
Set rstOld = Nothing


Will this add NEW records from tblAssignmentsTemp to our original Assignments table too?

Thanks Chuck.
0
AnnuAuthor Commented:
Hi Chuck.

Now that I have used Form views, I got the back color code to work. Great! Thanks!
0
Chuck WoodCommented:
Hi Annu,

You wrote: The back color changes when I set the condition, but all the fields for the column change colour.
Yeah, I was afraid of that.

You wrote: I have decided to use Form views for the 2 subforms because they have to view only one record at a time anyway. and Now that I have used Form views, I got the back color code to work.
That was a good move. I'm glad it works for you.

You wrote: Will this add NEW records from tblAssignmentsTemp to our original Assignments table too?

Yes, it should, if you have the list names (lstSubForm and lstMainForm) correct.
You may have to fully qualify the list names (Forms("frmForm2")("sbfForm2")("lstSubForm").

You should also check the tablenames in the code, as well: (tblAssignmentsTemp, Assignments)
And check that you have the correct number of fields (For intField = 1 to 36), excluding the first field. Note that the fields in a table are zero based. For example, if you have 37 fields, you would use 1 to 36.

Chuck
0
AnnuAuthor Commented:
Sorry, bit confused Chuck. What do you mean by 'list names' and lstSubForm, lstMainForm?

Do you mean text boxes? I only have text boxes on both SubForms. My MainForm only has 2 SubForm controls, nothing else.

Thanks.
0
Chuck WoodCommented:
Hi Annu,

In the code, the line that reads: strSQL = "SELECT * FROM tblAssignmentsTemp WHERE CustomerId=" & lstSubForm
refers to the lstSubForm list box. You should refer to the control (text box, combo box, list box) that contains the tblAssignmentsTemp CustomerId instead of lstSubForm.

In the code, the line that reads: strSQL = "SELECT * FROM Assignments WHERE CustomerId=" & lstMainForm
refers to the lstMainForm list box. Again, you should refer to the control that contains the Assignments table CustomerId instead of lstMainForm.

Chuck
0
AnnuAuthor Commented:
Hi Chuck.
Getting there...

I have a little off-the-track query. Maybe you can help.

I have 2 buttons on my MainForm (that displays changed and original records). They look like this

< >

These are buttons to move BOTH the SubForm records simultaneously Previous or Next.

In the code I am trying to use DoCmd.GoToRecord but don't know how to use this for SubForms. Can you help? Thanks.
0
AnnuAuthor Commented:
Hi Chuck.

Having problems with the code to copy new and changed data to the original table.
The code works fine when a record has to be CHANGED. Code does not work if record has to be ADDED.

I asked you: Will this add NEW records from tblAssignmentsTemp to our original Assignments table too?
And you said: yes, it should.

There is a slight problem here I think. The code that reads:

strSQL = "SELECT * FROM Assignments WHERE CustomerId=" & lstMainForm

will cause a rescordset to be opened WITHOUT any records (because this is a NEW record in the tblAssignmentsTemp table and has no corresponding record in the Assignments table).

How do we add the NEW records? Thanks.
0
AnnuAuthor Commented:
Also Chuck, I would rather have the button update/add ALL the records in one go, instead of one record at a time. Is that possible?

Thanks.
0
Chuck WoodCommented:
Hi Annu,

You wrote: I asked you: Will this add NEW records from tblAssignmentsTemp to our original Assignments table too?
And you said: yes, it should.

I don't know what I was thinking. It will not add new records.
To add new records, change your code to read:

strSQL = "SELECT * FROM tblAssignmentsTemp WHERE CustomerId=" & lstSubForm
Dim rstNew As New ADODB.Recordset, rstOld As New ADODB.Recordset
With rstNew
    .Open strSQL, cnn, adOpenDynamic, adLockOptimistic
    If Not .EOF Then
        ' select the old record in the main form
        strSQL = "SELECT * FROM Assignments WHERE CustomerId=" & lstMainForm
        With rstOld
            .Open strSQL, cnn, adOpenDynamic, adLockOptimistic
            If Not .EOF Then
                ' for each field in the new record,
                For intField = 1 to 36
                    ' set the field in the old record equal to the field in the new record
                    .Fields(intField) = rstNew.Fields(intField)
                Next intField
                .Update
            Else
                 .AddNew
                     For intField = 1 to 36
                         .Fields(intField) = rstNew.Fields(intField)
                     Next intField
                 .Update
            End If
        End If
        .Close
    End With
    .Close
End With

Chuck
0
Chuck WoodCommented:
Hi Annu,

You wrote: In the code I am trying to use DoCmd.GoToRecord but don't know how to use this for SubForms. Can you help?

Have you tried to use the code in the subforms?

Chuck
0
Chuck WoodCommented:
Hi Annu,

You wrote: Also Chuck, I would rather have the button update/add ALL the records in one go, instead of one record at a time. Is that possible?

It is possible. Let me think about that and I will get back to you.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck, I will start testing.

Re:
You wrote: In the code I am trying to use DoCmd.GoToRecord but don't know how to use this for SubForms. Can you help?

Have you tried to use the code in the subforms?


I would like the < > buttons to be on the MainForm, but when they are clicked I want the records in BOTH SubForms to move Next (or Previous). How would I do that?
0
Chuck WoodCommented:
Hi Annu,

Have you tried this?:

Private Sub NextButton() ' use the name of the > button
    DoCmd.GoToRecord acDataForm, "sbfForm1", acNext
    DoCmd.GoToRecord acDataForm, "sbfForm2", acNext
End Sub

Private Sub PreviousButton() ' use the name of the < button
    DoCmd.GoToRecord acDataForm, "sbfForm1", acPrevious
    DoCmd.GoToRecord acDataForm, "sbfForm2", acPrevious
End Sub

Chuck
0
AnnuAuthor Commented:
Yes, but I get an error that says 'sbfForm1 not open'
(I replaced the SubForm's correct name)
0
Chuck WoodCommented:
Hi Annu,

OK. Try this:

Private Sub NextButton() ' use the name of the > button
    Dim subOne As Form, subTwo As Form
    Set subOne = Forms!frmForm1!sbfForm1 ' use the correct names for the form and sub form
    Set subTwo = Forms!frmForm1!sbfForm2 ' use the correct names for the form and sub form
    DoCmd.GoToRecord acDataForm, subOne, acNext
    DoCmd.GoToRecord acDataForm, subTwo, acNext
End Sub

Private Sub PreviousButton() ' use the name of the < button
    Dim subOne As Form, subTwo As Form
    Set subOne = Forms!frmForm1!sbfForm1 ' use the correct names for the form and sub form
    Set subTwo = Forms!frmForm1!sbfForm2 ' use the correct names for the form and sub form
    DoCmd.GoToRecord acDataForm, subOne, acPrevious
    DoCmd.GoToRecord acDataForm, subTwo, acPrevious
End Sub

Chuck
0
AnnuAuthor Commented:
Doesn't work Chuck. These are the errors that I get:

When I use
Set subOne = Forms!frmCompareAssignments!sbfCompareChangedNewAssignments
Error
can't find the field sbfCompareChangedNewAssignments referred to in your expression

When I use
Set subOne = Forms!frmCompareAssignments!ChangedNewAssignments
(this is the name of the subform control on my main form)
Error
Type mismatch

When I use
Set subOne = Forms!frmCompareAssignments!ChangedNewAssignments.Form
Error
an expression you entered is the wrong data type for one of the arguments
This error occurs on the line DoCmd.GoToRecord acDataForm, subOne , acNext

Thanks.
0
Chuck WoodCommented:
Hi Annu,

Try this:

1. In a module, add this code toward the top of the module:
    Public subOne As Form, subTwo As Form

2. In the Form_Open event of the first subform, add this code:
    Set subOne = Me

3. In the Form_Open event of the second subform, add this code:
    Set subTwo = Me

4. Change the previous code to:

Private Sub NextButton() ' use the name of the > button
    DoCmd.GoToRecord acDataForm, subOne, acNext
    DoCmd.GoToRecord acDataForm, subTwo, acNext
End Sub

Private Sub PreviousButton() ' use the name of the < button
    DoCmd.GoToRecord acDataForm, subOne, acPrevious
    DoCmd.GoToRecord acDataForm, subTwo, acPrevious
End Sub

Chuck
0
AnnuAuthor Commented:
Thanks Chuck. I tried that, but same error again.

I used the code:

Private Sub cmdGoToNext_Click()
       
    DoCmd.GoToRecord acDataForm, subOne, acNext
    DoCmd.GoToRecord acDataForm, subTwo, acNext
       
End Sub

but I got the same error:
an expression you entered is the wrong data type for one of the arguments
This error occurs on the line DoCmd.GoToRecord acDataForm, subOne , acNext
0
Chuck WoodCommented:
Hi Annu,

I will have to try several things. Can you send me a database with the form, the sub forms, and some sample data? I will work on it and let you know a solution that works. My email is cwood _at_ wm _dot_ com.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

Sent.
0
AnnuAuthor Commented:
Hi Chuck. I hope you are well.

Have you had time to look at my problems:

1
Also Chuck, I would rather have the button update/add ALL the records in one go, instead of one record at a time. Is that possible?

You wrote: It is possible. Let me think about that and I will get back to you.

2
I would like the < > buttons to be on the MainForm, but when they are clicked I want the records in BOTH SubForms to move Next (or Previous). How would I do that?

Thanks.
0
Chuck WoodCommented:
Hi Annu,

Here is how to make the Next and Previous buttons work:

1. Set the frmCompareAssignments form Row Source to tblAssignments2Temp.
2. Create a text box on the form named txtCustomerId and set its Control Source to CustomerId.
3. In the properties for the ChangedNewAssignments sub form, set the Link Child Fields and Link Master Fields to CustomerId.
4. In the properties for the OriginalAssignments sub form, set the Link Child Fields and Link Master Fields to CustomerId.
5. In the sbfCompareOriginalAssignments form, change the Record Source to SELECT * FROM tblAssignments2;.
6. In the code for sbfCompareOriginalAssignments, in Form_Load, change the code to read  strOriginalRecordSource = "SELECT * FROM tblAssignments2;".
7. In the sbfCompareChangedNewAssignments form, change the Record Source to SELECT * FROM  tblAssignments2Temp;.
8. In the code for sbfCompareChangedNewAssignments, in Form_Load, change the code to read  strOriginalRecordSource = "SELECT * FROM tblAssignments2Temp;".
9. In the frmCompareAssignments form, in the cmdGoToNext button Click event, add this code:
Private Sub cmdGoToNext_Click()
    On Error Resume Next
    DoCmd.GoToRecord acDataForm, "frmCompareAssignments", acNext
    If Err.Number <> 0 Then
        Err.Clear
        MsgBox "You have reached the last record", vbInformation, "Last Record"
        On Error GoTo 0
        Exit Sub
    End If
    On Error GoTo 0
    If Not IsNull(Me.CurrentRecord) Then
        DisplayDifference
    End If
End Sub
10. In the frmCompareAssignments form, in the cmdGoToPrevious button Click event, add this code:
Private Sub cmdGoToPrevious_Click()
    On Error Resume Next
    DoCmd.GoToRecord acDataForm, "frmCompareAssignments", acPrevious
    If Err.Number <> 0 Then
        Err.Clear
        MsgBox "You have reached the first record", vbInformation, "First Record"
        On Error GoTo 0
        Exit Sub
    End If
    On Error GoTo 0
    If Not IsNull(Me.CurrentRecord) Then
        DisplayDifference
    End If
End Sub
11. In the Private Sub DisplayDifference code, add this code:
(Just before If Me!ChangedNewAssignments.Form!CustomerId <> Me![OriginalAssignments].Form!CustomerId )
    On Error GoTo ExitHere
(At the end of the sub, just before End Sub)
ExitHere:
    If Err.Number > 0 Then Err.Clear
    On Error GoTo 0

Chuck
0
Chuck WoodCommented:
Hi Annu,

< I would rather have the button update/add ALL the records in one go, instead of one record at a time. Is that possible?>

To do this, change the cmdUpload Click event slightly as follows:

Private Sub cmdUpload_Click()
    Dim myResult As Integer
    Dim cnn As ADODB.Connection
    Dim rstNew As New ADODB.Recordset
    Dim rstOld As New ADODB.Recordset
    Dim strSQLNew As String
    Dim strSQLOld As String
    Dim intField As Integer
    myResult = MsgBox("Are you sure you want to upload the new and changed records?", vbExclamation + vbYesNo + vbDefaultButton2, "Upload Records?")
        If myResult = 6 Then
            Set cnn = CurrentProject.Connection
            ' select the new records
            strSQLNew = "SELECT * FROM tblAssignments2Temp"
            With rstNew
                .Open strSQLNew, cnn, adOpenDynamic, adLockOptimistic
                Do While Not .EOF
                    ' select the old record that matches this new record
                    strSQLOld = "SELECT * FROM tblAssignments2 WHERE UniqueID = '" & .Fields("UniqueID") & "'"
                    With rstOld
                        .Open strSQLOld, cnn, adOpenDynamic, adLockOptimistic
                        If Not .EOF Then
                            ' for each field in the new record
                            For intField = 0 To 47
                                ' set the field in the old record equal to the field in the new record
                                .Fields(intField) = rstNew.Fields(intField)
                            Next intField
                            ' skip UniqueID and then do Notes
                            .Fields(49) = rstNew.Fields(49)
                            .Update
                        Else
                            .AddNew
                            For intField = 0 To 49
                                .Fields(intField) = rstNew.Fields(intField)
                            Next intField
                            .Update
                        End If
                    End With
                    rstOld.Close
                    .MoveNext
                Loop
            End With
            rstNew.Close
            Set rstNew = Nothing
            Set rstOld = Nothing
            MsgBox "Upload Complete", vbInformation + vbOKOnly
        End If
End Sub

Chuck
0
AnnuAuthor Commented:
Hi Chuck, thanks for that.

I have been testing the Next and Previous buttons that you sent. They work, but the problem is that now you get ALL the records to go through as both subforms are based on the tables.

Before the subforms were based on the queries, so the user only had to check the records that were new or changed (this is what they want).

So basically I need the subforms to show ONLY the new or changed records (and their originals) and then have the Next and Previous buttons work.

Is that possible?

I am checking your Update code too now.
0
AnnuAuthor Commented:
Hi Chuck, I just tested the Update code too.

Again, it is updating every single record, although it would be better if it only updated the necessary records (those that have changed or are new).

What do you think? Thanks.
0
Chuck WoodCommented:
Hi Annu,

<So basically I need the subforms to show ONLY the new or changed records (and their originals) and then have the Next and Previous buttons work.>

Here are the changes to make this work the way you want:

Private Sub cmdGoToNext_Click()
    Dim blnDifference As Boolean
    On Error Resume Next
    Do While Not blnDifference
        DoCmd.GoToRecord acDataForm, "frmCompareAssignments", acNext
        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "You have reached the last record", vbInformation, "Last Record"
            On Error GoTo 0
            Exit Sub
        End If
        blnDifference = DisplayDifference
    Loop
    On Error GoTo 0
End Sub

Private Sub cmdGoToPrevious_Click()
    Dim blnDifference As Boolean
    On Error Resume Next
    Do While Not blnDifference
        DoCmd.GoToRecord acDataForm, "frmCompareAssignments", acPrevious
        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "You have reached the first record", vbInformation, "First Record"
            On Error GoTo 0
            Exit Sub
        End If
        blnDifference = DisplayDifference
    Loop
    On Error GoTo 0
End Sub

Private Sub Form_Open(Cancel As Integer)
    Dim blnDifference As Boolean
    On Error Resume Next
    Do While Not blnDifference
        DoCmd.GoToRecord acDataForm, "frmCompareAssignments", acNext
        If Err.Number <> 0 Then
            Err.Clear
            MsgBox "You have reached the last record", vbInformation, "Last Record"
            On Error GoTo 0
            Exit Sub
        End If
        blnDifference = DisplayDifference
    Loop
    On Error GoTo 0
End Sub

Private Function DisplayDifference() As Boolean
    DisplayDifference = False
    Me!ChangedNewAssignments.Form!CustomerId.BackColor = 16777215
    Me!ChangedNewAssignments.Form!AccountId.BackColor = 16777215
    Me!ChangedNewAssignments.Form!Title.BackColor = 16777215
    Me!ChangedNewAssignments.Form!FirstName.BackColor = 16777215
    Me!ChangedNewAssignments.Form!Surname.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine1.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine2.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine3.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine4.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine5.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine6.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine7.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine8.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtCustAddrLine9.BackColor = 16777215
    Me!ChangedNewAssignments.Form!txtPostcode.BackColor = 16777215
    Me!ChangedNewAssignments.Form!Telephone.BackColor = 16777215
    Me!ChangedNewAssignments.Form!PayHow.BackColor = 16777215
    Me!ChangedNewAssignments.Form!PayWhen.BackColor = 16777215
    Me!ChangedNewAssignments.Form!MonthsPerPayment.BackColor = 16777215
    Me!ChangedNewAssignments.Form!PaymentDay.BackColor = 16777215
    Me!ChangedNewAssignments.Form!FirstPaymentDateCurrentPlan.BackColor = 16777215
    Me!ChangedNewAssignments.Form!NextPayPlanReviewDate.BackColor = 16777215
    Me!ChangedNewAssignments.Form!BankAccountName.BackColor = 16777215
    Me!ChangedNewAssignments.Form!BankAccountNo.BackColor = 16777215
    Me!ChangedNewAssignments.Form!BankSortCode.BackColor = 16777215
    Me!ChangedNewAssignments.Form!DDRefNumber.BackColor = 16777215
    Me!ChangedNewAssignments.Form!InstalmentAmount.BackColor = 16777215
    Me!ChangedNewAssignments.Form!DateONSent.BackColor = 16777215
    Me!ChangedNewAssignments.Form!JanValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!FebValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!MarValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!AprValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!MayValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!JunValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!JulValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!AugValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!SepValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!OctValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!NovValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!DecValue.BackColor = 16777215
    Me!ChangedNewAssignments.Form!MPANCore.BackColor = 16777215
    Me!ChangedNewAssignments.Form!MPANTopLine.BackColor = 16777215
    Me!ChangedNewAssignments.Form!StartDate.BackColor = 16777215
    Me!ChangedNewAssignments.Form!EndDate.BackColor = 16777215
    Me!ChangedNewAssignments.Form!RegistrationStatus.BackColor = 16777215
    Me!ChangedNewAssignments.Form!D0217Received.BackColor = 16777215
    Me!ChangedNewAssignments.Form!D0217LetterSent.BackColor = 16777215
    Me!ChangedNewAssignments.Form!TariffCode.BackColor = 16777215
    Me!ChangedNewAssignments.Form!UniqueID.BackColor = 16777215
    Me!ChangedNewAssignments.Form!Notes.BackColor = 16777215
    On Error GoTo ExitHere
    If Me!ChangedNewAssignments.Form!CustomerId <> Me![OriginalAssignments].Form!CustomerId Then
        Me!ChangedNewAssignments.Form!CustomerId.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!AccountId <> Me![OriginalAssignments].Form!AccountId Then
        Me!ChangedNewAssignments.Form!AccountId.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!Title <> Me![OriginalAssignments].Form!Title Then
        Me!ChangedNewAssignments.Form!Title.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!FirstName <> Me![OriginalAssignments].Form!FirstName Then
        Me!ChangedNewAssignments.Form!FirstName.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!Surname <> Me![OriginalAssignments].Form!Surname Then
        Me!ChangedNewAssignments.Form!Surname.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine1 <> Me![OriginalAssignments].Form!txtCustAddrLine1 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine1.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine2 <> Me![OriginalAssignments].Form!txtCustAddrLine2 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine2.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine3 <> Me![OriginalAssignments].Form!txtCustAddrLine3 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine3.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine4 <> Me![OriginalAssignments].Form!txtCustAddrLine4 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine4.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine5 <> Me![OriginalAssignments].Form!txtCustAddrLine5 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine5.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine6 <> Me![OriginalAssignments].Form!txtCustAddrLine6 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine6.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine7 <> Me![OriginalAssignments].Form!txtCustAddrLine7 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine7.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine8 <> Me![OriginalAssignments].Form!txtCustAddrLine8 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine8.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtCustAddrLine9 <> Me![OriginalAssignments].Form!txtCustAddrLine9 Then
        Me!ChangedNewAssignments.Form!txtCustAddrLine9.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!txtPostcode <> Me![OriginalAssignments].Form!txtPostcode Then
        Me!ChangedNewAssignments.Form!txtPostcode.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!Telephone <> Me![OriginalAssignments].Form!Telephone Then
        Me!ChangedNewAssignments.Form!Telephone.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!PayHow <> Me![OriginalAssignments].Form!PayHow Then
        Me!ChangedNewAssignments.Form!PayHow.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!PayWhen <> Me![OriginalAssignments].Form!PayWhen Then
        Me!ChangedNewAssignments.Form!PayWhen.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!MonthsPerPayment <> Me![OriginalAssignments].Form!MonthsPerPayment Then
        Me!ChangedNewAssignments.Form!MonthsPerPayment.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!PaymentDay <> Me![OriginalAssignments].Form!PaymentDay Then
        Me!ChangedNewAssignments.Form!PaymentDay.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!FirstPaymentDateCurrentPlan <> Me![OriginalAssignments].Form!FirstPaymentDateCurrentPlan Then
        Me!ChangedNewAssignments.Form!FirstPaymentDateCurrentPlan.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!NextPayPlanReviewDate <> Me![OriginalAssignments].Form!NextPayPlanReviewDate Then
        Me!ChangedNewAssignments.Form!NextPayPlanReviewDate.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!BankAccountName <> Me![OriginalAssignments].Form!BankAccountName Then
        Me!ChangedNewAssignments.Form!BankAccountName.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!BankAccountNo <> Me![OriginalAssignments].Form!BankAccountNo Then
        Me!ChangedNewAssignments.Form!BankAccountNo.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!BankSortCode <> Me![OriginalAssignments].Form!BankSortCode Then
        Me!ChangedNewAssignments.Form!BankSortCode.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!DDRefNumber <> Me![OriginalAssignments].Form!DDRefNumber Then
        Me!ChangedNewAssignments.Form!DDRefNumber.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!InstalmentAmount <> Me![OriginalAssignments].Form!InstalmentAmount Then
        Me!ChangedNewAssignments.Form!InstalmentAmount.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!DateONSent <> Me![OriginalAssignments].Form!DateONSent Then
        Me!ChangedNewAssignments.Form!DateONSent.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!JanValue <> Me![OriginalAssignments].Form!JanValue Then
        Me!ChangedNewAssignments.Form!JanValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!FebValue <> Me![OriginalAssignments].Form!FebValue Then
        Me!ChangedNewAssignments.Form!FebValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!MarValue <> Me![OriginalAssignments].Form!MarValue Then
        Me!ChangedNewAssignments.Form!MarValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!AprValue <> Me![OriginalAssignments].Form!AprValue Then
        Me!ChangedNewAssignments.Form!AprValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!MayValue <> Me![OriginalAssignments].Form!MayValue Then
        Me!ChangedNewAssignments.Form!MayValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!JunValue <> Me![OriginalAssignments].Form!JunValue Then
        Me!ChangedNewAssignments.Form!JunValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!JulValue <> Me![OriginalAssignments].Form!JulValue Then
        Me!ChangedNewAssignments.Form!JulValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!AugValue <> Me![OriginalAssignments].Form!AugValue Then
        Me!ChangedNewAssignments.Form!AugValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!SepValue <> Me![OriginalAssignments].Form!SepValue Then
        Me!ChangedNewAssignments.Form!SepValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!OctValue <> Me![OriginalAssignments].Form!OctValue Then
        Me!ChangedNewAssignments.Form!OctValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!NovValue <> Me![OriginalAssignments].Form!NovValue Then
        Me!ChangedNewAssignments.Form!NovValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!DecValue <> Me![OriginalAssignments].Form!DecValue Then
        Me!ChangedNewAssignments.Form!DecValue.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!MPANCore <> Me![OriginalAssignments].Form!MPANCore Then
        Me!ChangedNewAssignments.Form!MPANCore.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!MPANTopLine <> Me![OriginalAssignments].Form!MPANTopLine Then
        Me!ChangedNewAssignments.Form!MPANTopLine.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!StartDate <> Me![OriginalAssignments].Form!StartDate Then
        Me!ChangedNewAssignments.Form!StartDate.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!EndDate <> Me![OriginalAssignments].Form!EndDate Then
        Me!ChangedNewAssignments.Form!EndDate.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!RegistrationStatus <> Me![OriginalAssignments].Form!RegistrationStatus Then
        Me!ChangedNewAssignments.Form!RegistrationStatus.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!D0217Received <> Me![OriginalAssignments].Form!D0217Received Then
        Me!ChangedNewAssignments.Form!D0217Received.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!D0217LetterSent <> Me![OriginalAssignments].Form!D0217LetterSent Then
        Me!ChangedNewAssignments.Form!D0217LetterSent.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!TariffCode <> Me![OriginalAssignments].Form!TariffCode Then
        Me!ChangedNewAssignments.Form!TariffCode.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!UniqueID <> Me![OriginalAssignments].Form!UniqueID Then
        Me!ChangedNewAssignments.Form!UniqueID.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!Notes <> Me![OriginalAssignments].Form!Notes Then
        Me!ChangedNewAssignments.Form!Notes.BackColor = 65535
        DisplayDifference = True
    End If
ExitHere:
    If Err.Number > 0 Then Err.Clear
    On Error GoTo 0
End Function

Chuck
0
Chuck WoodCommented:
Hi Annu,

<Again, it is updating every single record, although it would be better if it only updated the necessary records (those that have changed or are new).>

Change the cmdUpload_Click code to:

Private Sub cmdUpload_Click()
    Dim myResult As Integer
    Dim cnn As ADODB.Connection
    Dim rstNew As New ADODB.Recordset
    Dim rstOld As New ADODB.Recordset
    Dim strSQLNew As String
    Dim strSQLOld As String
    Dim intField As Integer
    Dim blnMatch As Boolean
    myResult = MsgBox("Are you sure you want to upload the new and changed records?", vbExclamation + vbYesNo + vbDefaultButton2, "Upload Records?")
        If myResult = 6 Then
            Set cnn = CurrentProject.Connection
            ' select the new records
            strSQLNew = "SELECT * FROM tblAssignments2Temp"
            With rstNew
                .Open strSQLNew, cnn, adOpenDynamic, adLockOptimistic
                Do While Not .EOF
                    ' set the match flag to false
                    blnMatch = True
                    ' select the old record that matches this new record
                    strSQLOld = "SELECT * FROM tblAssignments2 WHERE UniqueID = '" & .Fields("UniqueID") & "'"
                    With rstOld
                        .Open strSQLOld, cnn, adOpenDynamic, adLockOptimistic
                        ' if there is a matching record,
                        If Not .EOF Then
                            ' for each field in the new record
                            For intField = 0 To 47
                                ' if the two fields do not match, set the match flag to false
                                If .Fields(intField) <> rstNew.Fields(intField) Then blnMatch = False
                            Next intField
                            ' if the notes field do not match, set the match flag to false
                            If .Fields(49) <> rstNew.Fields(49) Then blnMatch = False
                            ' if there was a non match,
                            If Not blnMatch Then
                                ' for each field in the new record
                                For intField = 0 To 47
                                    ' set the field in the old record equal to the field in the new record
                                    .Fields(intField) = rstNew.Fields(intField)
                                Next intField
                                ' skip UniqueID and then do Notes
                                .Fields(49) = rstNew.Fields(49)
                                .Update
                            End If
                        ' else, if there is no matching record,
                        Else
                            .AddNew
                            For intField = 0 To 49
                                .Fields(intField) = rstNew.Fields(intField)
                            Next intField
                            .Update
                        End If
                    End With
                    rstOld.Close
                    .MoveNext
                Loop
            End With
            rstNew.Close
            Set rstNew = Nothing
            Set rstOld = Nothing
            MsgBox "Upload Complete", vbInformation + vbOKOnly
        End If
End Sub

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

The Next and Previous buttons work now, except they don't show NEW records.
They stop at all CHANGED records, but not at any NEW records. Any idea how we can include NEW records too?
0
Chuck WoodCommented:
Hi Annu,

In the frmCompareAssignments form, in the DispayDifference function,

Change:
    On Error GoTo ExitHere
to:
    On Error GoTo ErrorHandler

Immediately after the On Error GoTo ErrorHandler line,

Change:
    If Me!ChangedNewAssignments.Form!CustomerId <> Me![OriginalAssignments].Form!CustomerId Then
        Me!ChangedNewAssignments.Form!CustomerId.BackColor = 65535
        DisplayDifference = True
    End If
    If Me!ChangedNewAssignments.Form!AccountId <> Me![OriginalAssignments].Form!AccountId Then
        Me!ChangedNewAssignments.Form!AccountId.BackColor = 65535
        DisplayDifference = True
    End If
to:
    If Me!ChangedNewAssignments.Form!CustomerId <> Me![OriginalAssignments].Form!CustomerId Then
        Me!ChangedNewAssignments.Form!CustomerId.BackColor = 65535
        DisplayDifference = True
    End If
'=== add this line =====
    On Error GoTo ExitHere
'=================
    If Me!ChangedNewAssignments.Form!AccountId <> Me![OriginalAssignments].Form!AccountId Then
        Me!ChangedNewAssignments.Form!AccountId.BackColor = 65535
        DisplayDifference = True
    End If


At the bottom of the function,

Change:

ExitHere:
    On Error GoTo 0
    If Err.Number > 0 Then Err.Clear
End Function

to:

ExitHere:
    If Err.Number > 0 Then Err.Clear
    On Error GoTo 0
    Exit Function
ErrorHandler:
    DisplayDifference = True
    Resume ExitHere
End Function

Chuck
0
AnnuAuthor Commented:
Great Chuck! I have tested the Next, Previous and Update buttons and they all work.

I really appreciate your help! Thanks!

We still have this to do:
8. Feature: The user can delete all the Account Balance Info records over three months old.

but I would like to award you 750 points for what we have done so far.

Before we start on feature 8, I would like you to help me with some reporting on the data that we have now imported into tblAssignments2. I hope you are OK to do this because you know my project pretty well now and so I think you are best to do this instead of me asking others to help. Of course, I will award you extra points for this reporting.

Basically, I need 2 reports:
Report 1
Total number of records in tblAssignments2 now
Total NEW records imported this week
Total NEW records imported this month
Total records with Start Date falling in this month
Report 2
For all previous months:
Total records with Start Date falling in each of those months
Group the above into categories of RegistrationStatus (Successful, Unsuccessful, Registering)
With the above also show how many 'Successful' HAVE End Dates
With the above also show how many 'Successful' DO NOT HAVE End Dates
With all the above also show the PayHow info for the records

If you are OK to do this, how shall we procede? Shall I increase the points of this question to 750 and award them to you and then open a new question for the reporting? Or do you want to carry on in this question? I will probably give you another 350 points for the reporting. Is that OK?

Thanks Chuck.

0
Chuck WoodCommented:
Hi Annu,

We can continue with the question.

For Report 1, you will need to create a two new tables with these fields:

tblWeekly
Week, NewRecordsThisWeek
(Format: date beginning, number)
(Example: 6/5/2005, 7)

tblMonthly
Month, , NewRecordsThisMonth
(Format: YYYYMM, number)
(Example: 200501, 23)

Then you will need to develop a way to count the new records imported.
Then you will need to develop a means to add a record/update the record for the new weekly and monthly new records.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck. This is what I have done so far:

1
Created a table called tblCounters_WeekAssignments:
ID (AutoNumber)
Week (DateTime, Format dd/mm/yyyy)
NewRecordsThisWeek (Number, Long Integer)

2
Adjusted the Upload Button code to:
Private Sub cmdUpload_Click()

    Dim myResult As Integer
    Dim cnn As ADODB.Connection
    Dim rstNew As New ADODB.Recordset
    Dim rstOld As New ADODB.Recordset
    Dim strSQLNew As String
    Dim strSQLOld As String
    Dim strSQL As String
    Dim intField As Integer
    Dim blnMatch As Boolean
    Dim NewRecordsUploaded As Long
   
    myResult = MsgBox("Are you sure you want to upload the new and changed records?", vbExclamation + vbYesNo + vbDefaultButton2, "Upload Records?")
       
        If myResult = 6 Then
            '******** I added next line
            NewRecordsUploaded = 0
            Set cnn = CurrentProject.Connection
            ' select the new records
            strSQLNew = "SELECT * FROM tblAssignments2Temp"
            With rstNew
                .Open strSQLNew, cnn, adOpenDynamic, adLockOptimistic
                Do While Not .EOF
                    ' set the match flag to false
                    blnMatch = True
                    ' select the old record that matches this new record
                    strSQLOld = "SELECT * FROM tblAssignments2 WHERE UniqueID = '" & .Fields("UniqueID") & "'"
                    With rstOld
                        .Open strSQLOld, cnn, adOpenDynamic, adLockOptimistic
                        ' if there is a matching record,
                        If Not .EOF Then
                            ' for each field in the new record
                            For intField = 0 To 47
                                ' if the two fields do not match, set the match flag to false
                                If .Fields(intField) <> rstNew.Fields(intField) Then blnMatch = False
                            Next intField
                           
                            ' if there was a non match,
                            If Not blnMatch Then
                                ' for each field in the new record
                                For intField = 0 To 47
                                    ' set the field in the old record equal to the field in the new record
                                    .Fields(intField) = rstNew.Fields(intField)
                                Next intField
                                .Update
                            End If
                        ' else, if there is no matching record,
                        Else
                            .AddNew
                            For intField = 0 To 48
                                .Fields(intField) = rstNew.Fields(intField)
                            Next intField
                            .Update
                            '******** I added next line
                            NewRecordsUploaded = NewRecordsUploaded + 1
                        End If
                    End With
                    rstOld.Close
                    .MoveNext
                Loop
            End With
            rstNew.Close
            Set rstNew = Nothing
            Set rstOld = Nothing
           
            '******** I added next 2 lines
            strSQL = "INSERT INTO tblCounters_WeekAssignments (Week, NewRecordsThisWeek) VALUES (Format(Date(),'dd/mm/yyyy'), " & NewRecordsUploaded & ");"
            cnn.Execute strSQL
           
            MsgBox "Upload Complete", vbInformation + vbOKOnly
        End If
       
End Sub

Is this OK to add a new record every upload into the tblCounters_WeekAssignments table?


Can you show me how to add the monthly records?

BTW, do we need the monthly table? Can't we work out the monthly new records from the existing data in tblCounters_WeekAssignments as we have the date (including month) for each upload?
0
Chuck WoodCommented:
Hi Annu,

It looks good but I would change NewRecordsUploaded  to lngNewRecordsUploaded  to keep it clear that it is a Long.

The only issues I see are:
1. Your week in the table is being loaded with the date. Is it true that you will only upload the new records only once a week?
2. Your count in the table is loaded with the count of this upload. If you are only uploading the new records once a week, this is not an issue

You should be able to work out the monthly new records from the existing data using the date (week) to retrieve the month's data.

If these issues are resolved (as I suspect they already are), then we should move on to retrieving the records for the week and month. Do you need help with the report?

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

I will change NewRecordsUploaded to lngNewRecordsUploaded.

Just to cover all eventualities, let's say that they did 2 uploads in the same week. Then we would have 2 records in tblCounters_WeekAssignments that would look like:
ID  Date    NewRecords
5   08/06/2005  6
6   10/06/2005  3

(I use UK date format dd/mm/yyyy)

Couldn't we make the reports to add 6+3 for that week and give us 9 new records uploaded for that week? Similar thing for that month.

Do you forsee any problems if they did more than 1 upload a week?
0
Chuck WoodCommented:
Hi Annu,

The challenge is writing a function or sub that recognizes the dates as part of the same week. I you can use a function to derive the week number like this:

' this is just to test the concept; use similar logic to return the week number
Public Sub Test()
    Dim intWeekNum As Integer
    intWeekNum = WeekNum(#6/6/2005#)
    MsgBox intWeekNum
End Sub

Public Function WeekNum(DateIn As Date) As Integer
     WeekNum = CInt(Format(DateIn, "ww", vbSunday))
    '                                                              ^
    ' use the day of the week you want to start the week on
    ' for example if you want to use January 1, use vbSaturday
    ' because it was a Saturday (weeks commonly start on Sunday)
End Function

You will still have to write code to add all the new records for the dates in each week.

For the month, just use the vba built-in Month function [ Month(SomeDate) ]

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

I think we can use your code above. It looks good.

Would it help if I created a new field in tblCounters_WeekAssignments called WeekNo. I could update this field at the same time when I create the records (on clicking Upload), so each record would now look like:

ID  Date    NewRecords   WeekNo
5   08/06/2005  6  24
6   10/06/2005  3  24

Then we could use the year part of the Date (2005) and WeekNo to identify each week.

What do you think?
0
Chuck WoodCommented:
Hi Annu,

Good call. To make your queries run faster, however, you might want to add the year to the table as well.

ID  Date    NewRecords   WeekNo   Year
5   08/06/2005  6              24         2005
6   10/06/2005  3              24         2005

Chuck
0
AnnuAuthor Commented:
OK, I will add that too at upload time.

Right, how should I now go about getting this Report:

Report 1
Total number of records in tblAssignments2 now
Total NEW records imported this week
Total NEW records imported this month
Total records with Start Date falling in this month

Shall I just create one report with 4 subreports in it. Each subreport could give one total for the above. Is there a better way? Do you think we could combine Report1 and Report2 into one report? Report1 info at the top and then Report2 info as the rest.

Thanks.
0
AnnuAuthor Commented:
Hi Chuck.

I have done the following:

Created tblAssignments2UploadHistory:
ID (AutoNumber)
UploadDate (Date)
NewRecordsUploaded (Long)
WeekNumber (Integer)
UploadYear (Integer)

Modified Upload button code:
.
.
intYear = DatePart("yyyy", Date)
intWeekNumber = GetWeekNumber(Date)
           
strSQL = "INSERT INTO tblAssignments2UploadHistory(UploadDate, NewRecordsUploaded, WeekNumber, UploadYear) VALUES(Format(Date(),'dd/mm/yyyy')," & lngNewRecordsUploaded & "," & intWeekNumber & "," & intYear & ");"

cnn.Execute strSQL
.
.

Created new Public Function:
Public Function GetWeekNumber(DateIn As Date) As Integer
     
     GetWeekNumber = CInt(Format(DateIn, "ww", vbSunday))
   
End Function

I have tested it too and all is working.
0
AnnuAuthor Commented:
Hi Chuck. I need your help with this report I have to do.

The report has 2 parts.

PART 1

The report looks like:

            ToDate   Cash/Cheque   Direct Debit   Prepayment   Blank
Total Assignments
Successful
Unsuccessful
Registering
Churn
Net
Churn%
Gap

Row Headers:
Total Assignments: total records in tblAssignments2
Successful: from field RegistrationStatus
Unsuccessful: from field RegistrationStatus
Registering: from field RegistrationStatus
Churn: Successful with field EndDate populated
Net: Successful - Churn
Churn%: Churn / Successful
Gap: Unsuccessful / Total Assignments

Column Headers:
ToDate: All records
Cash/Cheque: Only those records with Cash/Cheque in the PayHow field
Direct Debit: Only those records with Direct Debit in the PayHow field
Prepayment: Only those records with Prepayment in the PayHow field
Blank: Only those records with Blank in the PayHow field (empty string "")

An example of how the report would look is:

                ToDate   Cash/Cheque   Direct Debit   Prepayment   Blank
Total Assignments             1455       1056          260            129         10
Successful                        1324       947            250            123         4
Unsuccessful                     127        107            10              6             4
Registering                       4            2                0               0             2
Churn                              236         182            11              43           0
Net                                 1088        765            239            80           4
Churn%                          17.8%      19.2%        4.4%          35.0%      0.0%
Gap                                8.73%      10.13%       3.85%        4.65%      40.00%

PART 2

This report is exactly the same as PART 1 except that it takes data from records that have StartDate in the CURRENT MONTH. So the descriptions would be:

Row Headers:
Total Assignments: total records in tblAssignments2 where StartDate in Current Month
Successful: from field RegistrationStatus where StartDate in Current Month
Unsuccessful: from field RegistrationStatus where StartDate in Current Month
Registering: from field RegistrationStatus where StartDate in Current Month
Churn: Successful with field EndDate populated and in Current Month
Net: Successful - Churn
Churn%: Churn / Successful
Gap: Unsuccessful / Total Assignments

Column Headers:
ToDate: All records where StartDate in Current Month
Cash/Cheque: Only those records with Cash/Cheque in the PayHow field
Direct Debit: Only those records with Direct Debit in the PayHow field
Prepayment: Only those records with Prepayment in the PayHow field
Blank: Only those records with Blank in the PayHow field (empty string "")

An example of how the report would look is:

                MayMTD   Cash/Cheque   Direct Debit   Prepayment   Blank
Total Assignments             186           149             32               0           5
Successful                        159           127             31               0           1
Unsuccessful                     23             20              1                 0            2
Registering                       4               2                0                 0            2
Churn                              27             21               1                5            0
Net                                 132            106             30              -5           1
Churn%                          17.0%        16.5%         3.2%           !            0.0%
Gap                                12.37%      13.42%        3.13%         !          40.00%


Can you help please? I don't know where to begin.

Perhaps you could lead me in the right direction by giving me stages of the things that I have to do, then I can work on each stage.

Thanks Chuck.
0
Chuck WoodCommented:
Hi Annu,

For Report1, create a report that has the row and column headers you indicate. At each intersection or a row and column, create a text box. Use this code in the Report_Activate event to populate the text boxes:

Private Sub Report_Activate()
    ' set the counters
    Dim lngTotalToDate As Long, lngTotalCash As Long, lngTotalDirect As Long
    Dim lngTotalPrepay As Long, lngTotalBlank As Long
    Dim lngSuccessToDate As Long, lngSuccessCash As Long, lngSuccessDirect As Long
    Dim lngSuccessPrepay As Long, lngSuccessBlank As Long
    Dim lngUnsuccessToDate As Long, lngUnsuccessCash As Long, lngUnsuccessDirect As Long
    Dim lngUnsuccessPrepay As Long, lngUnsuccessBlank As Long
    Dim lngRegisterToDate As Long, lngRegisterCash As Long, lngRegisterDirect As Long
    Dim lngRegisterPrepay As Long, lngRegisterBlank As Long
    Dim lngChurnToDate As Long, lngChurnCash As Long, lngChurnDirect As Long
    Dim lngChurnPrepay As Long, lngChurnBlank As Long
    ' set recordset objects
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
    Dim strSQL As String
    strSQL = "SELECT * FROM tblAssignments2"
    Dim rst As New ADODB.Recordset
    With rst
        ' open the recordset
        .Open strSQL, cnn, adOpenKeyset, adLockReadOnly
        ' loop through the records
        Do While Not .EOF
            ' increment the total counters
            lngTotalToDate = lngTotalToDate + 1
            If .Fields("PayHow") = "Cash/Cheque" Then lngTotalCash = lngTotalCash + 1
            If .Fields("PayHow") = "Direct Debit" Then lngTotalDirect = lngTotalDirect + 1
            If .Fields("PayHow") = "Prepayment" Then lngTotalPrepay = lngTotalPrepay + 1
            If IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0 Then lngTotalBlank = lngTotalBlank + 1
            ' increment the success counters
            If .Fields("RegistrationStatus") = "Successful" Then lngSuccessToDate = lngSuccessToDate + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Cash/Cheque" Then _
                lngSuccessCash = lngSuccessCash + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Direct Debit" Then _
                lngSuccessDirect = lngSuccessDirect + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Prepayment" Then _
                lngSuccessPrepay = lngSuccessPrepay + 1
            If .Fields("RegistrationStatus") = "Successful" And IsNull(.Fields("PayHow")) Or _
                Len(Trim(.Fields("PayHow"))) = 0 Then lngSuccessBlank = lngSuccessBlank + 1
            ' increment the unsuccess counters
            If .Fields("RegistrationStatus") = "Unsuccessful" Then lngUnsuccessToDate = lngUnsuccessToDate + 1
            If .Fields("RegistrationStatus") = "Unsuccessful" And .Fields("PayHow") = "Cash/Cheque" Then _
                lngUnsuccessCash = lngUnsuccessCash + 1
            If .Fields("RegistrationStatus") = "Unsuccessful" And .Fields("PayHow") = "Direct Debit" Then _
                lngUnsuccessDirect = lngUnsuccessDirect + 1
            If .Fields("RegistrationStatus") = "Unsuccessful" And .Fields("PayHow") = "Prepayment" Then _
                lngUnsuccessPrepay = lngUnsuccessPrepay + 1
            If .Fields("RegistrationStatus") = "Unsuccessful" And IsNull(.Fields("PayHow")) Or _
                Len(Trim(.Fields("PayHow"))) = 0 Then lngUnsuccessBlank = lngUnsuccessBlank + 1
            ' increment the registering counters
            If .Fields("RegistrationStatus") = "Registering" Then lngRegisterToDate = lngRegisterToDate + 1
            If .Fields("RegistrationStatus") = "Registering" And .Fields("PayHow") = "Cash/Cheque" Then _
                lngRegisterCash = lngRegisterCash + 1
            If .Fields("RegistrationStatus") = "Registering" And .Fields("PayHow") = "Direct Debit" Then _
                lngRegisterDirect = lngRegisterDirect + 1
            If .Fields("RegistrationStatus") = "Registering" And .Fields("PayHow") = "Prepayment" Then _
                lngRegisterPrepay = lngRegisterPrepay + 1
            If .Fields("RegistrationStatus") = "Registering" And IsNull(.Fields("PayHow")) Or _
                Len(Trim(.Fields("PayHow"))) = 0 Then lngRegisterBlank = lngRegisterBlank + 1
            ' increment the churn counters
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> "lngRegister" Then _
                lngChurnToDate = lngChurnToDate + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> "lngRegister" And _
                .Fields("PayHow") = "Cash/Cheque" Then lngChurnCash = lngChurnCash + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> "lngRegister" And _
                .Fields("PayHow") = "Direct Debit" Then lngChurnDirect = lngChurnDirect + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> "lngRegister" And _
                .Fields("PayHow") = "Prepayment" Then lngChurnPrepay = lngChurnPrepay + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> "lngRegister" And _
                IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0 Then lngChurnBlank = lngChurnBlank + 1
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing
    ' populate the text boxes
    txtTotalToDate = lngTotalToDate
    txtTotalCash = lngTotalCash
    txtTotalDirect = lngTotalDirect
    txtTotalPrepay = lngTotalPrepay
    txtTotalBlank = lngTotalBlank
    txtSuccessToDate = lngSuccessToDate
    txtSuccessCash = lngSuccessCash
    txtSuccessDirect = lngSuccessDirect
    txtSuccessPrepay = lngSuccessPrepay
    txtSuccessBlank = lngSuccessBlank
    txtUnsuccessToDate = lngUnsuccessToDate
    txtUnsuccessCash = lngUnsuccessCash
    txtUnsuccessDirect = lngUnsuccessDirect
    txtUnsuccessPrepay = lngUnsuccessPrepay
    txtUnsuccessBlank = lngUnsuccessBlank
    txtRegisterToDate = lngRegisterToDate
    txtRegisterCash = lngRegisterCash
    txtRegisterDirect = lngRegisterDirect
    txtRegisterPrepay = lngRegisterPrepay
    txtRegisterBlank = lngRegisterBlank
    txtChurnToDate = lngChurnToDate
    txtChurnCash = lngChurnCash
    txtChurnDirect = lngChurnDirect
    txtChurnPrepay = lngChurnPrepay
    txtChurnBlank = lngChurnBlank
    txtNetToDate = lngSuccessToDate - lngChurnToDate
    txtNetCash = lngSuccessCash - lngChurnCash
    txtNetDirect = lngSuccessDirect - lngChurnDirect
    txtNetPrepay = lngSuccessPrepay - lngChurnPrepay
    txtNetBlank = lngSuccessBlank - lngChurnBlank
    ' set the percent defaults
    txtChurnPctToDate = 0
    txtChurnPctCash = 0
    txtChurnPctDirect = 0
    txtChurnPctPrepay = 0
    txtChurnPctBlank = 0
    If lngSuccessToDate > 0 Then txtChurnPctToDate = lngChurnToDate / lngSuccessToDate
    If lngSuccessCash > 0 Then txtChurnPctCash = lngChurnCash / lngSuccessCash
    If lngSuccessDirect > 0 Then txtChurnPctDirect = lngChurnDirect / lngSuccessDirect
    If lngSuccessPrepay > 0 Then txtChurnPctPrepay = lngChurnPrepay / lngSuccessPrepay
    If lngSuccessBlank Then txtChurnPctBlank = lngChurnBlank / lngSuccessBlank
    ' set the gap defaults
    txtGapToDate = 0
    txtGapCash = 0
    txtGapDirect = 0
    txtGapPrepay = 0
    txtGapBlank = 0
    If lngTotalToDate > 0 Then txtGapToDate = lngUnsuccessToDate / lngTotalToDate
    If lngTotalCash > 0 Then txtGapCash = lngUnsuccessCash / lngTotalCash
    If lngTotalDirect > 0 Then txtGapDirect = lngUnsuccessDirect / lngTotalDirect
    If lngTotalPrepay > 0 Then txtGapPrepay = lngUnsuccessPrepay / lngTotalPrepay
    If lngTotalBlank > 0 Then txtGapBlank = lngUnsuccessBlank / lngTotalBlank
End Sub

Chuck
0
AnnuAuthor Commented:
Thanks Chuck!

I tried it out and it works fine! That's a clever way to do it, I thought it would involve some complex crosstab queries or something.

For the second report I suppose I would just have to change the recordset SQL to:
SELECT * FROM tblAssignments2 WHERE Month(StartDate) = Month(Date)

The second report has a slight issue with Churn though, becuase Churn is: Successful with field EndDate populated and in Current Month.

Also, I tried to export Report1 to Excel (uisng the OfficeLinks icon) but I get the error:
MS Access can't start the OLE server
You tried to use a form, report, or datasheet that contains an OLE object, but the OLE server (the application used to create the object) may not be registered properly. Reinstal the OLE server to register it correctly.

Do you know what that means? I use the OfficeLinks with other tables and queries and it works OK.

Thanks again Chuck.
0
Chuck WoodCommented:
Hi Annu,

<For the second report I suppose I would just have to change the recordset SQL to:
SELECT * FROM tblAssignments2 WHERE Month(StartDate) = Month(Date)>
That should do it.

<The second report has a slight issue with Churn>
This should work:
     If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> "lngRegister" And _
        Month(.Fields("EndDate")) = Month(Date) Then lngChurnToDate = lngChurnToDate + 1

<MS Access can't start the OLE server>
I didn't have that problem, although the report was not very useful.
Here is what it produced:
txtTotalToDate  txtTotalCash  txtTotalDirect  txtTotalPrepay  txtTotalBlank  txtSuccessToDate  ...
5                     3                  2                    0                    0                   5                         ...
Here is what Microsoft says about your error:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;302507
I had much better results publishing the report with Microsoft Word.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

My 2 reports are wroking now. That's great!

Just one last (I hope) small issue. I made both reports as subreports and put both of them in a main report. When I open each subreport separately, they run fine, but when I open the main report the subreports contain no data.

It seems that the OnActivate event is not running for the subreports. Any idea how to solve this probelm? Thanks.
0
Chuck WoodCommented:
Hi Annu,

Change the code in each subreport as follows:

Do a global find and replace changing 'txt' to 'Reports!rptTest!rptReport1!txt' (without the quotes and replacing rptTest with the name of your main form and replacing rptReport1 with the name of your subform.

This will change txtTotalToDate = lngTotalToDate to Reports!rptTest!rptReport1!txtTotalToDate = lngTotalToDate, for example.

After doing this in each subreport, move the code in both subreports to the main report.

Chuck.
0
AnnuAuthor Commented:
Great Chuck, that worked! Thanks.

So the reporting is done now too. I know that points don't matter too much to you, but it is still my duty to keep you informed.

For the work so far I will give you 750 + 350 = 1100 points.

The next stage of my project involves working with tblAssignments2. I need to split data from tblAssignments2 into seperate tables. The separate tables are:

tblCustomers
tblContracts (Assigment is another term for Contract)

The split of data will have to be done weekly, after the new and changed data has been uploaded to tblAssignments2. So again, we will need to add new/change customer records and add new/change Contract records.

I might have to do some linking with the Bills and Balances tables too.

So, I just wanted to ask you again if you would like me to close this question and award you the points or carry on here. I will set a point value of 500 for the next stage too.

Thanks Chuck.
0
Chuck WoodCommented:
Hi Annu,

You can start a new question or continue with this one. It really doesn't matter to me.

<So again, we will need to add new/change customer records and add new/change Contract records.>

I think it might be easier to just clear the two tables and fill them from the tblAssignments2, unless you are going to add persistent data to them after updating the tables.

Chuck
0
AnnuAuthor Commented:
Thanks Chuck.

OK, I will just continue here. I will post some details up in a couple of days when I have thought the process through.

Have nice weekend.
0
AnnuAuthor Commented:
Hi Chuck, i hope you are well.

I am having some problems with the report we made (the one with the 2 subreports):

1 The report doesn't print. When I send it for printing everything appears except the values in each cell.

2 I can't export the values to Excel. When I use the Office Links to export the report to Excel, there are only text box names, no values.

Do you know how to solve these problems?

Thanks.
0
nico5038Commented:
Hi Annu,

Quite a question andf I guess it's best to close this one and start one for the tblAssignments2.
Personally I use in a case like this a linked table (or tables) for the weekly data and import it into a table with a YearWeek field to hold it "separate". This will allow the user to rerun an import when he got an erroneous file.

After importing I show the data in a main form with a subform and for every additional data needed another synchronized subform will be added.

That's the general design, but it's best to start with posting a Q with the split criteria and the related data needed for the user to update the final table(s).

Nic;o)
0
Chuck WoodCommented:
Hi Annu,

Sorry for not responding. It has been a very busy time lately. I will try to get back early tomorrow.

Chuck
0
AnnuAuthor Commented:
Thanks so much Nico.
I have taken your advice and created a new question for the splitting of tblAssignments2. The link is:
http://www.experts-exchange.com/Databases/MS_Access/Q_21483045.html

That's OK Chuck. As always, I really appreciate your help. Do you think we will have to re-do the way we calculate our report stats to make it print and export to Excel?
0
Chuck WoodCommented:
Hi Annu,

1.  The report doesn't print. When I send it for printing everything appears except the values in each cell.

I don't know why it doesn't print. I am looking into this.

2.  I can't export the values to Excel. When I use the Office Links to export the report to Excel, there are only text box names, no values.

I have the same problem. I am looking into this as well.

Chuck
0
AnnuAuthor Commented:
Hi Chuck, any luck with printing and exporting Report1?

Maybe you can help, Nico?

Thanks.
0
nico5038Commented:
Can you make a summary, reading this Q will take a lot of time and I don't have much of that nowadays.
Did you consider the additional field to keep runs separated ?

Nic;o)
0
AnnuAuthor Commented:
Hi Nico. Here is the summary:

My clients have a database that stores customer contracts (called Assignments). The Assignments table data is sent to them weekly by a third party that captures the data. Every week they send ALL the data, ie all old data and any new data. Old data can have fields that have changed.

In our application we have a main Assignments table called tblAssignments2. This holds their live data. We import the weekly Assignments data into a temporary table called tblAssignments2Temp.

Then we display the original live data and the newly imported data record by record on one form called frmCompareAssignments. This form has 2 subforms, one for the original Assignments data and one for the new Assignments data. The user clicks Previous and Next buttons that we created and goes through the records. He sees the old record and the new record in the same form and the form highlights fields that have changed.

When the user is happy he clicks the Upload button which uploads all the new records from tblAssignments2Temp to tblAssignments2 and changes any records in tblAssignments2 that have changed in tblAssignments2Temp.

They then run a report called Assignment Statistics. This is where I need help.

I have explained the report in my comment above 06/13/2005 02:49PM PDT (Annu), the only difference is that I have made one report with 2 subreports rather than 2 reports as in the comment.

This is the code I use to populate the 2 subreports:

-----------------------------------------

Private Sub Report_Activate()

    ' set the counters
    Dim lngTotalToDate As Long, lngTotalCash As Long, lngTotalDirect As Long
    Dim lngTotalPrepay As Long, lngTotalBlank As Long
    Dim lngSuccessToDate As Long, lngSuccessCash As Long, lngSuccessDirect As Long
    Dim lngSuccessPrepay As Long, lngSuccessBlank As Long
    Dim lngUnsuccessToDate As Long, lngUnsuccessCash As Long, lngUnsuccessDirect As Long
    Dim lngUnsuccessPrepay As Long, lngUnsuccessBlank As Long
    Dim lngRegisterToDate As Long, lngRegisterCash As Long, lngRegisterDirect As Long
    Dim lngRegisterPrepay As Long, lngRegisterBlank As Long
    Dim lngChurnToDate As Long, lngChurnCash As Long, lngChurnDirect As Long
    Dim lngChurnPrepay As Long, lngChurnBlank As Long
    Dim lngChurnMTD As Long, lngChurnCashMTD As Long, lngChurnDirectMTD As Long
    Dim lngChurnPrepayMTD As Long, lngChurnBlankMTD As Long
   
    ' for all Assigments
    ' set recordset objects
    Dim cnn As ADODB.Connection
    Set cnn = CurrentProject.Connection
   
    Dim strSQLAll As String
    strSQLAll = "SELECT * FROM tblAssignments2"
   
    Dim rstAll As New ADODB.Recordset
    With rstAll
        ' open the recordset
        .Open strSQLAll, cnn, adOpenKeyset, adLockReadOnly
       
        ' loop through the records
        Do While Not .EOF
            ' increment the total counters
            lngTotalToDate = lngTotalToDate + 1
            If .Fields("PayHow") = "Cash/Cheque" Then lngTotalCash = lngTotalCash + 1
            If .Fields("PayHow") = "Direct Debit" Then lngTotalDirect = lngTotalDirect + 1
            If .Fields("PayHow") = "Prepayment" Then lngTotalPrepay = lngTotalPrepay + 1
            If IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0 Then lngTotalBlank = lngTotalBlank + 1
            ' increment the success counters
            If .Fields("RegistrationStatus") = "Successful" Then lngSuccessToDate = lngSuccessToDate + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Cash/Cheque" Then lngSuccessCash = lngSuccessCash + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Direct Debit" Then lngSuccessDirect = lngSuccessDirect + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Prepayment" Then lngSuccessPrepay = lngSuccessPrepay + 1
            If .Fields("RegistrationStatus") = "Successful" And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngSuccessBlank = lngSuccessBlank + 1
            ' increment the unsuccess counters
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" Then lngUnsuccessToDate = lngUnsuccessToDate + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And .Fields("PayHow") = "Cash/Cheque" Then lngUnsuccessCash = lngUnsuccessCash + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And .Fields("PayHow") = "Direct Debit" Then lngUnsuccessDirect = lngUnsuccessDirect + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And .Fields("PayHow") = "Prepayment" Then lngUnsuccessPrepay = lngUnsuccessPrepay + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngUnsuccessBlank = lngUnsuccessBlank + 1
            ' increment the registering counters
            If .Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending" Then lngRegisterToDate = lngRegisterToDate + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And .Fields("PayHow") = "Cash/Cheque" Then lngRegisterCash = lngRegisterCash + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And .Fields("PayHow") = "Direct Debit" Then lngRegisterDirect = lngRegisterDirect + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And .Fields("PayHow") = "Prepayment" Then lngRegisterPrepay = lngRegisterPrepay + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngRegisterBlank = lngRegisterBlank + 1
            ' increment the churn counters
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> #1/1/1900# Then lngChurnToDate = lngChurnToDate + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> #1/1/1900# And .Fields("PayHow") = "Cash/Cheque" Then lngChurnCash = lngChurnCash + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> #1/1/1900# And .Fields("PayHow") = "Direct Debit" Then lngChurnDirect = lngChurnDirect + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> #1/1/1900# And .Fields("PayHow") = "Prepayment" Then lngChurnPrepay = lngChurnPrepay + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("EndDate") <> #1/1/1900# And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngChurnBlank = lngChurnBlank + 1
            ' increment the MDT churn counters (ALL Successful records with End Dates in Current Month)
            If .Fields("RegistrationStatus") = "Successful" And DatePart("yyyy", .Fields("EndDate")) = DatePart("yyyy", Date) And Month(.Fields("EndDate")) = Month(Date) Then lngChurnMTD = lngChurnMTD + 1
            If .Fields("RegistrationStatus") = "Successful" And DatePart("yyyy", .Fields("EndDate")) = DatePart("yyyy", Date) And Month(.Fields("EndDate")) = Month(Date) And .Fields("PayHow") = "Cash/Cheque" Then lngChurnCashMTD = lngChurnCashMTD + 1
            If .Fields("RegistrationStatus") = "Successful" And DatePart("yyyy", .Fields("EndDate")) = DatePart("yyyy", Date) And Month(.Fields("EndDate")) = Month(Date) And .Fields("PayHow") = "Direct Debit" Then lngChurnDirectMTD = lngChurnDirectMTD + 1
            If .Fields("RegistrationStatus") = "Successful" And DatePart("yyyy", .Fields("EndDate")) = DatePart("yyyy", Date) And Month(.Fields("EndDate")) = Month(Date) And .Fields("PayHow") = "Prepayment" Then lngChurnPrepayMTD = lngChurnPrepayMTD + 1
            If .Fields("RegistrationStatus") = "Successful" And DatePart("yyyy", .Fields("EndDate")) = DatePart("yyyy", Date) And Month(.Fields("EndDate")) = Month(Date) And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngChurnBlankMTD = lngChurnBlankMTD + 1

            .MoveNext
        Loop
        .Close
       
    End With
    Set rstAll = Nothing
   
    ' populate the text boxes
    Reports!rptAssignmentStatistics!AllAssignments!txtTotalToDate = lngTotalToDate
    Reports!rptAssignmentStatistics!AllAssignments!txtTotalCash = lngTotalCash
    Reports!rptAssignmentStatistics!AllAssignments!txtTotalDirect = lngTotalDirect
    Reports!rptAssignmentStatistics!AllAssignments!txtTotalPrepay = lngTotalPrepay
    Reports!rptAssignmentStatistics!AllAssignments!txtTotalBlank = lngTotalBlank
    Reports!rptAssignmentStatistics!AllAssignments!txtSuccessToDate = lngSuccessToDate
    Reports!rptAssignmentStatistics!AllAssignments!txtSuccessCash = lngSuccessCash
    Reports!rptAssignmentStatistics!AllAssignments!txtSuccessDirect = lngSuccessDirect
    Reports!rptAssignmentStatistics!AllAssignments!txtSuccessPrepay = lngSuccessPrepay
    Reports!rptAssignmentStatistics!AllAssignments!txtSuccessBlank = lngSuccessBlank
    Reports!rptAssignmentStatistics!AllAssignments!txtUnsuccessToDate = lngUnsuccessToDate
    Reports!rptAssignmentStatistics!AllAssignments!txtUnsuccessCash = lngUnsuccessCash
    Reports!rptAssignmentStatistics!AllAssignments!txtUnsuccessDirect = lngUnsuccessDirect
    Reports!rptAssignmentStatistics!AllAssignments!txtUnsuccessPrepay = lngUnsuccessPrepay
    Reports!rptAssignmentStatistics!AllAssignments!txtUnsuccessBlank = lngUnsuccessBlank
    Reports!rptAssignmentStatistics!AllAssignments!txtRegisterToDate = lngRegisterToDate
    Reports!rptAssignmentStatistics!AllAssignments!txtRegisterCash = lngRegisterCash
    Reports!rptAssignmentStatistics!AllAssignments!txtRegisterDirect = lngRegisterDirect
    Reports!rptAssignmentStatistics!AllAssignments!txtRegisterPrepay = lngRegisterPrepay
    Reports!rptAssignmentStatistics!AllAssignments!txtRegisterBlank = lngRegisterBlank
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnToDate = lngChurnToDate
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnCash = lngChurnCash
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnDirect = lngChurnDirect
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnPrepay = lngChurnPrepay
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnBlank = lngChurnBlank
    Reports!rptAssignmentStatistics!AllAssignments!txtNetToDate = lngSuccessToDate - lngChurnToDate
    Reports!rptAssignmentStatistics!AllAssignments!txtNetCash = lngSuccessCash - lngChurnCash
    Reports!rptAssignmentStatistics!AllAssignments!txtNetDirect = lngSuccessDirect - lngChurnDirect
    Reports!rptAssignmentStatistics!AllAssignments!txtNetPrepay = lngSuccessPrepay - lngChurnPrepay
    Reports!rptAssignmentStatistics!AllAssignments!txtNetBlank = lngSuccessBlank - lngChurnBlank
    ' populate the text boxes on MTD subreport
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnMTD = lngChurnMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnCash = lngChurnCashMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnDirect = lngChurnDirectMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPrepay = lngChurnPrepayMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnBlank = lngChurnBlankMTD
   
    ' set the percent defaults
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctToDate = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctCash = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctDirect = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctPrepay = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctBlank = 0
    If lngSuccessToDate > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctToDate = lngChurnToDate / lngSuccessToDate
    If lngSuccessCash > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctCash = lngChurnCash / lngSuccessCash
    If lngSuccessDirect > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctDirect = lngChurnDirect / lngSuccessDirect
    If lngSuccessPrepay > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctPrepay = lngChurnPrepay / lngSuccessPrepay
    If lngSuccessBlank > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtChurnPctBlank = lngChurnBlank / lngSuccessBlank
   
    ' set the gap defaults
    Reports!rptAssignmentStatistics!AllAssignments!txtGapToDate = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtGapCash = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtGapDirect = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtGapPrepay = 0
    Reports!rptAssignmentStatistics!AllAssignments!txtGapBlank = 0
    If lngTotalToDate > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtGapToDate = lngUnsuccessToDate / lngTotalToDate
    If lngTotalCash > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtGapCash = lngUnsuccessCash / lngTotalCash
    If lngTotalDirect > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtGapDirect = lngUnsuccessDirect / lngTotalDirect
    If lngTotalPrepay > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtGapPrepay = lngUnsuccessPrepay / lngTotalPrepay
    If lngTotalBlank > 0 Then Reports!rptAssignmentStatistics!AllAssignments!txtGapBlank = lngUnsuccessBlank / lngTotalBlank
   
   
   
   
    ' for this months Assignments
    ' reset the counters
    Dim lngTotalMTD As Long
    lngTotalCash = 0
    lngTotalDirect = 0
    lngTotalPrepay = 0
    lngTotalBlank = 0
    Dim lngSuccessMTD As Long
    lngSuccessCash = 0
    lngSuccessDirect = 0
    lngSuccessPrepay = 0
    lngSuccessBlank = 0
    Dim lngUnsuccessMTD As Long
    lngUnsuccessCash = 0
    lngUnsuccessDirect = 0
    lngUnsuccessPrepay = 0
    lngUnsuccessBlank = 0
    Dim lngRegisterMTD As Long
    lngRegisterCash = 0
    lngRegisterDirect = 0
    lngRegisterPrepay = 0
    lngRegisterBlank = 0
   
    ' set recordset objects
    Dim strSQLMonth As String
    strSQLMonth = "SELECT * FROM tblAssignments2 WHERE DatePart('yyyy', StartDate) = DatePart('yyyy', Date()) AND Month(StartDate) = Month(Date())"
   
    Dim rstMonth As New ADODB.Recordset
    With rstMonth
        ' open the recordset
        .Open strSQLMonth, cnn, adOpenKeyset, adLockReadOnly
       
        ' loop through the records
        Do While Not .EOF
            ' increment the total counters
            lngTotalMTD = lngTotalMTD + 1
            If .Fields("PayHow") = "Cash/Cheque" Then lngTotalCash = lngTotalCash + 1
            If .Fields("PayHow") = "Direct Debit" Then lngTotalDirect = lngTotalDirect + 1
            If .Fields("PayHow") = "Prepayment" Then lngTotalPrepay = lngTotalPrepay + 1
            If IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0 Then lngTotalBlank = lngTotalBlank + 1
            ' increment the success counters
            If .Fields("RegistrationStatus") = "Successful" Then lngSuccessMTD = lngSuccessMTD + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Cash/Cheque" Then lngSuccessCash = lngSuccessCash + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Direct Debit" Then lngSuccessDirect = lngSuccessDirect + 1
            If .Fields("RegistrationStatus") = "Successful" And .Fields("PayHow") = "Prepayment" Then lngSuccessPrepay = lngSuccessPrepay + 1
            If .Fields("RegistrationStatus") = "Successful" And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngSuccessBlank = lngSuccessBlank + 1
            ' increment the unsuccess counters
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" Then lngUnsuccessMTD = lngUnsuccessMTD + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And .Fields("PayHow") = "Cash/Cheque" Then lngUnsuccessCash = lngUnsuccessCash + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And .Fields("PayHow") = "Direct Debit" Then lngUnsuccessDirect = lngUnsuccessDirect + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And .Fields("PayHow") = "Prepayment" Then lngUnsuccessPrepay = lngUnsuccessPrepay + 1
            If .Fields("RegistrationStatus") = "Registration Unsuccessful" And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngUnsuccessBlank = lngUnsuccessBlank + 1
            ' increment the registering counters
            If .Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending" Then lngRegisterMTD = lngRegisterMTD + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And .Fields("PayHow") = "Cash/Cheque" Then lngRegisterCash = lngRegisterCash + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And .Fields("PayHow") = "Direct Debit" Then lngRegisterDirect = lngRegisterDirect + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And .Fields("PayHow") = "Prepayment" Then lngRegisterPrepay = lngRegisterPrepay + 1
            If (.Fields("RegistrationStatus") = "Registering" Or .Fields("RegistrationStatus") = "Registration Pending") And (IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0) Then lngRegisterBlank = lngRegisterBlank + 1
                       
            .MoveNext
        Loop
        .Close
       
    End With
    Set rstMonth = Nothing
   
    ' populate the text boxes
    Reports!rptAssignmentStatistics!MonthAssignments!txtTotalMTD = lngTotalMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtTotalCash = lngTotalCash
    Reports!rptAssignmentStatistics!MonthAssignments!txtTotalDirect = lngTotalDirect
    Reports!rptAssignmentStatistics!MonthAssignments!txtTotalPrepay = lngTotalPrepay
    Reports!rptAssignmentStatistics!MonthAssignments!txtTotalBlank = lngTotalBlank
    Reports!rptAssignmentStatistics!MonthAssignments!txtSuccessMTD = lngSuccessMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtSuccessCash = lngSuccessCash
    Reports!rptAssignmentStatistics!MonthAssignments!txtSuccessDirect = lngSuccessDirect
    Reports!rptAssignmentStatistics!MonthAssignments!txtSuccessPrepay = lngSuccessPrepay
    Reports!rptAssignmentStatistics!MonthAssignments!txtSuccessBlank = lngSuccessBlank
    Reports!rptAssignmentStatistics!MonthAssignments!txtUnsuccessMTD = lngUnsuccessMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtUnsuccessCash = lngUnsuccessCash
    Reports!rptAssignmentStatistics!MonthAssignments!txtUnsuccessDirect = lngUnsuccessDirect
    Reports!rptAssignmentStatistics!MonthAssignments!txtUnsuccessPrepay = lngUnsuccessPrepay
    Reports!rptAssignmentStatistics!MonthAssignments!txtUnsuccessBlank = lngUnsuccessBlank
    Reports!rptAssignmentStatistics!MonthAssignments!txtRegisterMTD = lngRegisterMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtRegisterCash = lngRegisterCash
    Reports!rptAssignmentStatistics!MonthAssignments!txtRegisterDirect = lngRegisterDirect
    Reports!rptAssignmentStatistics!MonthAssignments!txtRegisterPrepay = lngRegisterPrepay
    Reports!rptAssignmentStatistics!MonthAssignments!txtRegisterBlank = lngRegisterBlank
   
    Reports!rptAssignmentStatistics!MonthAssignments!txtNetMTD = lngSuccessMTD - lngChurnMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtNetCash = lngSuccessCash - lngChurnCashMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtNetDirect = lngSuccessDirect - lngChurnDirectMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtNetPrepay = lngSuccessPrepay - lngChurnPrepayMTD
    Reports!rptAssignmentStatistics!MonthAssignments!txtNetBlank = lngSuccessBlank - lngChurnBlankMTD
   
    ' set the percent defaults
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctMTD = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctCash = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctDirect = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctPrepay = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctBlank = 0
    If lngSuccessMTD > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctMTD = lngChurnMTD / lngSuccessMTD
    If lngSuccessCash > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctCash = lngChurnCashMTD / lngSuccessCash
    If lngSuccessDirect > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctDirect = lngChurnDirectMTD / lngSuccessDirect
    If lngSuccessPrepay > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctPrepay = lngChurnPrepayMTD / lngSuccessPrepay
    If lngSuccessBlank > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtChurnPctBlank = lngChurnBlankMTD / lngSuccessBlank
   
    ' set the gap defaults
    Reports!rptAssignmentStatistics!MonthAssignments!txtGapMTD = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtGapCash = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtGapDirect = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtGapPrepay = 0
    Reports!rptAssignmentStatistics!MonthAssignments!txtGapBlank = 0
    If lngTotalMTD > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtGapMTD = lngUnsuccessMTD / lngTotalMTD
    If lngTotalCash > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtGapCash = lngUnsuccessCash / lngTotalCash
    If lngTotalDirect > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtGapDirect = lngUnsuccessDirect / lngTotalDirect
    If lngTotalPrepay > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtGapPrepay = lngUnsuccessPrepay / lngTotalPrepay
    If lngTotalBlank > 0 Then Reports!rptAssignmentStatistics!MonthAssignments!txtGapBlank = lngUnsuccessBlank / lngTotalBlank

End Sub

-------------------------------------------

The problem I am having is that:

1 The report doesn't print. When I send it for printing everything appears except the values in each cell.

2 I can't export the values to Excel. When I use the Office Links to export the report to Excel, there are only text box names, no values.

Can you solve these problems for me please. I need to be able to print the report and export it to Excel.

For your info, these are the fields of tblAssignments2:

CustomerId(number)
Title(txt), FirstName(txt), Surname(txt), Customer Address Line 1 - 9(txt), Customer Address Postcode(txt), Telephone(txt)

AccountId(number)
PayHow(txt)
PaymentDay(number)
FirstPaymentDateCurrentPlan(date)
NextPayPlanReviewDate(date)
BankAccountName(txt), BankAccountNo(txt), BankSortCode(txt), DDRefNumber (txt), InstalmentAmount(currency)

MPANCore(txt), MPANTopline(txt)
StartDate(date), EndDate(txt)
RegistrationStatus(txt)
TariffCode(txt)
UniqueID (txt)

The UniqueID field consists of CustomerId, AccountId, MPANCore, MPANTopline and StartDate.

Thanks Nico.
0
AnnuAuthor Commented:
Hi Nico, I hope the summary was OK. Didn't confuse you did I?

I would appreciate your help.

Thanks.
0
nico5038Commented:
Sorry, you were still on my todo list, but had to start in 2 jobs last month so a bit exhausted in the evenings...

The calculation you do can be moved to the report query by using the IIF() statement like:
            If .Fields("PayHow") = "Cash/Cheque" Then lngTotalCash = lngTotalCash + 1
            If .Fields("PayHow") = "Direct Debit" Then lngTotalDirect = lngTotalDirect + 1
            If .Fields("PayHow") = "Prepayment" Then lngTotalPrepay = lngTotalPrepay + 1
            If IsNull(.Fields("PayHow")) Or Len(Trim(.Fields("PayHow"))) = 0 Then lngTotalBlank = lngTotalBlank + 1
can be done using:
select sum(IIF(len(nz([PayHow]))>0,0,1)) as lngTotalBlank, sum(IIF(nz([PayHow])="Prepayment",1,0)) as lngTotalPrepay,  sum(IIF(nz([PayHow])="Direct Debit",1,0)) as lngTotalDirect, sum(IIF(nz([PayHow])="Cash/Cheque",1,0)) as lngTotalCash , etc...

Best probably to drop the compacted and zipped .mdb in my mail so I can experiment with your report.

Nic;o)

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AnnuAuthor Commented:
Thanks Nico.

I have sent it to your yahoo.com account.

Appreciate your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.