Add New Item to Invoice Using VBA but Only If Item Isn't already in Invoice Details

[orange]

It still added the LateFee repeatedly.


You need to identify the business rules.
Please see the links in my post #11.

[breakingme10]

The is a storage facility "Buffalo Storage" it has 122 units that Tenants rent from us. Tenants can rent more than one unit. We also have a second storage facility "Custom Boat & RV Storage", some tenants have a unit at custom boat & rv and at buffalo, but they are billed separately. Rent for every tenant is ALWAYS due on the first. If they have not paid by the 5th a 15 dollar late fee per unit is added ( no additional late fees are added). so if a tenant pays on the fifth no late fee, if they pay on the 6th late fee is added.
Yes my relationships are not working correctly and I have no idea how to fix it all without starting from scratch. The real estate property management template is a great alternative but there is no invoicing and billing with that. If i can add my invoicing and billing to that template i will be in great shape, so i think that is what i am gonna try and do. So i still have code problems with my frm_InvoiceDetails saying that a late fee is already added when there is not one and i think that is because it is searching the entire tblInvoiceDetails and seeing that other invoices have late fees. i'm not sure how much more i can describe our facilities. we don't charge extra for units that have openers on them or anything, but i do need to note which units do so that i know to give them a remote. my brain wants to explode.
as i said here at work i'm the go to woman for anything computer related, and this is killing me.

a current situation i have at the moment is a customer only paid for ONE of his units this month. so i add a late fee for only one unit instead of on both.

[breakingme10]

It still added the LateFee repeatedly.
You need to identify the business rules.
Please see the links in my post #11.

see above post

[atuhacek]

You should have invoice linked to tenants. Payments to invoice invoice should include storage facility. Each month should create a new invoice for each tenant. It would be possible to have multiple payments/invoice. Then run a function on the 6th of each month that does uses dsum () to verify that the invoice total has be paid in full if not adds an invoice for late fee. You need to address your table structure. The longer it goes the more difficult it is going to be to address.

[orange]

I have reviewed the materials and summarized here.

There are 2 storage facilities involved - "Buffalo Storage" and "Custom Boat & RV Storage". Buffalo Storage has 122 units that Tenants rent. Tenants can rent more than one unit from either Facility. Tenants at each Facility billed separately. Rent for every tenant is ALWAYS due on the first of the month. If they have not paid by the 5th a 15 dollar late fee per unit is added ( Only 1 LateFee per Unit per Month is charged). For example, if a tenant pays on the fifth no late fee, if they pay on the 6th late fee is added.

Business rules/facts:
A Storage Facility is divided into many Units
Client/Tenant can have a RentalAgreement for 1 or more Units
A RentalAgreement is for a fixed period of time.
A monthly Invoice is sent to the Tenant (according to RentalAgreement/RA)
An Invoice is for monthly rental of 1 or more storage Units(according to RA)
A Invoice must be paid by 5^th of the Month.
Unpaid Invoices will be assigned a LateFee as of the 6^th of the month
A per Unit Latefee is assigned at the current fixed Amount(allows flexibility on $$)
1 or more Payments are made against an Invoice

(Attached is a preliminary model for consideration)

Questions:
How many units at "Custom Boat & RV Storage"?
How are the Units identified?
What happens if less than full Invoiced Amount is Paid?
What is the eviction process?

[breakingme10]

I have reviewed the materials and summarized here.

There are 2 storage facilities involved - "Buffalo Storage" and "Custom Boat & RV Storage". Buffalo Storage has 122 units that Tenants rent. Tenants can rent more than one unit from either Facility. Tenants at each Facility billed separately. Rent for every tenant is ALWAYS due on the first of the month. If they have not paid by the 5th a 15 dollar late fee per unit is added ( Only 1 LateFee per Unit per Month is charged). For example, if a tenant pays on the fifth no late fee, if they pay on the 6th late fee is added.

