Qry on Qry says Recordset not updateable

[Timothy]

Hello,
Ive got a form with 2 entry boxes. box 1 is attached to
qry1, and box 2 is attached to qry2. Looking for different
criteria.
I now have a 3rd qry, qry3, that compares the results from
the 1st two qrys and displays the records that have
matching primary keys, So I run the 3rd qry which runs the
two others automatically.
Ive made 3 qry groups like this to search a total of 3
tables.
I can run the 3rd qry's seperately and get the desired
results of all three qrys in datasheet view, but Now heres
the problem:
Ive attached the form to a macro that runs the 3rd qrys,
and opens a report to display the results for each 3rd qry.
I now get an error that says RECORDSET NOT UPDATEABLE. And
No datasheet or report is shown, and access locks up.
What am i doing wrong, and how can I fix it???
Thank you in advance

Timothy

 

[John Viescas]

We're not mindreaders. Please post the SQL from your quey - and perhaps a
brief description of your tables (primary and foreign keys).

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[Timothy]

Here is the sql statement:
SELECT SeachQRY.[Record Number], SeachQry1.[Record Number]
FROM SeachQRY INNER JOIN SeachQry1 ON SeachQRY.[Record
Number] = SeachQry1.[Record Number];

The table this query is based on has 34 fields, 29 of
which are searched by the underlying queries.

I have a form with 2 unbound text boxs (entry and entry1)
for criteria input.

I have Seachqry that is connected to Entry and uses this
statement in the criteria section: Is Not Null And
Like '*'+NZ([Forms]![Search Box]!
[Entry],"somethingthatwillneverhappen")+"*"

then Seachqry1 is connected to ENTRY1 and uses this
statement in the criteria section: Is Not Null And
Like '*'+NZ([Forms]![Search Box]!
[Entry1],"somethingthatwillneverhappen")+"*"

Now, I have a 3rd qry MultiSeachqry - SQl statement is
listed at the top- that compares the Primary key ("record
number") of the two queries and shows the only records
with matching primary keys.

I have a macro attached to the search button on the form
that runs the 3rd qry and displays the results as a
report. When I run the query from the QBE view I get the
prompt for criteria, and it works fine. When I run it thru
the form which triggers a macro it says Recordset not
updateable, and locks up.
I tried to put both statements in the qbe (entry and
entry1) but it returns seperate results -all records with
criteria1 and all records with criteria2, not only the
ones with matching "record number"s

My knowledge of SQL is beginner and I have the sql for the
1st qry...but Id have to send it to you. Its not for
public view.

Thanks for your help

Timothy

 

[John Viescas]

Timothy-

Well, if you're trying to use this as the Record Source for a report, Access
shouldn't care one whit whether the query is updatable. It's probably not
updatable because you're joining the table to itself in non-primary key
values. It would help to know what SeachQry and SeachQry1 looked like - and
the basic structure of the table - but you say you can't post that.

The problem might be in the macro. What macro does the command button run,
and what actions are in that macro? If you can open the query outside the
form/macro, then it's probably not the query. You're trying to do some
action against a recordset that is not updatable - such as save the current
row in your search form.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)