Simple one

[HandSam]

I have a simple database that is supposed to manage tenants, houses and rent payment.
I input House details with fields as HouseID, HouseName, and ifOccupied(yes/no) fields.
THere is then another table that stores tenants details as they come in to rent the houses.
The house field in the tenants table is a lookup to the house field in the houses table.
If a tenant has taken a certain house, maybe house 005, the system should mark the house 005 as occupied in the houses table. ie, the yes/no field should be updated to yes or 1. If a house is vacated by a tenant, the house should be marked as vacant.
I have tried looping in the onload event of the houses table, but it is not working. I do not know if I should use record sets, but I really need an easy way to do this. Any assistance will be thankfully received

Handsam

 

[MarkK]

Are you tracking time somewhere? Like, tenants occupy a house on a date. Rent is due on a date. It's not just a matter of IF a house is occupied, but it's a matter of when it was occupied. I think your system will be easier to build if you think about time and keep track of events as they occur in time.

I would expect to see a tenants table and a properties table for sure, but then there should be a rental agreement table, with the date the agreement starts, the amount of rent, and any other rental agreement facts and dimension, and, just like in reality, tenants are related to properties through that intermediate rental agreement. Then you have an occupancy date and you can use that to calculate rent, and rent increases and pro-rates and things.

And then it's your rental agreement table that is the work horse, and then rent payments can become child records of the rental agreement. So you can calculate how much rent you collected for each property between various dates.

Makes sense?

 

[Beetle]

Assuming that your rental details table has start and end dates for the term of the rental contract, then you would likely do this in the After Update event of that form. The logic being that if the newest rental record for a given house has a start date, but no end date, then it is occupied. If the newest rental record has and end date, then it is vacant. You wouldn't even necessarily need the Yes/No field in the houses table. You could just display the status in a calculated control on a form with DLookup using the same basic logic.

 

[HandSam]

I get your point Beetle and lagbolt. I had not seen it that way. I think, just as you say, whether or not a house is vacant will be determined by the rental record/ agreement table. I will therefore need to include the timing in the system. I will get back when the code gets there.
Thanks alot.