Hello all! I have several fields in a query that I want to be able to click a button on my switchboard at the beginning of each month that will reset fields that I select back to 0 or to an empty string, such as Rent Paid and Date Rent Paid (set as currency and date/time).

I've attempted to set up a macro for this. I've set the macro name to Reset Month. I'm using SetValue for action and pointing it to the rent paid field in my tenants query. For the expression I'm using =0. I'm assuming for reseting the date you would use ="".

But basically I want to set a macro to reset the rent paid field and clear out the date on the date rent paid field. Can anybody help me?

Thanks!
David Somers

I meant all those "=0" and "=""" without the =.

Thanks!
David Somers

In the OnClick event of the button try...

Private Sub YourButton_Click()

Me.YourRentPaidField = ""
Me.YourDateRentPaidField = ""

End Sub


When you say "will reset fields that I select", how are you selecting the fields? With a CheckBox?

HTH
IMO

Does this mean that payments are entered automatically and you're correcting existing entries?

Sorry.. that came out wrong. I have 2 fields that I want to reset using this macro. I have [Rent Paid] and [Date Rent Paid]. [Rent Paid] is set for currency and [Date Rent Paid] is set to Short Date. When I reset I was [Rent Paid] = 0 and [Date Rent Paid] to be empty.

How can I configure the macro to accomplish this?

Thanks!
David Somers

Also, the reasoning behind this resetting is because at the first of every month I need the Rent Paid to be 0 and the Date Rent Paid to be cleared out so I can enter new data for the next month.

Thanks!
David Somers

Forget the macro, use this code in the OnClick event of the reset button...

Me.[Rent Paid] = 0
Me.[Date Rent Paid] = ""

HTH
IMO

How do I get to the switchboard code to insert this code?

Thanks!
David Somers

Is this a switchboard created by the switchboard manager or have you created it yourself?

IMO

Switchboard manager.

Thanks!
David Somers

Ok, Open the form where you view the data in design view. Create a button cancelling the wizard. Right click on the button and select properties, scroll down to "OnClick" and from the dropdown select "Event Procedure". Just to the right of the drop down arrow is another button " ... " click on that to open the vb editor. Paste the code between the " Private Sub BlahBlah_Click() " and the " End Sub ". Close the editor and the form saving the form. Re-open and give it a try.

HTH
IMO

You mean open the switchboard form? I have several forms where the Rent Paid and Date Rent Paid are used.

Thanks!
David Somers

No, not the switchboard. Select one of the forms that use Rent Paid and Date Rent Paid (Like an edit accounts form) and place the button on there.

HTH
IMO

What if I took that macro and converted it to VB code? Would that do the same thing and I could run the code from my switchboard? Reason why I ask is I got a MsgBox that comes up confirming the reset.

Thanks!
David Somers

I want to reset only those 2 fields. I tried converting the macro to VB code and renamed the module Reset Month Code. The first function that appears in the code is Reset_Month(). It handles displaying a MsgBox and then going to reset the code if confirmed. When I try to run this code using Run Code in the switchboard, what name should I use for the code in order for this to work?

Thanks!
David Somers

Change the code to...

If MsgBox("Are you sure you want to reset the selected record?", vbYesNo, "Reset?") = vbYes Then
Me.[Rent Paid] = 0
Me.[Date Rent Paid] = ""
Else

End If

HTH
IMO

How would I call this code in switchboard manager using the Run Code option if I name the module Reset Month Code? However, in the module, the function name is Reset_Month().

Thanks!
David Somers

You can't call it from the switchboard manager, how would it know which record to reset? Or do you only have one record in the entire db? The switchboard is related to no records, it acts as the control panel for your other forms etc. It is possible to reset ALL the fields in the table from the switchboard wth the click of a button.

HTH
IMO

I'm not sure how you've set up your db, if you only enter rents as they're received then why do you need to re-set them? I think your structure is flawed. You should have a separate table recording payments received as a one to many relationship with tenants and linked by TenantID

Ok, I have a form created and linked to my tenants table. When I run the code from above there's no errors or anything, but when I got back to check to see if anything has been set back to 0 or cleared, there is nothing. The data has not changed.

Any ideas?

Thanks!
David Somers

If the amount of rent paid is never set back to 0, then how do you know if charge a late fee or not?

Thanks!
David Somers

On further inspection that code adds entries to my database with $0.00 rent paid instead of changing the entries for the tenants.

Any ideas?

Thanks!
David Somers

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

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

One thing however, I need if a new tenant moves in after the 5th of the month, to have my Balance field in my query to not charge them a late fee. Not sure how that will change things...

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

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! :D 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

Originally posted by dsomers
Yes, I've tried demos of other rental software and in my opinion they suck.

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

So what am I doing wrong in my program? How do I implement the points that I made earlier?

Thanks!
David Somers

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

Here it is... Any help is really appreciated! :)

Thanks!
David Somers

:( No file?

--Searching Mac

Sorry, here it is.. had to compress it and take some extras that don't pertain to this out so I could upload.

Thanks for the help!

Thanks!
David Somers

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:
Originally posted by Rich
You do not need 4 tables for Apt Numbers, again One Table for Building and one for Apts. The PK from Buildings becomes the FK in Apts. That way you can have as many Buildings with as many Rooms as you like without having to create more tables

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:Originally posted by Rich
You should have a separate table recording payments received as a one to many relationship with tenants and linked by TenantID


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...
[list=1]
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 (http://support.microsoft.com/default.aspx?scid=kb;EN-US;283698) 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

Originally posted by dsomers
Having a history is definitely something I would like to implement later.

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

You're still trying to store a balance and a running balance, both can be calculated, your history table field Paid This Month should be AmntPaid, since your storing the date paid. You need to add a PK to tblApartmentInfo, that then gets stored in the tblLease:
Avoid special characters and spaces in field names too, Access doesn't like them

Ok. How does this look:

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
ApartmentID
Move In Date
Lease Expiration Date

tblHistory:
TenantID
AmntPaid
Date Rent Paid

tblRents:
TenantID
Additional Rents
Additional Rent Description

tblBuildingInfo:
Building ID
Building #

tblApartmentInfo:
ApartmentID
Apartment #
Rental Rate
BuildingID

I'm assuming by putting a primary key called ApartmentID from tblApartmentInfo into tblLease will then provide the relationship from the tenant to the correct Apartment they are located in. Also, I understand able special characters and spaces in field names. I just type it out like that here so everyone could understand what I'm talking about. :)

Thanks!
David Somers

One more question I have is do I need a primary key for my tblDeposit and tblHistory tables? My first impression is no.

Thanks!
David Somers

Here is a copy of the new version with the correct tables and with the couple of changes Rich mentioned. The question I'm having is does my relationships look complete and correct?

Thanks!
David Somers

post a 97ver please

Here's the 97 version.

Thanks!
David Somers

One more question I have is with this table setup, how do I implement cascading combo boxes for building number and apartment number? That way I can select from a drop down menu which building number the tenant stays in and have the correct corresponding apartment numbers for that building number selectable from the apartment number drop down menu. I had this implemented when I had a table for building numbers and separate tables for each building's apartment numbers.

Thanks!
David Somers

D -- Not sure which of these many tables I'm supposed to be commenting on. Suggest you start a fresh project and import only the "current" version of your structure.

Based on what I see, though, I would say you can consolidate substantially. You don't need a separate table for Rent, Additional Rent, Deposits, etc., etc. One table named tblTenantTransactions will do, with a related table called tblTenantTransactionTypes.

As for cascading combos, search the forum archives. This has been covered many times.

--Confused Mac