Prevent duplicates in same month in access

[Usmantahir]

Hello All,

I have a access database to give pension for pensioners in transaction form i have 8000 accounts in my data table and i am using drop down list combo box to make transactions.

I want single entry in each month of every account if any user try to do duplicate entry ms access prevent to duplicate entry in same month.

I have these field in my transaction form
Date,account no,name of pensioner,amount of pension

 

[theDBguy]

Hi. Welcome to AWF!

One approach you could take is to create a multi-field unique index. I think that may be possible with using a calculated field to determine the month from your date field. Otherwise, another approach is to use the Form's BeforeUpdate event to validate the data entry to prevent any duplicates.

 

[Pat Hartman]

To make the unique index, you're going to have to separate out yyyy and mm from the date unless your payments are always all on the same date.

 

[theDBguy]

I think that may be possible with using a calculated field to determine the month from your date field.

I just did a quick test. Sadly, calculated fields can't be indexed.

 

[Usmantahir]

Hi. Welcome to AWF!

One approach you could take is to create a multi-field unique index. I think that may be possible with using a calculated field to determine the month from your date field. Otherwise, another approach is to use the Form's BeforeUpdate event to validate the data entry to prevent any duplicates.

@theDBguy ,

Can you tell me please before update event code for validate data.

 

[Pat Hartman]

I just did a quick test. Sadly, calculated fields can't be indexed.

They are calculated on the fly. That is the point. If they don't permanently exist, they cannot be indexed.

 

[Pat Hartman]

Dim yyyymm as string
Dim strSQL as string

yyyymm = Format(Me.YourDate, "yyyymm")
strSQL = Format(YourDate, "yyyymm") & " = '" & yyyymm & "'"
strSQL = strSQL & " AND YourID = " & Me.YourID

If dCount("*", "yourtable", strSQL ) > 0 Then
    msgbox " This payment already exists for " & yyyymm  & "for " & Me.YourID, vbOKOnly
    Cancel = True
    Me.YourDate.SetFocus
    Exit Sub
End If

Make sure to check the strings to ensure they are valid. Getting the quotes in the right place isn't all that easy when writing pseudo code.

 

[Usmantahir]

Dim yyyymm as string
Dim strSQL as string

yyyymm = Format(Me.YourDate, "yyyymm")
strSQL = Format(YourDate, "yyyymm") & " = '" & yyyymm & "'"
strSQL = strSQL & " AND YourID = " & Me.YourID

If dCount("*", "yourtable", strSQL ) > 0 Then
    msgbox " This payment already exists for " & yyyymm  & "for " & Me.YourID, vbOKOnly
    Cancel = True
    Me.YourDate.SetFocus
    Exit Sub
End If

Make sure to check the strings to ensure they are valid. Getting the quotes in the right place isn't all that easy when writing pseudo code.

@Pat Hartman

Thanks a lot sir
I tried to this code and i hope it work.

 

[arnelgp]

see table Transactions in Design view.
see the Before Change macro I made to prevent duplicate.
i only check the Account No and Month/Year of the transactions.

now Open Transaction table and input some data to test.

 

[Usmantahir]

see table Transactions in Design view.
see the Before Change macro I made to prevent duplicate.
i only check the Account No and Month/Year of the transactions.

now Open Transaction table and input some data to test.

@arnelgp

Its working sir can you explain how I apply it to my DB

 

[Usmantahir]

see table Transactions in Design view.
see the Before Change macro I made to prevent duplicate.
i only check the Account No and Month/Year of the transactions.

now Open Transaction table and input some data to test.

Sir i have linked tables for transactions form how i apply this macro

 

[arnelgp]

you Edit the table in the BE and put the macro there.

 

[MsAccessNL]

@Pat Hartman

Thanks a lot sir
I tried to this code and i hope it work.

And what if the payment had been made on another date in the same month? I think you have to look for entries in the same month and year instead of the same day..

 

[Usmantahir]

you Edit the table in the BE and put the macro there.

Its working sir

Bundle of thanks