Form won't allow update

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi,
I have a form(datasheet view) which shows all dogs currently in
inventory. This form previously allowed updates or edits to fields on the
form. I recently changed the record source of the form to a union query
instead of a select query and now my form will not allow updates. Could
using a union query as the record source be why my form is no longer
editable? One of the queries in the union query uses a Make Table query as
part of its makeup.

The form shows both dogs that have never been sold and dogs that may have
been sold and then returned and available for sale again. Dogs could even
have been returned twice if they were sold twice. The reason I changed the
record source is because if a dog was returned twice, it would show up on
the form twice instead of once. Using the union query as the record source,
these dogs only appear once instead of twice but the form is not editable.
The reason I used the union query is that I have different limiting criteria
with returned dogs. How can I make my form editable again?

Joan
 
Hi,
I have a form(datasheet view) which shows all dogs currently in
inventory. This form previously allowed updates or edits to fields on the
form. I recently changed the record source of the form to a union query
instead of a select query and now my form will not allow updates. Could
using a union query as the record source be why my form is no longer
editable? One of the queries in the union query uses a Make Table query as
part of its makeup.
Click to expand...

Yes. Union queries are never updateable (since if you were to add a
record, it's impossible to determine which table you want to add it
to).

A MakeTable query CANNOT be part of a Union query; it doesn't return
any records! It is an "action" query which can be run, creating a new
table - but it cannot be used as part of a union query, or as the
recordsource for a form. I'm not sure just what you're doing!
The form shows both dogs that have never been sold and dogs that may have
been sold and then returned and available for sale again. Dogs could even
have been returned twice if they were sold twice. The reason I changed the
record source is because if a dog was returned twice, it would show up on
the form twice instead of once. Using the union query as the record source,
these dogs only appear once instead of twice but the form is not editable.
The reason I used the union query is that I have different limiting criteria
with returned dogs. How can I make my form editable again?
Click to expand...

By not using a UNION... :-{(

What was the Recordsource of the form before? If a dog is returned, do
you create a new record (one that you don't want to see) in the Dogs
table? I'd suggest having the Dogs table contain one record for each
dog, regardless of whether that dog has been sold or returned; a Sales
table related one-to-many to the Dogs table would record sales (and
returns).
 
Hi John,

Thanks for your reply. I figured out a way around this by putting a new
field in the Dogs table called FinalStore which acts as a switch for the
form. If the FinalStore field Is Null then the dog's record belongs on the
form and if it Is Not Null then it doesn't belong on the form. That way I
don't have to reference the DateSold field in Invoices or the InvoiceNumber
field in either Invoices or the Sales table to determine the state of the
dog. State being whether it has been sold or should be listed on the
inventory form. My form is working as it should.

I mispoke when I said that I had tried putting a Make Table query in my
union query. It wasn't actually the query but rather the table made from the
query.

Thanks again,
Joan
 
Back
Top