Business rules/facts:
A Storage Facility is divided into many Units
Client/Tenant can have a RentalAgreement for 1 or more Units
A RentalAgreement is for a fixed period of time.
A monthly Invoice is sent to the Tenant (according to RentalAgreement/RA)
An Invoice is for monthly rental of 1 or more storage Units(according to RA)
A Invoice must be paid by 5^th of the Month.
Unpaid Invoices will be assigned a LateFee as of the 6^th of the month
A per Unit Latefee is assigned at the current fixed Amount(allows flexibility on $$)
1 or more Payments are made against an Invoice

(Attached is a preliminary model for consideration)

Questions:
How many units at "Custom Boat & RV Storage"?
How are the Units identified?
What happens if less than full Invoiced Amount is Paid?
What is the eviction process?

Ok our rentals are month to month, no fixed time period. They can keep them for a month or I have tenants that have been using our units since 2004! The units are identified in tbl_Units under the column "Unit" the first 80 are just 1-80 (Small Units) then b1-b42 then in custom boat & rv we have 27, at buffalo storage we also have 1 outside rv parking that is a friend of the owner that we just charge 25 a month. so technically all together 150 units, 123 Buffalo 27 Custom.
We do allow customers who are behind to pay their bill off a little at a time. this is a very small town and the people here are usually very good, (we did find a meth lab in one unit last week tho!)
if a tenant gets too far behind and based on their history of late payments we send an eviction letter giving them 10 days to respond after 10 days locks are cut and their stuff is most likely chunked in the trash because its usually just junk.

some tenants sign a recurring payment agreement and leave us their credit card information to take payment out automatically each month.
i have to process these manually by hand keying in the card numbers and tracking declined accepted invalid card # expired card etc.

[orange]

Did you look at the model? Does it make any sense to you?

Does a Tenant sign anything?
Do you ever change rental fee for units?
Do you ever change LateFee amount?

Are units 1-80 the same rental fee/month? What about b1-b42?
And the units a facility2?

You should have a table of Monthly rates per unit.

[breakingme10]

I do, tbl_Units has a "Rent Code" that corresponds to the PK of tblRentCodes which has all the prices, yes some units get discounts, the model does make since, a tenant signs the rental agreement. attaching a copy of rental agreement in pdf and an account statement
late fee hasn't changed tho sometimes we do waive it under certain circumstances

[orange]

I now see 3 Facilities. You do have a formal signed contract. I'm surprised there is no time frame to the "lease agreement".

Do you see the difference in this model vs the one per your Access database?
What are your next steps?

[breakingme10]

the third facility is managed by the owner herself the agreement was made so that she could also use it, i just manage the buffalo storage and custom boat & rv.
i'm currently looking at the template rental manager that comes on access and it is very similar to your diagram (what program did you use to make that?) except it doesn't have invoices. so i am trying to adjust the tables on it to incorporate the invoicing.

[orange]

This was done with the Community Edition of ERWin.

You can do it with pencil and paper. You can mock it up in Access -but you have to have a table and keys. The tool to create the model is not critical. It's getting the model to support the business.

Your database could support all 3 businesses, right? Or a fourth/fifth if these evolved?

[breakingme10]

yes, though thankfully, my trucking database doesn't require invoicing and my wrecker database has yet to be started.

[orange]

If you have other databases to build, and they all involve a service/product and the related invoices and payments, you may want to step back and generalize a more encompassing model. Something like:

Client  --Contract/Agreement--  UnitOfService or Product ---HighLevelServices/ProductGroup
|   |
|  |
|   +-------Invoice
|                  |
|                  |
+---------- Payment

[breakingme10]

basically i could use the same model for both rental and wrecker, tenant=customer, unit=vehicle, agreement=towsheet, invoice=invoice

[orange]

Yep, do it once. Keep stepping back until you find something that won't work. Work og the general model and customize when and if necessary.

Good luck with your project.