Clearing a form with delete query?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to use a delete query to clear out all of the records in a query
except for the one that I am searching for in a form. The logic I am using
now in the "where" field is "Is Not Null". Unfortunately this is not working.
What logic should I be telling Access to use? I know this is quite general,
so if you need anymore info, let me know.
 
WHERE YourField <> Forms!frmYourFormName!YourControlName.Value

That should do it, Rob.

Sam
 
Rob said:
I am trying to use a delete query to clear out all of the records in a query
except for the one that I am searching for in a form. The logic I am using
now in the "where" field is "Is Not Null". Unfortunately this is not working.
What logic should I be telling Access to use? I know this is quite general,
so if you need anymore info, let me know.
Click to expand...

You mean you want to delete the contents of the underlying table(s) for
all records that show in your query result except that one?

DELETE
FROM MyTable
WHERE FieldName<>Forms!MyForm!MyField
AND...

I'd be careful with stuff like this... you could end up deleting a lot
of records you didn't mean to. Or are you just intending to clear the
values in your current record/form? hard to tell. You don't really
delete anything from a form.... it gets deleted from the underlying
table.

So could you explain what you're trying to do again? Either:
(a) deleting records from a table
(b) clear the contents of fields on a form
 
Ok. I have a table that has about 200,000 some odd records in it. Alot of
these records are "duplicates" in the sense that they are the same product,
but the different records show different activities for that particular
product. For instance, month to month inventory status and returns.

I'm trying to set it up so that when I search for one product number on a
form, the query only returns the records for that particular product. Then be
able to print these results on a report. My assumption was that a delete
query would do this, but am open to the idea that this is the wrong approach
if there is a better way.
 
You do not want a DELETE query. Delete queries permanently REMOVE the
records from the table where they are stored.

It sounds as if you want a query with criteria as the source of your form.
You want to change the criteria to show you only records with a specific
product.

This may be a bit complex for you, but as an example take a look at
http://allenbrowne.com/ser-62.html
 
Back
Top