Results 1 to 14 of 14
  1. #1
    New Lounger
    Join Date
    May 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Big query problem (Access 2000)

    I am building a query on two tables, then a form on the query. It was working fine, then all the sudden I cant add results to any query I try to form with more than one table. Parameters seem to be the same. Could I need to reload Access? Or is there another solution?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Big query problem (Access 2000)

    You can build a query against many tables and use that as a recordset to a form to review the data in form view, however, you cant add data to a table through a form with a query as the recordset. If this form is then open and you try to add data to the table, you will most likely get an error message because the table is in use. Try closing the form and adding data to the table if that is what you are trying to do.

    What do you mean by you cant add results to any query I try to form with more then one table?

    I don't think you need to reload access.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    3 Star Lounger
    Join Date
    May 2002
    Location
    Toronto, Ontario, Canada
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big query problem (Access 2000)

    Hi Leighcoopster...

    Maybe some of the Truly Wonderful gurus on here can help with that description, but I want to help if I can <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>...
    Could you give me a bit more information?
    What does the error message say?... Can you give us a few more specs on how the tables are joined and what sort of data is involved?

    What does "all the sudden I cant add results to any query I try to form with more than one table" mean? ... By results, you mean??

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Big query problem (Access 2000)

    <hr>you cant add data to a table through a form with a query as the recordset.<hr>
    Actually, you can Gary, but the query either must be on a single table or it has to be built carefully to make it updateable.

    Any query with an inner join between the tables is probably going to be read-only. Any query that does not use the DISTINCTROW keyword when there are multiple tables will not be updateable. And you generally have to include all the key fields and required fields from both tables before the query will be updateable. The failure to use the DISTINCTROW keyword is the most common reason for multiple table queries to be read-only.
    Charlotte

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Big query problem (Access 2000)

    Were you previously working in Access 97? The query engine changed between versions and the 2000 enginer requires the use of the DISTINCTWORD keyword (Unique Records in the properties dialog) to make a multi-table query updateable, which is a change from Access 97 SQL. This is true even for a delete query. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

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

    Re: Big query problem (Access 2000)

    Uhh... it should be DISTINCTROW, instead of DISTINCTWORD.

  7. #7
    New Lounger
    Join Date
    May 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big query problem (Access 2000)

    Thanks to all that answered my question. I work in a lab. I am an Access rookie. There are two tables, one that I input data into and one that contains specifciations. They are linked by the material number of the product. The query uses the material number and if/then statements to match the material number and indicate whether or not the results I am entering are within specification. Data is entered into a form, which is built on the query. I had been entering data into the form and everything was working fine, suddenly, the query won't allow new data. The form error message states "Cant go the the specified record" when I try to open a new record. The query doesn't have the arrow asterik button usable to add data. Some are working but not others. When I use one table it works, but when I add the second it stops. I checked in parameters of the query, it is set to all unique data. Is there something else meaning the "distinct row"?

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Big query problem (Access 2000)

    You're absolutely right. Thanks for the catch.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Big query problem (Access 2000)

    Unique values (if that's what you mean by "unique data") makes the query non-updateable. Change the setting to unique *records*.
    Charlotte

  10. #10
    New Lounger
    Join Date
    May 2003
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Big query problem (Access 2000)

    Unique records is set to yes. I doesn't work with yes or no. Is there anything else?

  11. #11
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Big query problem (Access 2000)

    Charlotte,

    Thanks for clarifying that and keeping me on the straight and narrow path.... (again - you too Hans)

    Its back in the <img src=/S/cauldron.gif border=0 alt=cauldron width=20 height=20> for me.
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Big query problem (Access 2000)

    Ahhh, I just realized how stupid my response was. Many apologies. Time for that <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    Regards,

    Gary
    (It's been a while!)

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Big query problem (Access 2000)

    Post your SQL and let us have a look at it. Otherwise, all we're doing is guessing.
    Charlotte

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Big query problem (Access 2000)

    Maybe if you post a zipped version of your database, we would probably see the problem much quicker.

Posting Permissions

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