Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    11

    Unanswered: Duplicat value on year

    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.
    Last edited by Kallukallu; 11-17-11 at 17:16.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider defining a unique index on billnr & year(date)
    incidentally date is a reserved word that could cause you problems at a later date

    but I don't actually see what your question or problem is
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    11

    Duplicat value on year

    I am new in access database, I dont no how to definine a unique index on billnr & year(date), cen u explain more.
    Actually my problem is, I want to avoid entering same bill two times on database,(sametimes users make a mistake entering bills on database,they change jusut one nr. on bill 12345 to 12354).

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    open the table in design mode
    look for the indexes button
    define an index
    give the index a name
    define the columns that comprise the index
    set the index properties to unique
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    11

    Duplicat value on year

    Not working what Im looking 4.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you have presented some code
    there is no indicatrion of what is or isn't working as expected

    I suspect no one here as a scooby as to what you are tryi9ng to achieve

    not what Im looking 4
    isn't that helpful either

    whatever else we are on this forum (that is private individuals offering our time and expereince (or lack of)) we are not psychic.

    Se ya matey, I hope you find a solution to your problem, but it wont be from me
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2011
    Posts
    11

    Thumbs down Duplicat value on year

    Hi there, healdem I finde solutions on another formu, excatly what Im looking 4, thanks on replay.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •