Results 1 to 5 of 5
  1. #1
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038

    Value comparison -- VBA run-time error '3163'

    Good morning:



    Attached ACCDB includes a mechanism to compare 2 tables (identical in structure) and output whether or not a value has changed from tbl_Legacy_Date (aka "Before" table) to tbl_Target_Data (aka "After" table).

    This process works fine as long as I am NOT comparing a memo (long text) field.

    Upon opening the DB, please do the following:
    1. By default, DB opens with form "F01_MainMenu"
    2. In the listbox, select either [AGE] OR [FIRST_NAME] OR both [AGE] AND [FIRST_NAME]. At this time (for testing only) do NOT select value [MISHAP_ANALYSIS] just yet.
    3. Once either [AGE] OR [FIRST_NAME] have been selected, click command button "View Change Log".
    4. The subform will now display 2 records for field [AGE] and 4 records for field [FIRST_NAME] and display the change in the next 2 columns.

    Given the both source tables include 200 records, this works great as I can quickly identify what value change occurred. For testing purposes, please not that it is NOT important to identify the record ID number.

    Ok, now to the problem...
    - First, let's empty the change log by clicking on command button "Clear Change Log".
    - Now, select value [MISHAP_ANALYSIS] from the listbox.
    - Then, click on command button "View Change Log".
    - At this time, I am getting VBA run-time error '3163'.
    - Click on debug, brings me to line "![VALUE_BEFORE] = nField.Value".
    - Finally, click CTRL+ALT_DEL to shut down Access... then re-open it.

    Fixing the error:
    - This error is caused given that fieldname [MISHAP_ANALYSIS] has a data type = "Long Text"
    - Now, please open module "modGlobalCode" and locate function "IdentifyTablesChanges" and scroll down to "With tblDef"

    Here, I have changed the following from:
    Set nField = .CreateField("VALUE_BEFORE", dbText, 255)
    Set nField = .CreateField("VALUE_AFTER", dbText, 255)

    To:
    Set nField = .CreateField("VALUE_BEFORE", dbMemo, 64000)
    Set nField = .CreateField("VALUE_AFTER", dbMemo, 64000)


    Unfortunately, the above change from dbText to dbMemo does not work. I'm not sure whether or not I should have included the "64000" or not.

    Does anyone have a suggestion as to how the VBA needs to be modified so that I can execute the comparison for field [MISHAP_ANALYSIS] as well?

    Thank you,
    Tom
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tom,

    I modified the change log and it processes AGE, FirstName and MISHAP_ANALYSIS without error.
    I did not change anything else and did not change modGlobalCode. Hope it's helpful.
    Click image for larger version. 

Name:	Change4Tom.PNG 
Views:	14 
Size:	11.0 KB 
ID:	46146

    Click image for larger version. 

Name:	ChangeLogResult.PNG 
Views:	14 
Size:	27.1 KB 
ID:	46147

  3. #3
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    orange -- wow, that was simple... I totally overlooked to look into this option. Thank you so much for pointing this out and helping me solve this issue.

    Cheers,
    Tom

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,818
    Did the same.

    But if you do need code that builds table, remove the 64000.

    Why use aggregate query as form RecordSource? Grouping on memo field will truncate data. Is that a concern?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    skydivetom is offline VIP
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2019
    Posts
    1,038
    June7 -- thank you... will do.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Code Time comparison
    By napiedra in forum Forms
    Replies: 4
    Last Post: 05-25-2014, 07:15 PM
  2. Time comparison based on date - Late Report
    By gryphin81 in forum Reports
    Replies: 8
    Last Post: 04-25-2014, 11:35 AM
  3. Error Message 3163 "field is too small...."
    By reggieara in forum Forms
    Replies: 3
    Last Post: 01-22-2014, 08:30 PM
  4. Run-time Error 3163 - Field Too Small
    By CementCarver in forum Programming
    Replies: 4
    Last Post: 07-23-2013, 09:38 AM
  5. Time Comparison
    By Larry819 in forum Queries
    Replies: 1
    Last Post: 06-22-2009, 09:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums