Adding/deleting records

[Emma Hope]

Dear All,

I have a table with a number of records including values 'X' and 'U'. I want
to replace these with '99' and '98' respectively, however the table is a
lookup table and won't let me do this directly due to referencial integrity.
So i need to add the new values, update the second table and then delete the
records for X and U. Some issues that may affect this are, the tables are
linked and have a relationship which cannot be deleted.

The major problem is that i need to do this off a button on a form in VBA, i
can write an amend, update and delete query but for reasons (complicated and
lengthy) there must be NO queries, it must be done totally from VBA, please
can anyone help me write some code to do this.

Thank you
Emma

 

[Steve Schapel]

Emma,

This is a macros newsgroup, and as such is not related to VBA. However,
if you're not allowed to use queries, a VBA procedure would work well, I
guess. I don't understand why the Relationship can't be deleted
temporarily, but this will probably not affect the process. It is
difficult to be specific since you didn't give many details, but
something along these lines...
Dim dbs As DAO.Database
Set dbs = CurrentDb
With dbs
.Execute "INSERT INTO YourLookup ( YourField ) Values ( 'X' )"
.Execute "INSERT INTO YourLookup ( YourField ) Values ( 'U' )"
.Execute "UPDATE YourOtherTable SET TheField = '99' WHERE TheField
= 'X'"
.Execute "UPDATE YourOtherTable SET TheField = '98' WHERE TheField
= 'U'"
.Execute "DELETE * From YourLookup WHERE YourField In('X','U')"
End With
Set dbs = Nothing