Delete Queries, any help appreciated!

[jim_s]

First post, after much lurking.

I'm fairly new to Access, so please go easy on me, everyone!

I'm having problems with deleting records. I have an archive table containing
records, and a 'Search Result" table that runs an append query to take
records out of the Archive and into the Search Results table. For security
reasons of editing/adding new records, this is therefore a 1-to-1
relationship.

One of the options I want to make available in the Search Results form when
viewing the Search Results table is the ability to delete the current record.
Currently, a VB delete command works fine to delete the current record, but
obviously it needs to delete the corresponding record in the archive. The
tables are linked by a PK Ref number.

How do I get it to do this? I obviously can't turn on cascade deletes due to
relationships. The only other way round this seems to be to create a unique
"Search Results" form for each type of search - at the moment the user can
search by 3 or 4 options, which is a VERY simple append query using the
option searced as a Parameter.

I'd be really grateful for any help.

Thanks!

 

[Jeff Boyce]

You're kidding, right? Folks here go easy on everyone <g>...

It sounds like you're trying to move records around. Why?

You might do that if you were working with a spreadsheet, but you don't need
to do that with Access, a relational database.

Instead, if there are records you don't want to see (say, no longer valid,
or past end date or ...), use a query to exclude them. If your record
doesn't have a way to identify these, add a field to hold something like
[DateInvalidated] or [EndDate] or some other fact on which you wish to
select (or select out).

When it comes time to consider/look up/review all those historical records,
do you want to be able to do it from a single table, or do you want to have
to search for all the tables that might hold those "archive" records...?
(hint, pick door #1!)

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

You could run a delete query against the archive table

Dim strSQL
Dim lCurrentRecordNumber as Long

lCurrentRecordNumber = Me.PKRefNumberControl

strSQL = "DELETE FROM ArchiveTable WHERE [PK Ref Number]=" & lCurrentRecordNumber

CurrentDb().Execute StrSQL, dbFailOnError


I might make this a bit more complex and use recordsets and transactions so
that I was assured of both records being deleted, but that is not necessary.

NOW, why do you have an archive table? It is usually better to just mark the
records as archived with a field in the table and then filter out the archived
records in queries unless the archived records are needed in the results.

You can use a boolean field (Yes/No), but I prefer a dateTime field. That way
I can determine when the record was archived. In a query, I just test to see
if the value is null (to return active records), not null (to return the
archived records), or no text at all (to return both archived and active records).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[jim_s]

Thanks for the replies.

There are, in fact, three tables with corresponding forms. The main table is
the Archive table. The second is a blank input table with a blank input form.
This one-to-one works fine - the user enters the details, presses the 'Save
Record' button and a Macro runs to clear the input table and copies it to the
archive via. an append query. Finally, the Search Result table/form pulls
data out of the Archive.

When a record is saved from the input form, it saves the date with a
timestamp (nothing is visible when inputting a new record though). The
timestamp is then visible. but not editable when viewing the Archive.

In the archive, nothing is out of date. It simply stores all of the data that
has been entered.

The problem is the appending when searching. A form asks 'What do you wish to
search by". Each button runs a different query and pulls the data from the
archive into the new search results table. So, I see the solution as either:

1. Create 5 search result forms. If this is done, there is no need for a new
table. The queries run, the form uses the query as the control source and
thus if the user wishes to delete the record, they just need to press Delete.
This doesn't strike me as particularly efficient.

2. Keep the one table and search result. The Delete button runs an SQL query,
which says:
Delete the current record. Also, delete anything that has the same PK in the
Archive table as the record currently being viewed.
I don't know how to do this, though I gathered this can be done with an inner
join query.

The reason it's designed like this is there will be more functionality added
later on, which will include more conventional 1-to-many RD design.

Hope that's some use- I'll try the SQL posted by John and let you know how I
get on!

You could run a delete query against the archive table

Dim strSQL
Dim lCurrentRecordNumber as Long

lCurrentRecordNumber = Me.PKRefNumberControl

strSQL = "DELETE FROM ArchiveTable WHERE [PK Ref Number]=" & lCurrentRecordNumber

CurrentDb().Execute StrSQL, dbFailOnError

I might make this a bit more complex and use recordsets and transactions so
that I was assured of both records being deleted, but that is not necessary.

NOW, why do you have an archive table? It is usually better to just mark the
records as archived with a field in the table and then filter out the archived
records in queries unless the archived records are needed in the results.

You can use a boolean field (Yes/No), but I prefer a dateTime field. That way
I can determine when the record was archived. In a query, I just test to see
if the value is null (to return active records), not null (to return the
archived records), or no text at all (to return both archived and active records).

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

First post, after much lurking.

[quoted text clipped - 21 lines]

Thanks!

 

[jim_s]

Just following up. The SQL code works a treat, many thanks for your help!