Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem after data import (Access 97)

    I have successfully imported data from an Excel spreadsheet to my new Access DB. Now for every record I try to view I get the error message that the 'value of data entered is not valid for this field'. All of the data looks correct. I checked the properties of each field, and they are correct in length and type. Any ideas on how to fix this?
    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem after data import (Access 97)

    Open the imported table in design view and check the following

    1. Has a validation rule been set for any field in the table (or for the table as a whole)?
    2. (In the Lookup tab) Has any field been set to be displayed as a combo box?

  3. #3
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem after data import (Access 97)

    No validation rules are set.
    Yes, there are several combo boxes. I made sure all the data for importing fit the values defined before I imported it. The correct values are appearing in those combo boxes.
    There is an unbound combo box at the top of the form for looking up records by LastName. As I move through the records, the combo retains the value of the last person looked up. Can this be the problem?
    Many thanks

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem after data import (Access 97)

    The unbound combo box is almost certainly not the problem.
    There might be a conflict, though, with one of the bound combo boxes, if the bound column refers to a field of a different type than expected. This probably sounds cryptic.
    Example: a field contains an integer. A combo box is used to look up descriptions for these integers in another table. The usual setup is a combo box with two columns: the first is the integer value, and the second is the description; the first column is hidden by setting its width to 0 in the ColumnWidths property. If the Bound Column is 2, Access will try to store the description in the (integer) field. In this example, the Bound Column should be 1 (which is the default value, by the way)

  5. #5
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem after data import (Access 97)

    Ah, yes, I see. The data in every combo box is text. The bound column is 1, as you said, with a width of zero. Should I change the bound column to 2?
    I have set up a unique ID number for each of the items in the table the combo takes its values from. I guess that is the column 1 value. Perhaps I should delete the ID number .
    Thanks for your help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Problem after data import (Access 97)

    The usual setup in such cases is to store the unique ID number in the table and to display the corresponding text by means of a combo box. You could change your design this way, but it would be extra work. I don't know what repercussions it would have for the rest of your database. If you want to leave the text values in the table, you should either set the bound column to 2, or scrap the ID numbers entirely, because they aren't being used anyway.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    106
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problem after data import (Access 97)

    Thanks Hans
    I really appreciate your help (and being able to use this forum!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •