Solved UniqueIndex (1 Viewer)

[ZEEq]

hello Everyone!
My question is regarding unique index in table i have a studentFeeInvoiceT table with fields
FeeInvoiceID
StudentClassID
FeeSettingID
InvoiceDueDate
is it possible to create a unique index based on these 4 columns ?
i tried calculated field for invoiceduedate month but i think you can't make unique index on calculated fields what other ways can i achieve this
Regards

 

[arnelgp]

Unique Index on StudentClassID + FeeSettingID is enough.

meaning each student and (the fee) is unique.

to make InvoiceDueDate automatically filled, you can also use a Form and an Event on the form that assign the value for it.
this way (making invoiceDueDate as a Date type) you can create index to it.

 

[ZEEq]

Unique Index on StudentClassID + FeeSettingID is enough.

meaning each student and (the fee) is unique.

yes @arnelgp unique row and also student cannot be charged twice in a month

 

[arnelgp]

charged twice in a month

what do you mean? fees are collectable bi-monthly?
like i said, use a Form for your data entry and add Validation to check how many fees already in the table (not more than 2 in a month).

 

[ZEEq]

i use append query to insert fees to invoice table just want to make it tight so that any student cannot be charged twice in a single month we collect fees every month i tried it with a function and datamacro but i am not sure that i am writing function correctly

 

[arnelgp]

here is a demo of a Form.
open forn StudentFeeInvoiceF and enter a student and a fee.
again, enter same student and same fee.

 

[ZEEq]

thank you so much @arnelgp for this much appreciated i did some thing similar please have a look at my function and tell me if i was doing right

Function checkDuplicateInvoiceFee() As Boolean

    On Error GoTo ErrorHandler

    Dim InvoiceFeeID As Long
    InvoiceFeeID = [InvoiceFeeID]
    Dim StudentClassID As Long
    StudentClassID = [StudentClassID]
    Dim StudentID As Long
    StudentID = [StudentID]
    Dim StudentFeeSettingID As Long
    StudentFeeSettingID = [StudentFeeSettingID]
    Dim InvoiceDueDate As Date
    InvoiceDueDate = [InvoiceDueDate]
          
    Dim DupCount As Long
    
    DupCount = DCount("*", "StudentFeeInvoiceJT", "StudentClassID=" & StudentClassID & _
              " AND StudentID=" & StudentID & _
              " AND StudentFeeSettingID=" & StudentFeeSettingID & _
              " AND Month([InvoiceDueDate])=" & month(InvoiceDueDate) & _
              " AND Year([InvoiceDueDate])=" & Year(InvoiceDueDate) & _
              " AND FeeInvoiceID <> " & InvoiceFeeID)

    If DupCount > 0 Then
        checkDuplicateInvoiceFee = True
    Else
        checkDuplicateInvoiceFee = False
    End If

ExitHandler:
    On Error Resume Next
    Exit Function

ErrorHandler:
            MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                "Error Number: " & Err.Number & vbCrLf & _
                "Error Source: checkDuplicateInvoiceFee()" & vbCrLf & _
                "Error Description: " & Err.Description & _
                Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                , vbOKOnly + vbCritical, "Inform Administrator!"
            
            Resume ExitHandler
  
End Function

 

[ZEEq]

and then i used aobe code in datamacro before change event but its not working

 

[arnelgp]

you put your function in a Module (not in the form).
then modify it to this:

Function checkDuplicateInvoiceFee( _
            ByVal StudentClassID As Long, _
            ByVal StudentID As Long, _
            ByVal StudentFeeSettingID As Long, _
            ByVal InvoiceDueDate As Date) As Boolean

    On Error GoTo ErrorHandler
          
    Dim DupCount As Long
    
    DupCount = DCount("*", "StudentFeeInvoiceJT", "StudentClassID=" & StudentClassID & _
              " AND StudentID=" & StudentID & _
              " AND StudentFeeSettingID=" & StudentFeeSettingID & _
              " AND Month([InvoiceDueDate])=" & Month(InvoiceDueDate) & _
              " AND Year([InvoiceDueDate])=" & Year(InvoiceDueDate))

    checkDuplicateInvoiceFee = (DupCount <> 0)

ExitHandler:
    On Error Resume Next
    Exit Function

ErrorHandler:
            MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
                "Error Number: " & Err.Number & vbCrLf & _
                "Error Source: checkDuplicateInvoiceFee()" & vbCrLf & _
                "Error Description: " & Err.Description & _
                Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                , vbOKOnly + vbCritical, "Inform Administrator!"
            
            Resume ExitHandler
 
End Function

now your macro change to this:

 

[ZEEq]

worked perfectly thank you so much @arnelgp just one question how to trigger this function if someone try to update the record or change the record ?

 

[arnelgp]

the same thing, use the Change Event Macro of the table but don't test if you are in New Record [IsInsert].

 

[ebs17]

Annotation:
If you agree for yourself that InvoiceDueDate is used with always the first of the month, you can set a unique index over then three fields (StudentClassID + FeeSettingID + InvoiceDueDate) and save yourself a lot of checking.

 

[Pat Hartman]

Here is a picture of what you need.
1. Remove the indexes on StudentClassID and FeeSettingID. They are NOT needed when you define relationships. Access automagically builds HIDDEN tables to support the FK in a relationship and you absolutely do not want multiple indexes on the same column
2. Add the relationships and check the enforce RI box
3. Open the junction table in design view and then open the indexes dialog. You should only see the PK. Then add the new, unique index. The first line has a unique name. then pick the three fields you want. Do NOT include the FeeInvoiceID since that defeats the purpose of the unique index on the other three fields. You never include the autonumber when making an alternate unique index on a table.

If it makes you feel good to have code to validate the uniqueness, then that is fine. You will be able to give the user a more user-friendly message but you should STILL create the correct relationships and use the database RI to enforce your business rules whenever possible. Get in the habit of creating relationships and RI as part of the process of creating the tables. Do NOT leave it until later. Proper RI is an important part of every stable application.

PS - as @ebs17 mentioned, if you don't always bill a particular student on the same day of the month each month, this will not work. You will need to create a separate field with just the year and month - yyyymm and put the unique index on that. You still need the InvoiceDueDate because you need the actual date but the InvoiceYM field will only ever be used for RI.