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

[breakingme10]

I have a form that when it loads if it sees that the status of the invoice is "overdue" it adds a line to invoice details using vb code to do so. i've tried asking this question in Programming section but i've been ignored for quite some time.


my problem is that whenever i open the form it sees "overdue" and adds late fee line to details everytime i open the form so i am getting multiple late fees added to the invoice.

my code is

If Me.txtStatus = "Overdue" Then
With Me.sfrm_InvoiceDetails.Form.RecordsetClone
.AddNew
!Invoice = Me.InvoiceID
!Item = 32
.Update
End If
End With
End If

how can i get it to check and see if the late fee line is already there and if so not to add it again?
Please help me!

[orange]

I think this code should be

If the status is Overdue, then
add the late fee line to details,
but if the invoice is Paid, you have to update the Status to Paid or something different than Overdue.

What is your business rule regarding LateFees? Do you record the date that the LateFee was added? If so, you can check the LateFee date to the current date. Does the client get another 10 days to Pay etc? When the Invoice is Paid, how/where/when do you change the Status from Overdue?

Somewhere in your logic, you have to reset the status....

Good luck.

[breakingme10]

The status is set by the qry_InvoiceData

Status: IIf(IsNull([Payment]),IIf(Date()>[DateDue],"Overdue","Unpaid"),"Paid")

So once I enter in a payment it will say "Paid" not overdue, also if it is not past the due date it will just say "Unpaid"

[breakingme10]

I really need it to not add the late fee every time i open the form to check the status or when people call to ask their balance. It needs to only be added once, but I don't want to have to add a button to the form to add a late fee.

[orange]

But if , every time you open the form ,you add a new latefee, you have a logic problem. You need a LateFeeAddedDate or something to check the Status as of DateX - regardless of how many times you open the form.

What are the business rules around LateFees?

eg. If Payment is due on DateY, and you open the record on DateY +1, you would change the status to OverDue. I would suggest a field to indicate when the Status last change (changed to the current Value). Then when I open the form, I would check today's Date against the StatusLastChangedDate, and based on your business rules, either Add the LateFee, or bypass adding the LateFee.

But it comes down to the rules your business has regarding Payments, Overdue etc.

[breakingme10]

Its a self storage unit facility (well two separate facilities that i manage) meaning i'm making basically two different forms reports etc on for one facility and one for the other, they are owned by the same person but have different locations and names. rent is due on the first, after the 5th a $15 late fee is charged. so i have invoicedate as 6/1/14 duedate: dateadd ("d",4,[InvoiceDate]), and the status code above. i'll empty the db and post it on here, its in the beginning stages and very very simple right now. i'm just trying to get all my forms tables and relationships correctly set up before i improve on design.



Even once my db is zipped it won't upload it, it says error so I uploaded it to my googledrive.
added db that has no personal info
frm_invoicedetails is the form im working on it has subform sfrm_invoicedetails
it also won't allow me to enter new payment

https://drive.google.com/file/d/0B9J...it?usp=sharing

[breakingme10]

in the northwind db vb code they have something for if a productorder exists, i would need help editing this to work for me tho.

Function Exists(PurchaseOrderID As Long) As Boolean
    Exists = Not IsNull(DLookupWrapper("[Purchase Order ID]", "Purchase Orders", "[Purchase Order ID]=" & PurchaseOrderID))
End Function

and i see it being used here

Function PurchaseOrderIsValid() As Boolean
    Dim PurchaseOrderID As Long
    PurchaseOrderID = Nz(Me![Purchase Order ID], 0)
    
    Select Case Me![Status ID]
    Case New_PurchaseOrder
        If Not PurchaseContainsLineItems() Then
            MsgBoxOKOnly PurchaseHasNoLineItems
            If MsgBoxYesNo(CancelOrderPrompt) Then
                If PurchaseOrders.Exists(PurchaseOrderID) Then
                    If Not PurchaseOrders.Delete(PurchaseOrderID) Then
                        Exit Function
                    End If
                End If
            End If
        End If
    Case Submitted_PurchaseOrder
    Case Approved_PurchaseOrder
    Case Closed_PurchaseOrder
    End Select
    
    PurchaseOrderIsValid = True
End Function

the first is in a module, the second is in the purchase order details forms code

[orange]

