Duplicat data on year

[Kallu]

I have tblRegister
Column1: BillNr (123456)
Column2: Date (17.11.11)
How to restrict enterin same bill number on current year?
Column1: BillNr (123456)
Column2: Date (01.01.11-31.12.11) Restrict

Column1: BillNr (123456)
Column2: Date (01.01.12-......) Allow
I us this vba code for duplicat value

Private Sub BillNr_BeforeUpdate(Cancel As Integer)
'*********************************
'Code sample courtesy of srfreeman
'*********************************

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

SID = Me.BillNr.Value
stLinkCriteria = "[BillNr]=" & "'" & SID & "'"

'Check tblRegistertable for duplicate BillNr
If DCount("BillNr", "tblRegister", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "BillNr " _
& SID & " its registrent on database." _
& vbCr & vbCr & "Klik to se registred bill.", _
vbInformation, "Warning"
'Go to record of original BillNr
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If
End Sub

Thanks.

 

[nanscombe]

Hi Kallu,

You could create a unique index on the table but this will not work if you already have duplicate data in the table.

1. Open tblRegister in design view.
2. Click on the Indexes button.
3. Go to the first empty row and give the index a name in the Index Name column.
4. Choose BillNr in Field Name column.
5. Make the Unique option, in the bottom left pane, Yes.
6. Go to the next empty row
7. Leave the Index Name empty.
8. Choose your date field in the Field Name column.
(Is it really called Date? That could give you problems as it's a reserved word.)

You should end up with something that looks like:

(... used to represent data in different fields)

Index Name...Field Name...Sort Order
NoDuplicates...BillNr...Ascending
<Blank cell>...Date...Ascending

With Unique ... Yes in the bottom left hand pane.

9. Close the Indexes popup.
10. Save the changes.

If you get an error message about duplicate data you will have to close the table without saving. Fix any duplicates. Then try again.

Once that index is on the table the user will get a generic popup message telling them that they can't enter duplicated data.

I hope this helps.

 

[nanscombe]

Sorry, that would only give uniqueness on one day.

I'll have another think about it.

 

[nanscombe]

The attached database has a form which should show the code you need to achieve what you want.

There is also a bonus bit of code, in ModBonus, that you could use to automatically generate a sequential Bill Number if you wanted to.

The way it is shown it will reset the value to 1 each New Year.


I hope this is useful.

 

[Kallu]

Hi nanscombe thank you very much, that was excatly what Im looking 4, thanks thanks.

Good luck nanscombe.