Results 1 to 8 of 8
  1. #1
    mcgwn is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    6

    Short Text "field is too small" error on first character entered

    I have a text box control on a form, writing to a short text field in a table. The short text field has a field size of 50 characters; the error occurs when I enter a single character in the field. I can accept the error, and proceed entering data, which does populate the field. However, I need to distribute this form to hundreds of users, and would like it to work cleanly.

    Searching around, I see people encounter this error if:


    1. they mix up the field/values order in an insert statement; however, I am simply trying to manually enter data in a text box control
    2. they experience "corruption" of the control or field, and suggest removing and replacing; I have tried both with no luck
    3. the field is set as a lookup field and the wrong data type is being written; however, this field has a display control of text box


    I have a Before Change data macro populating another field by operating on this field's value; however, I have the same error occurring with a second table/form/field that does not have a Before Change data macro.

    Any suggestions would be greatly appreciated.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  3. #3
    mcgwn is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    6

    db for analysis

    Hi June7,

    The database is attached for analysis. The issue occurs when entering data in the agency_num fields in the Project/New Sites/Site Update forms. I believe it has to do with AutoNumber ID fields that are populated on new entries, but these are set to Long Integer so I am not sure where the issue would be. Many thanks.

    em2020_template.zip

  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,825
    Why is rec_date in Sites a text instead of date/time field?

    Why does Concordance have azsite_num and site_num fields?

    Not clear to me what this db is for and what data relationships are. Why do Sites and Site_Update appear to duplicate data?

    Appear to be associating multiple 'sites' with an 'azsite', whatever those are. Then in one label you refer to 'project' with multiple 'sites'.
    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
    mcgwn is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    6
    Hi June7,

    Your questions seem to be outside the scope of my problem, but since you asked:

    This database is for recording archaeological data. I believe rec_date is formatted as text because that is how it is formatted in the sql server db that this is an intake module for. It's unfortunate it was done that way (long before my time), but it would be an effort to back out date/time from these data in sql server now. That doesn't mean this field couldn't be a date/time. Perhaps we should change it.

    The concordance table is used to record alternative site numbers, from agencies that are not the official recording agency. This has to do with land jurisdiction. azsite_num is a numerical identifier used as a unique ID in our databases; agency_num are alphanumeric site identifiers based on a statewide topographic grid; concordance.site_num could be any alphanumeric identifier issued to a given site from a different agency.

    You are correct that we are dealing with projects and sites; a given project may encounter numerous sites; it may also encounter previously recorded sites that are "updated". Considerations for new recordings vs. updates are different, which is why they are separated in the database, though there is some overlap in the data recorded. Projects also have an "agency_num", in this case the alphanumeric identifier issued by the permitting agency. There are also project concordances, such as project designations used by the company conducting the project, etc.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Are Agency_Num and Agency fields for the same entity?

    Entering data directly into Sites table works so definitely something about form design. Very mysterious, still exploring. I don't use Navigation form.

    Strongly advise not to build lookup fields in tables, at least not if using alias http://access.mvps.org/Access/lookupfields.htm.
    Greater flexibility using tables as RowSource instead of ValueList.

    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.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Here's a clue. Unbind agency combobox and the error does not occur. The issue is caused by its DefaultValue of "Arizona State Museum". This string is too long for the field.
    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.

  8. #8
    mcgwn is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    6

    Lightbulb thank you

    Ah, that was it! The combo's row source contains agency code and agency name; agency code is the bound column. Therefore the default value needs to be the corresponding agency code. Can't believe after all the headaches it was that simple. Can't thank you enough.

    Quote Originally Posted by June7 View Post
    Here's a clue. Unbind agency combobox and the error does not occur. The issue is caused by its DefaultValue of "Arizona State Museum". This string is too long for the field.

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

Similar Threads

  1. Replies: 5
    Last Post: 08-10-2018, 02:20 PM
  2. Replies: 5
    Last Post: 01-13-2017, 02:20 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. Replies: 1
    Last Post: 10-08-2012, 09:01 AM
  5. Replies: 8
    Last Post: 11-12-2010, 10:55 AM

Tags for this Thread

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