By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

Add New Record and Update Last Record (to deactivate record)

P: 2
In my application I have a table called Tenant and I have another table called Leases.
As I add a new Lease to a Tenant I have a Yes/No Field that defaults to yes when I add a new Lease. But I need to deactivate the Old lease record.
Can anyone suggest on how I can accomplish this.

Or recommend a better way to ADD a new Record that will be the ACTIVE Record update the LAST Record to make it INACTIVE when I add new Lease Records.


Thank you, in advance for your help.

Regards,

Manny
Jan 4 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,607
In my application I have a table called Tenant and I have another table called Leases.
As I add a new Lease to a Tenant I have a Yes/No Field that defaults to yes when I add a new Lease. But I need to deactivate the Old lease record.
Can anyone suggest on how I can accomplish this.

Or recommend a better way to ADD a new Record that will be the ACTIVE Record update the LAST Record to make it INACTIVE when I add new Lease Records.


Thank you, in advance for your help.

Regards,

Manny
Assuming your Leases Table is called tblLeases, your Yes/No Field is called [New], and your Primary Key Field is called [ID] then the following SQL Statement will Update all values in the New Field to False except the last Record saved which I assume is the most current lease. Hope this is what you wanted.
Expand|Select|Wrap|Line Numbers
  1. UPDATE tblLease SET tblLease.New = False
  2. WHERE tblLease.ID<>DLast("[ID]","tblLease");
Jan 4 '07 #2

P: 2
Thank You ADezzii.

That is exactly what I need!!!

I will put your recomendation into my code.

Thank You, again and have a great day!!

Regards,

Manny
Jan 4 '07 #3

ADezii
Expert 5K+
P: 8,607
Thank You ADezzii.

That is exactly what I need!!!

I will put your recomendation into my code.

Thank You, again and have a great day!!

Regards,

Manny
Glad to be of assistance..
Jan 5 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Glad to be of assistance..
Hi Adezii

Sorry to be a pain but did you get a chance to set up your PM facility yet?

Mary
Jan 5 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.