Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Posts
    21

    Question Unanswered: Limit To List Not Working

    Good day to everyone,

    I have a Set Field which mainly accepts S/1, S/2, S/3, S/4
    The Set Field Limit To List Property is set to true.

    If I enter record by record, the Limit To List works with no problems.

    However If I paste more than 1 record at a time,

    It accepts other than the S/1, S/2, S/3, S/4 specified.

    Really puzzled. ANyone had this problem before?

  2. #2
    Join Date
    Aug 2004
    Posts
    40
    Hi sotong

    A couple of questions, is your field a combo and is it called "set"?

  3. #3
    Join Date
    Apr 2004
    Posts
    21
    yup its a combo and its called Unit actually, but didnt know that was important.

  4. #4
    Join Date
    Aug 2004
    Posts
    40
    I asked because calling a field "set" is not a good idea.

    OK is this form, if so I'm puzzled about the pasting of of more than one record at a time.

    If it's a datasheet I don't think that limiting to a list works (I could be and quite possibly be wrong here).

  5. #5
    Join Date
    Apr 2004
    Posts
    21
    I have set the conditions in the Tables as well as the form, for some reason could it be that it is an access bug?
    By the way the record source for the combo box is a value list and not referring to a table? does that help?

    Still very puzzled.

  6. #6
    Join Date
    Jul 2004
    Posts
    125
    Limit to List property works in datasheet as well. However, I don't understand what you mean by "pasting" more than one value?

  7. #7
    Join Date
    Apr 2004
    Posts
    21
    I have an excel worksheet with rows 1-10 for example on column A for example.
    I copy the 10 rows at one go and paste it into the access form with the limit to list enabled.
    But even though the source contains other than the value list specified, it still accepts entries.
    However if we go row by row, it works, but if i go more than 1 it doesn't.

  8. #8
    Join Date
    Aug 2004
    Posts
    40
    I don't think pasting applies the same rules as actually entering record by record. Is it possible to get the Excel sheet into a format that it can imported directly?

  9. #9
    Join Date
    Apr 2004
    Posts
    21
    I think that's just plain silly, there must be a way other than using the import function. Being able to paste into the database straight from excel shouldn't be a problem. Well thats what I think, anyone else encountered this before?

  10. #10
    Join Date
    Aug 2004
    Posts
    40
    Hhmmm... I've just created some similar to play with and got the same results. I even set the field to a required field with the limit to list on the combo lookup.

    So yes it is crazy but true... hopefully someone else has an answer for you.

    Good luck, I'll keep watching with interest.

    Howard

  11. #11
    Join Date
    Apr 2004
    Posts
    21
    i know its unbelievable aint it.

    Anyhow hope someone would give us some insight.

  12. #12
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    When you paste multiple records, it is inserting them all at one time and is not checking against the database rules that you have set up. Perhaps this is crazy, but I have found many things in access that do not work the way I want them to. I have had to use alternate methods to get it to work. In your case, I would create an import. Actually, I would have started with an import since it gives me so much more control for checking the data. Yes it is duplication of work, but what is the alternative? You can ask Microsoft to fix it, or wait for them to release the code to open source, so you can fix it yourself. For the sake of productivity, I decided to fix my problems myself and not fret (too much) about this or that not working the way I think it should. I don't think I should sometimes get a 'Module not found' error when I rename a form until it is compacted. I don't think I should have to get file in use errors when compacting or creating an mde when I am on my system and am the only one using the file. I could come up with dozens of examples, but I won't.

  13. #13
    Join Date
    Sep 2003
    Posts
    14
    Can't you just format the data in Excel?

  14. #14
    Join Date
    Apr 2004
    Posts
    21
    The reason is RickyB77, I am dealing with something like around 500 different excel worksheets with a total of 4000+ rows of data. I suppose I could do a replace and find function, but it would still take time to put all the files together into a single excel file for it to make sense.

    Thanks jmrSudbury for sharing your experiences. At least it keeps me aware that access's limit to list function doesn't work if I paste multiple records, among other problems with you highlight to me . Its going to be a real bummer for all the data that I have to work with, guess I will just change it in Access once all the "wrong" data has been inserted.

    Thanks for your time people. Just have to work around it. Will let you know if I come up with anything.

    Cheers.

  15. #15
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    One other solution is to replace the 500 spreadsheets with access forms, so the data is being inserted directly into the database.

Posting Permissions

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