Problem Deleting record

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

Guest

I have a form with an underlying table that I need to be able to delete
records from. This is not a problem in itself, but there is a second table I
want to delete a record from which has the same primary key as the first
table. The help screens on how to delete a record describes a current record
with a numeric key and doing a Seek method for the record to be deleted.
However, the primary key is a text field and I get an error message on the
Seek method that the "Operation is invalid without a current index". Can
anyone tell me please how to delete a record from a table in code where the
text key is known but the table is not bound to the form?

Thanks in advance,
 
You can use a delete query to delete the record from the second table before
you delete the first one.

docmd.runsql "DELETE MyTable.* FROM MyTable WHERE MyTable.Key= '" &
Me.KeyFieldName & "'"
 
To use the Seek method, you have to set an index first. Seek is used with a
Table type recordset (DAO). If you open the recordset as a Dynaset instead,
you can use FindFirst instead of Seek. This doesn't require setting the
index first. An example from the help file on setting the index:

With rstProducts
' Set the index.
.Index = "PrimaryKey"
.Seek "=", Val(strSeek)
End With

This would set the index for the seek to the primary key index of the table.
This tells Seek which field to search. If the index is a multifield index,
you need to provide a value for each field in the .Seek statement or use an
operator such as ">=".

If the two tables are linked in a one-to-one relationship and you're trying
to delete the record from the second table when you delete the associated
record from the first table, it may be easier to set up Referential
Integrity on the link between the tables and set the link for Cascade
Updates and Cascade Deletes. While I generally don't like these, one-to-one
relationships is one place I'll use them.
 
You say the other table has the same *primary* key?

If so, it sounds like you have a one-to-one relatonship. Depending which way
around you built the relationshp (Relationships on the Tools menu), you may
be able to check the Cascading Deletes box, and then Access will
automatically delete the related record for you when you delete the main
one.

You don't want to use Seek: it works only if you open a recordset of type
dbOpenTable, and that works only for local tables (attached tables, nor
queries.)
 
Back
Top