Big query problem (Access 2000)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

[indent]

---

you cant add data to a table through a form with a query as the recordset.

---

[/indent] 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.

Reply | Quote

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”?

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Charlotte,

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

Its back in the for me.

Reply | Quote

Ahhh, I just realized how stupid my response was. Many apologies. Time for that

Reply | Quote

Hi Leighcoopster…

Maybe some of the Truly Wonderful gurus on here can help with that description, but I want to help if I can …
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??

Reply | Quote

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.

Reply | Quote

Uhh… it should be DISTINCTROW, instead of DISTINCTWORD.

Reply | Quote

You’re absolutely right. Thanks for the catch.

Reply | Quote