Resetting Fields with macros
- Thread starter dsomers
- Start date
R
Rich
Guest
If you calculate the dates due,a simple query can determine wether the rent has been paid or not, if the entry does not exist then no payment has been made, you don't have to record a zero payment
I don't think you're understanding what I'm getting at. I'm not trying to record a 0 payment. Lets say you pay $350 for this month's rent. At the end of the month, I want your rent paid to go back to 0 because obviously you paid for 1 month's rent, not 2. So that field for you needs to be reset to 0 so when you come to pay next months rent, I can change it to $350. But, always start with a 0 balance at the beginning of each month.
Thanks!
David Somers
Thanks!
David Somers
R
Rich
Guest
No, you should calculate the rent due based on the DateDiff between the StartOfTenancy and Date(), it's a simple calculation that doesn't require re-setting anything
Ok.. I think I see what you're getting at. But how do I code that in? There are 4 situations that I have. Here they are:
1. On first day of each month, have rent posted to MonthlyRent. Have place to enter additional rents.
2. On 6th day of month, auto post 5% of MonthlyRent to anybody that is past due. Also autoprint first late notice.
3. On 11th day of month, auto print inhouse eviction notice.
4. On 16th day of month, auto print eviction proceeding letters and post $75 to tenants balance for court fees and filing fees.
I realize that the autoprinting of my word documents for late notices makes this a VB question, but just for having the Rent Paid field to reset back to 0 each month, how would you code that in the query I have set up?
Thanks!
David Somers
1. On first day of each month, have rent posted to MonthlyRent. Have place to enter additional rents.
2. On 6th day of month, auto post 5% of MonthlyRent to anybody that is past due. Also autoprint first late notice.
3. On 11th day of month, auto print inhouse eviction notice.
4. On 16th day of month, auto print eviction proceeding letters and post $75 to tenants balance for court fees and filing fees.
I realize that the autoprinting of my word documents for late notices makes this a VB question, but just for having the Rent Paid field to reset back to 0 each month, how would you code that in the query I have set up?
Thanks!
David Somers
To further the discussion on this, I need help on a couple of things that are automated and one that has to be set to use with a command button. Here they are:
1. Set Balance to 0 at the beginning of each month and if there's an outstanding balance, add that to Prev Balance. This can be done with an equation in the query, but not sure how to do it. In C++ it'd look like Prev. Balance = Prev Balance + Balance. This must be automated.
2. I need to be able to click a button on a form and add the Late Fee to the balance of those that are outstanding.
3. I need to make sure that the Rent Paid and Date Rent Paid for all my tenants is cleared at the beginning of each month. This must be automated too.
I apologize for the repeat posts on this.
Thanks!
David Somers
1. Set Balance to 0 at the beginning of each month and if there's an outstanding balance, add that to Prev Balance. This can be done with an equation in the query, but not sure how to do it. In C++ it'd look like Prev. Balance = Prev Balance + Balance. This must be automated.
2. I need to be able to click a button on a form and add the Late Fee to the balance of those that are outstanding.
3. I need to make sure that the Rent Paid and Date Rent Paid for all my tenants is cleared at the beginning of each month. This must be automated too.
I apologize for the repeat posts on this.
Thanks!
David Somers
directormac
Occasional Presence
- Local time
- Today, 00:15
- Joined
- Oct 24, 2001
- Messages
- 259
Other Options
Please don't take offense at this, because I truly mean it in a helpful spirit...
(1) Have you considered purchasing an off-the-shelf piece of rental property management software? I'm not trying to discourage you--if you WANT to do this in Access as a learning exercise, then keep plugging!
But if your primary goal is to facilitate property rentals, you may find that the price of the software is less an expense than the drain on your time... A quick dogpile search revealed many packages, almost all of which had free demos and many of which seemed reasonably priced...
(2) If you DO want to set this up as a way of learning Access, I'd recommend stepping back and re-examining the design and goals of this project before investing more time in trying to get these particular buttons to work. Start with a pen and a paper and make a list of what you want this application to DO for you FIRST. Once you have that stated in very detailed, concrete ways, THEN start thinking about what buttons you need. Can't even guess how many hours (years...) I've lost getting fixated on making some doo-dad work when (if I had started with a legal pad instead of a laptop) I would have seen it wasn't the right path from the beginning.
Just some random thoughts regarding higher-level prioritizing...
--Wastrel Mac
Please don't take offense at this, because I truly mean it in a helpful spirit...
(1) Have you considered purchasing an off-the-shelf piece of rental property management software? I'm not trying to discourage you--if you WANT to do this in Access as a learning exercise, then keep plugging!
But if your primary goal is to facilitate property rentals, you may find that the price of the software is less an expense than the drain on your time... A quick dogpile search revealed many packages, almost all of which had free demos and many of which seemed reasonably priced...(2) If you DO want to set this up as a way of learning Access, I'd recommend stepping back and re-examining the design and goals of this project before investing more time in trying to get these particular buttons to work. Start with a pen and a paper and make a list of what you want this application to DO for you FIRST. Once you have that stated in very detailed, concrete ways, THEN start thinking about what buttons you need. Can't even guess how many hours (years...) I've lost getting fixated on making some doo-dad work when (if I had started with a legal pad instead of a laptop) I would have seen it wasn't the right path from the beginning.
Just some random thoughts regarding higher-level prioritizing...
--Wastrel Mac
Yes, I've tried demos of other rental software and in my opinion they suck. Therefore, I'm writing my own to fit my needs. I know what I need, its just that I'm learning how to do this and some things I don't know how to implement. That is why I'm here asking for help.
Thanks!
David Somers
Thanks!
David Somers
directormac
Occasional Presence
- Local time
- Today, 00:15
- Joined
- Oct 24, 2001
- Messages
- 259
dsomers said:
A perfectly valid reason, and one that has inspired a couple of my own projects. This makes it all the more important that you start with a list of very specific goals to make sure that YOURS doesn't suck as well...
--Goalie Mac
directormac
Occasional Presence
- Local time
- Today, 00:15
- Joined
- Oct 24, 2001
- Messages
- 259
Looking back at your earlier posts, I think (as was suggested earlier) that you may have a problem with the basic structure of your tables and their relationships. Zip up your db and upload it so those trying to help have a better point of reference.
--Structured Mac
--Structured Mac
directormac
Occasional Presence
- Local time
- Today, 00:15
- Joined
- Oct 24, 2001
- Messages
- 259
No file?--Searching Mac
directormac
Occasional Presence
- Local time
- Today, 00:15
- Joined
- Oct 24, 2001
- Messages
- 259
Return to the Beginning
You definitely need to rethink your structure. On the current topic, the way you're trying to set this up means that your DB will have no "memory" of any past transactions--just whether the tenant has paid their current month's rent or not. Also, as Rich advised you on another thread:
I would again advise you to step back and rethink your design. Start by asking yourself "what do I want this thing to DO for me?" before you worry at all about tables, relationships, forms, what have you. Make your answers very detailed and very specific.
Once you get to the construction phase, I'd suggest you have a separate table called tblTenantTransactions where you can store all the charges and payments for your tenants. "Rent Due" then becomes a type of charge transaction that is repeated monthly. This has the added benefit of allowing you to see a particular tenant's transaction history at any time. Looking back, I see Rich also suggest this as well:
Having said all that, if you choose to ignore all the advice and insist on having a button on your switchboard to reset the Rent Paid value of every record...
You definitely need to rethink your structure. On the current topic, the way you're trying to set this up means that your DB will have no "memory" of any past transactions--just whether the tenant has paid their current month's rent or not. Also, as Rich advised you on another thread:
Rich said:
I would again advise you to step back and rethink your design. Start by asking yourself "what do I want this thing to DO for me?" before you worry at all about tables, relationships, forms, what have you. Make your answers very detailed and very specific.
Once you get to the construction phase, I'd suggest you have a separate table called tblTenantTransactions where you can store all the charges and payments for your tenants. "Rent Due" then becomes a type of charge transaction that is repeated monthly. This has the added benefit of allowing you to see a particular tenant's transaction history at any time. Looking back, I see Rich also suggest this as well:
Rich said:
Having said all that, if you choose to ignore all the advice and insist on having a button on your switchboard to reset the Rent Paid value of every record...
- Open your switchboard form in design view.
- Add a form footer (to keep from interfering with your switchboard items).
- In the new footer, create a new command button.
- In the OnClick event of your new button, add a VBA function that loops through all records in the table, adding current balances to previous ones and setting values to whatever you want for each field.
[/list=1]
The details on how to accomplish all the above can be found by consulting Access Help and the AccessWorld forum archives.
However, it's not going to be long after you solve that problem that you run into another one caused by the structure problems. If you haven't already, read this article for an overview on designing a relational DB.
--Backtrack Mac
So I can link my tenants to their building number, apartment number, keep a history for each of my tenants by redoing my structure to link through the TenantID?
Having a history is definitely something I would like to implement later.
Any suggestions on from what you've seen from my db, how I should break this down? What tables I need and do I link them by a TenantID? I think somebody posted on this, but I can't find the right thread.
I will read the doc file from the microsoft site you've posted and post my own thoughts on how I think it should be broke down and get some feedback. Thanks a bunch, and thanks for bearing with me since I'm pretty new to this!
Thanks!
David Somers
Having a history is definitely something I would like to implement later.
Any suggestions on from what you've seen from my db, how I should break this down? What tables I need and do I link them by a TenantID? I think somebody posted on this, but I can't find the right thread.
I will read the doc file from the microsoft site you've posted and post my own thoughts on how I think it should be broke down and get some feedback. Thanks a bunch, and thanks for bearing with me since I'm pretty new to this!
Thanks!
David Somers
directormac
Occasional Presence
- Local time
- Today, 00:15
- Joined
- Oct 24, 2001
- Messages
- 259
Testify!
This kind of thing is exactly why you want to get your table structure right from the beginning. Adding something new later is almost impossible if your app consists of a million work-arounds. If you have good design & structure, however, you should be able to look at your db and see just where your new idea should fit.
Again, start with a list of what you want it to DO. For example, say one thing you want to DO is be able to track the repair history of any apartment. Don't worry about combo boxes at this point--just about the end results you want: "I want to know the repair and renovation history of any apartment." State it in specific details: "I want to know the date of every service call made on that unit, the nature of the problem, who did the repair work, and how much it cost. I want to be able to see the same information for all units in any one building, so I can tell if it's a unit-specific problem or a whole-building problem. I want to know if the same problem occurs more than once in the same unit or within the same building. I also want to be able to see how much I've spent repairing a building versus how much rent I've made from it"
Then figure out what information it needs to do those things. "I need to know the date of every service call. I need to know which unit had the trouble. I need to know who fixed it. I need to know how much the repair cost." etc., etc...
Group the required info into logical units based on what it's "about". Each table should be "about" one (AND ONLY ONE) thing--one table about buildings, one about units, one about tenants, etc. So when you have a piece of information like "service call", ask yourself if that's about a building, a unit, or a renter... In this case, it's about a unit. Does it make sense to add service call fields to the units table? No, because there might be a lot of service calls for any unit, or one call might be for multiple units, plus the number of calls is open-ended, not a fixed number... so, you need another table that's just about service calls, and since service calls relate to individual units, one field in tblServiceCalls needs to be fkUnitID.
All of this is pen & paper stuff, before you ever open Access. Spend some time figuring that stuff out, THEN ask some more questions after you have some progress to show. That way you'll avoid getting a reputation on the forum as someone who wants it done for you...
Just my two bits worth...
--Preacher Mac
dsomers said:
This kind of thing is exactly why you want to get your table structure right from the beginning. Adding something new later is almost impossible if your app consists of a million work-arounds. If you have good design & structure, however, you should be able to look at your db and see just where your new idea should fit.
Again, start with a list of what you want it to DO. For example, say one thing you want to DO is be able to track the repair history of any apartment. Don't worry about combo boxes at this point--just about the end results you want: "I want to know the repair and renovation history of any apartment." State it in specific details: "I want to know the date of every service call made on that unit, the nature of the problem, who did the repair work, and how much it cost. I want to be able to see the same information for all units in any one building, so I can tell if it's a unit-specific problem or a whole-building problem. I want to know if the same problem occurs more than once in the same unit or within the same building. I also want to be able to see how much I've spent repairing a building versus how much rent I've made from it"
Then figure out what information it needs to do those things. "I need to know the date of every service call. I need to know which unit had the trouble. I need to know who fixed it. I need to know how much the repair cost." etc., etc...
Group the required info into logical units based on what it's "about". Each table should be "about" one (AND ONLY ONE) thing--one table about buildings, one about units, one about tenants, etc. So when you have a piece of information like "service call", ask yourself if that's about a building, a unit, or a renter... In this case, it's about a unit. Does it make sense to add service call fields to the units table? No, because there might be a lot of service calls for any unit, or one call might be for multiple units, plus the number of calls is open-ended, not a fixed number... so, you need another table that's just about service calls, and since service calls relate to individual units, one field in tblServiceCalls needs to be fkUnitID.
All of this is pen & paper stuff, before you ever open Access. Spend some time figuring that stuff out, THEN ask some more questions after you have some progress to show. That way you'll avoid getting a reputation on the forum as someone who wants it done for you...
Just my two bits worth...
--Preacher Mac
Thanks! I worked this weekend on how to reorganize my tables, and here's what I came up with.
tblTenants:
TenantID
First Name
Last Name
Previous Address
City
State
ZIP
DOB
SSN
DL #
Phone Number
Active Status
tblDeposits:
TenantID
Security Deposit
Pet Deposit
tblLease:
TenantID
Move In Date
Lease Expiration Date
tblHistory:
TenantID
Rent Paid This Month
Date Rent Paid
Previous Balance (A running total from first month of tenancy)
This Month's Balance
tblRents:
TenantID
Additional Rents
Additional Rent Description
tblBuildingInfo:
Building ID
Building #
tblApartmentInfo:
Apartment #
Rental Rate
BuildingID
There are 4 questions regarding what I've laid out: 1) Does this look like a good structure for my program? If that answer is yes, then 2) How do I link a tenant to the correct building and apartment number? Do I need a TenantID in both tblBuildingInfo and tblApartmentInfo? 3) How do I hide the TenantID from the user? I want to base finding a tenant on their Building and Apartment Numbers. Give the user one less thing to keep track of. And finally, 4) How do I make the history keep a monthly running balance for each tenant? How do I figure in late fees? If you notice, I didn't put a Late Fee field in any of my tables because of being told and reading that storing calculations is bad practice in db programming.
Thanks for the help!
Thanks!
David Somers
tblTenants:
TenantID
First Name
Last Name
Previous Address
City
State
ZIP
DOB
SSN
DL #
Phone Number
Active Status
tblDeposits:
TenantID
Security Deposit
Pet Deposit
tblLease:
TenantID
Move In Date
Lease Expiration Date
tblHistory:
TenantID
Rent Paid This Month
Date Rent Paid
Previous Balance (A running total from first month of tenancy)
This Month's Balance
tblRents:
TenantID
Additional Rents
Additional Rent Description
tblBuildingInfo:
Building ID
Building #
tblApartmentInfo:
Apartment #
Rental Rate
BuildingID
There are 4 questions regarding what I've laid out: 1) Does this look like a good structure for my program? If that answer is yes, then 2) How do I link a tenant to the correct building and apartment number? Do I need a TenantID in both tblBuildingInfo and tblApartmentInfo? 3) How do I hide the TenantID from the user? I want to base finding a tenant on their Building and Apartment Numbers. Give the user one less thing to keep track of. And finally, 4) How do I make the history keep a monthly running balance for each tenant? How do I figure in late fees? If you notice, I didn't put a Late Fee field in any of my tables because of being told and reading that storing calculations is bad practice in db programming.
Thanks for the help!
Thanks!
David Somers
