Results 1 to 10 of 10
  1. #1
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36

    field not big enough


    Please baree with me and excuse me for the lack of accuracy on specifics

    When I try to eenteeer the eighth characteer into a field, I get an error that thee data won't fit in t the field.

    The fieeld in the backend table >A99.99aa field size shows 8
    when I try to imput the 8th character into The field in the frontend, i get an eerror:

    the field is too small to enteeer thea mount of data


    If I open the table in either backend or front end I can enter the 8th digit into the releveant table.
    But I can't seelect the efield from a combox bo in my front end GUI
    The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data. (Error 3163)

    How to find the problem and it. Do I have to re split the database? how would I do that? when look into backend there are eno from. [T

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Confusing post. No idea what this means: The fieeld in the backend table >A99.99aa How can anything be greater or less than A99.99?
    This too: But I can't seelect the efield from a combox bo in my front end GUI
    If you can add 8 characters in the table field directly, but cannot through your form, then something about your form is padding the value in the control for this field, or is adding additional characters. I don't get what selecting a value from a combo box list has to do with entering characters and exceeding the limit - unless you're saying that you get the error when you choose a value from the combo list, in which case, typing/entering data shouldn't come into play.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Correct, I meant selecting a value form the combo list.

    I seem to be ablee to enter the 8th charactere into the table ein thee backend or in the front end, but I can't seleect it in the combo box (excuse my sticky eeees)

    I built this table and GUI yeears ago. . . .I don't know why the > is in the input mask

    How do I look for the "padding culprit"? Where do I look?

    I put the FORM GUI into DESIGN view and click propertiees and I don't seeee anything releeevant there. Wheere else do I look?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The > in input mask forces all following alpha characters to uppercase.

    I cannot replicate the issue.
    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
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm still not getting what an input mask (that's new info) and typing into some as yet unidentified control has to do with selecting a combo value and generating an error. Perhaps June7 is ahead of me on this one. All I can think of is there's code running off some combo box event that's trying to insert too many characters in the field.
    I think you'll have to post a zipped copy of your db with instructions on how to replicate the issue.

  6. #6
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    Okay, there aree e2 tables involved - - obviously.

    backend table1 is the datasource tables with the >A99.99aa mask for datafieldOFinterest
    backeend table2 is the client transaction table which has among other fieelds a value for datafildOFinterest; this fieeld is a lookup to datafieeldOFinteerest
    backend tablee2 has a many to one relation ship with clientID table

    I seee the issue is that the datafieldOFinteerst (hreafter rferrd to as Intereestfieeld2 or IF2 ) is defind as 6 characteers !!
    So ofcourse if I combo slect an 8 characteer valuee to put into a 6 characteere field, theree is goign to be an ereror for size is too large
    for field !

    Why is the IF2 defind as 6 chars, you ask?
    until recently, that field only had values valid to up to 6 chars. but regulations changed and now IF2 can have up to 8 chars.

    I cannot change the table definition in the front end of course.
    When I try to change it in the backend, I get an error that the table-field IF2 cannot bee modified since theere is a relationship. First I must deleteee relationship in SHOWREELATIONSHIP mode

    So I displayed the reelationship, clicked and deelted the relationship. but I still got the same error when I went back to table2 Client transaciton tabl to chang ethe fiedl size from 6 to 8 . I closed the databse and reeopened it showed thee REELATIONSHIPS. Indeed theere is no relationship of table2 to ClieeentID

    I tried changing combo box to tEXT in the field type (succeessfully) and theen tried to changee the fiedl from 6 to 8. no dice.

    what am I doing wrong?

    I am doing this all in a copy of the live mdb files. Wheen I reopen the live mdb backend, the reelationship is theree - it was not deleted as it is in the copy. So I don't think ACCESS is getting confused from the copy to the NONcopy?


    So as I asked,

    1) how do I change the 6 to an 8 in the client table IF2 field?
    2) will doing so damage the old data? It doeesn't seem as though it would
    3) is a resplitting neecessary oncee you show m how to do it? I should think all I need to do is rerun link manager
    Last edited by rogeye; 11-17-2017 at 11:21 PM. Reason: clarification

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you build the IF2 field as a Lookup type in the table using the Lookup Wizard? I NEVER build lookups in table. I recently did a test of building a lookup type field in table with the wizard and discovered that even if I changed the control type property in the table to textbox and made sure there was no relationship link in Relationships window, there was still a 'relationship' hiding somewhere. Seem to remember I couldn't even delete the field. Compact & Repair didn't seem to help. I looked in system tables and think I finally gave up finding the relationship and built a new table and copied all the data.
    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
    rogeye is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Nov 2017
    Posts
    36
    thanks - -that is a helpful reeply

    I built this mdb many yeears ago. I am an amateur plus I cannot remeember. Likely I did use the wizard.

    please be more descriptive how I can have a lookup display control as a COMBOBOX without using a wizard?

    how do I "copy over" data (10,000 records) ?

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I just put combobox control on form and set its properties.

    My database is a very small one I just use to test stuff. Doesn't really matter if it gets demolished.

    Before you resort to desperate measure of replacing table, confirm that the experience I had will replicate for you. Make sure the field does not have lookup settings in table and then Compact & Repair on backend. Maybe it will work for you and you can change the field size. If not, next build a new field in table and copy data from the IF2 field to this new field. See if you can delete the old field and rename the new one.
    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.

  10. #10
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You can't delete a relationship from a back end copy and expect that it will affect the live database. Suggest you make test copies of the fe and be. Then in the fe copy, use the linked table manager to examine the links to the be. If they are linked to the file path of the live database as they should be, you must re-link them to your fe copy otherwise you risk affecting the live version while you are playing around.

    Once you are sure you won't affect the live database, attempt to delete the relationship. Then set the field size for each of those fields so that they are the same. See if you can add data in both table fields before you reestablish the relationship. You will have to use Refresh All on the ribbon if you have both tables open at the same time and want to see the changes. If all is well after that, you can reestablish the relationship and test again as just noted.

    Assuming all is OK, you know how to fix it in the live version. You will have to make sure everyone is out of the db when you go to fix this. You should open the be tables of the live version exclusively. You do this by navigating to the file from the Access Office button and choose Open Exclusive in the file dialog.

    To get any type of control without a wizard, you turn the wizard off. While in design view of a form or report, on the Design tab of the ribbon are all the available controls. You would have to have at least a minimal understanding of their properties and how they need to be set. These properties are on the property sheet, which you may have to open from the Tools tab of the ribbon.

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

Similar Threads

  1. Replies: 21
    Last Post: 09-29-2017, 01:30 PM
  2. Replies: 6
    Last Post: 06-26-2015, 08:55 PM
  3. Replies: 17
    Last Post: 03-16-2014, 10:52 PM
  4. Replies: 3
    Last Post: 12-27-2013, 02:33 PM
  5. Replies: 1
    Last Post: 03-03-2012, 10:17 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