query behind main form with subforms

[p-61]

I have a main form that is based on a query. The subforms are based on
tables. All are linked to the ID field of the query behind the main
form. There is no code whatsoever.

The query behind the main form is a simple one-table query. Whether
the query is unique values only, or unique values properties set to
"no" the results are the same.

The problem is that the main form will not bring up any records unless
unique values are set to "yes" - which makes it read-only, no editing,
no updating, no adding in the main form (the subforms *are*
updatable). If I set the unique values to "no" the query is updatable
but the form comes up blank, as if I'd opened it from a switchboard in
"add mode"

I want to bring up the records and be able to edit, update or add to
both the main and subforms.

As you can probably tell, this is my first experience with subforms. I
haven't come across how to get around this in my reading so I'll go
over to the sample databases and try to figure it out but if someone
here knows how to resolve this, I'd appreciate it.

 

[John Vinson]

The query behind the main form is a simple one-table query. Whether
the query is unique values only, or unique values properties set to
"no" the results are the same.

The problem is that the main form will not bring up any records unless
unique values are set to "yes" - which makes it read-only, no editing,
no updating, no adding in the main form (the subforms *are*
updatable). If I set the unique values to "no" the query is updatable
but the form comes up blank, as if I'd opened it from a switchboard in
"add mode"

That's very odd. Please post the SQL of the query. There's *something*
in this query preventing updates, or possibly your database is
corrupt! Have you tried deleting and recreating the query?

 

[pedro]

That's very odd. Please post the SQL of the query. There's *something*
in this query preventing updates, or possibly your database is
corrupt! Have you tried deleting and recreating the query?

SELECT TCDB.CID, TCDB.CONTACT, TCDB.CODE, TCDB.[LAST], TCDB.[FIRST],
TCDB.[INI], TCDB.[NICK], TCDB.[LEGAL TAX NAME], TCDB.[M #], TCDB.[M
N/S/E/W], TCDB.[M STREET], TCDB.[H APT/STE], TCDB.[PO BOX], TCDB.[M
CITY], TCDB.[M ST], TCDB.[M ZIP], TCDB.[M Z+4], TCDB.[M COUNTRY],
TCDB.MAIL, TCDB.[RTN MAIL REASON], TCDB.[RTN MAIL DATE],
TCDB.SUBDIVISION, TCDB.[APN/AREA #], TCDB.[T #], TCDB.[T N/S/E/W],
TCDB.[T STREET], TCDB.[T APT/STE], TCDB.[T PO BOX], TCDB.[T CITY],
TCDB.[T ST], TCDB.[T ZIP], TCDB.[T Z+4], TCDB.[T COUNTRY], TCDB.[MAX
PRICE], TCDB.[MIN PRICE], TCDB.[TC AGENT], TCDB.[LAST SALES PRICE],
TCDB.[LAST SALES DATE], TCDB.POOL, TCDB.[LOT SQUARE FOOTAGE],
TCDB.[SQUARE FOOTAGE], TCDB.[YEAR BUILT], TCDB.COMMENTS,
TCDB.[ANNIVERSARY DATE], TCDB.RATE, TCDB.[B MO], TCDB.[B DAY],
TCDB.[SOURCE LEAD], TCDB.HOME, TCDB.[BUSINESS], TCDB.[CELL],
TCDB.[FAX], TCDB.[PAGER], TCDB., TCDB.[COMPANY],
TCDB.[OCCUPATION], TCDB.[REFERENCE 1], TCDB.[REF ADDRESS 1], TCDB.[REF
B/S 1], TCDB.[REF CROSS PERSON 1], TCDB.[REF AGENT 1], TCDB.[REF
BROKER 1], TCDB.[REFERENCE 2], TCDB.[REF ADDRESS 2], TCDB.[REF B/S 2],
TCDB.[REF CROSS PERSON 2], TCDB.[REF AGENT 2], TCDB.[REF BROKER 2],
TCDB.CHILDREN, TCDB.DOGS, TCDB.CATS, TCDB.[OTHER ANIMALS]
FROM TCDB;

originally I had it sorted by Last and specified that the contact =
"client" but when I took that out it made no difference.

If it was corrupted wouldn't I get a message to that effect when I
compact it? It's version 2002, btw.

And thank you!

 

[John Vinson]

originally I had it sorted by Last and specified that the contact =
"client" but when I took that out it made no difference.

Very odd. And despite the fields named MAX and MIN, it *is* a table,
not a Totals query? If you open the table itself in datasheet view is
it editable?

If it was corrupted wouldn't I get a message to that effect when I
compact it? It's version 2002, btw.

Well... not necessarily. You might try creating a new database and
using File... Get External Data... Import to import everything to get
clean systems tables.

 

[pedro]

Well... not necessarily. You might try creating a new database and
using File... Get External Data... Import to import everything to get
clean systems tables.

That was it, all right, I wish I'd come here sooner instead of
fighting with it. I virus-checked too, I just never had an Access
database corrupt without the compact utility telling me. It works just
fine now - thanks for your help!