I saw your PM and responded. There are questions in the response.
I also looked briefly at the code (Current Event of the frm_InvoiceDetails)

This solution is really for demonstration only. You will need to incorporate your business rules.
Here is some logic to stop the additional LateFee being added, but how you should do this depends on the rules within your business.

Private Sub Form_Current()

    Me.txtStreet = Me.cboTenant.Column(2)
    Me.txtZIP_ID = Me.cboTenant.Column(3)
    Me.txtPhone = Me.cboTenant.Column(4)
    Me.txtEmailAddress = Me.cboTenant.Column(5)

    If Me.txtLatePaymentQ = "No" Then
        Me.txtDateDiff.Visible = False
    Else
        Me.txtDateDiff.Visible = True
    End If

    If Me.txtStatus = "Overdue" Then
        With Me.sfrm_InvoiceDetails.Form.RecordsetClone
      
       '********************************************************* 
        'If there is already a late fee on this Invoice then don't add another
       '*********************************************************

        If DCount("Item", "tbl_InvoiceDetails", "Item = 32") > 0 Then
        MsgBox "LateFee already exists ...don't add another", vbOKOnly
        GoTo orangeByPass
        Else
            .AddNew
            !Invoice = Me.InvoiceID
            !Item = 32
            .Update
orangeByPass:       ' By pass the addnew and update  orange
        End If
        End With
    End If
    'Me.txtTotal = DSum("ItemTotal", "[frm_InvoiceDetails]![sfrm_InvoiceDetails]", "Invoice=[InvoiceID]")
End Sub

What exactly does Item 32 mean? I see in your Categories you have 3 as LateFee, but why 32?

Do you ever add a second LateFee to an Invoice? If so, under what condition(s)? That is the key to your logic issue.

Your code would add the LateFee every time the record involved was the current record.


I am attaching the revised code in your original zip. I did a compact and repair on your accdb and got rid of a lot of "wasted space". When you add, edit, delete, change objects in your database, Access marks that space as used(unavailable). You should do regular Compact and Repair utility runs to recover that unavailable space.

Also, and more important, you should do regular backups. So many posters will tell you, "if only I had backed it up!!"

Good luck.

[breakingme10]

No second late fees are added, just the one.

[breakingme10]

the late fee is ItemID 32 in the tblRentCodes (which is a dumb name for my price list table but it is what it is). I do do regular compact repair and backups on the real database, the empty space on that one was from the Delete All Data removing alllll the information already in the database.

[orange]

When I talk about business rules, I asking what, in plain English, should you do with a LateFee? But in the more general sense, you really need a complete statement of your business ( a paragraph or so that identifies the things and how the things fit together). And most important, in my view, you need to design the tables and relationships to support the business, and test the model with test data, before doing anything with Forms/Queries or Reports.

Things like you said:

A Customer may rent 1 or Many StorageUnits.

Did you see the sample code I gave in post #8?

Since I don't know you business, I just checked to see if the InvoiceStatus was OverDue, then if there is no LateFee, then Add a LateFee to the InvoiceDetails. However, if there is a LateFee (via the Dcount()>0), then don't add another LateFee.

But whether you have these identified explicitly or not, you/your company will have a rule that they want to apply when an INvoice is Overdue.

Here is an unrelated situation, in the form of a tutorial, that describes a business setup and leads you through designing tables and relationships including Normalization.

Here is similar info in point form from a different site.

These links may be helpful to you.

Note: I'm willing to help but do need to know the business in more detail.

I have included a jpg of you tables and relationships as you have them now. I removed the Navigation items. I have expanded the boxes to show all fields.
I don't see anything that says a tenant rents a Unit.
Tenant should only have Tenant info. It should not have a UnitId.
Is the Tenant the Client always? It appears that a Tenant only makes 1 Payment?

[breakingme10]

The code is saying that there is a late fee added already when there is not.

[orange]

When I opened your form, it added a new LateFee as a line item on INvoiceDetails.

I deleted one of the LateFees, adjusted the code and reopened the form. It showed the message saying the status was overdue, and a latefee already existed.

[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.

[breakingme10]

When I opened your form, it added a new LateFee as a line item on INvoiceDetails.

I deleted one of the LateFees, adjusted the code and reopened the form. It showed the message saying the status was overdue, and a latefee already existed.

because that sample only had one invoice listed in it, my real data has many