Avoid duplicate record

[khicon73]

Hello all
I have a form and a subform, link by the CollectedID

for example: on the form
I have CollectedID: 2008-VKGS-65656

subform:
CollectedID ConfirmedID
2008-VKGS-65656 T00005
2008-VKGS-65656 T00006
2008-VKGS-65656 T00007

all data stored in a table named tblCollectedData. First four number of CollectedID is the year.

Each year the confirmedID started with T00001...to T99999. You can not have the confirmedID duplicated during that year. For instance, year 2007 ....ConfirmedID; T00005, T00006 and year 2008 ....ConfirmedID:T00005, T00006, etc...it is OK. But year 2008 cannot have T00005 twice.
So please help with the code, thanks alots.

 

[Remou]

It would be best to do this with a unique index on the table. Is it possible to include year as a field? That is, split CollectedID?

 

[gwinn7]

Agreed, split the id. Avoid concatenated values.

You can have multi-member primary keys.

In fact, if you can get away with it, remove the 'T' from the confirmed ID when you store that value. Plus, store it as an integer or a long.

Remember, using SQL, or design view, you can make the data appear any way you like in a report or on a form.

Gary
gwinn7

 

[TheAceMan1]

How are ya khicon73 . . .

I'm looking for more info on [blue]ID's here[/blue].

For the same year . . . can the identifier of CollectionID [blue]VKGS[/blue] be different (different collection)?

. . . and can the [blue]length of this identifier[/blue] in CollectionID vary?

In the [blue]ConfirmedID[/blue] can [blue]T[/blue] also vary in length? . . . and this length comprise other characters?

I have a feeling were not getting the whole picture here.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[khicon73]

First of all, thanks alots for all your response. I'm very appreciated.
Well, the ConfirmedID can not be removed the T infont of it. It's text field and always have T before untill the end of the year.
TheAceMan1, CollectedID have four different collections such as VKGS, TGVK, ABDS, and STVT. It always like this:
YYYY-VGKS-1 or YYYY-ABDS-1225 or YYYY-STVT-99.
Once, thank you very much for your help.

 

[TheAceMan1]

khicon73 . . .

So collections of the same year . . .

2007-ABDS-01225
2007-STVT-01225
2007-VKGS-01225

. . . can have the same ConfirmedID?

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[khicon73]

TheAceMan1, No we can't have the same collection like that. After the letter, the number is always different because this number is an increment number from other table and start at 1 on the new year
It should be like this:
2007-ABDS-1
2007-STVT-2
2007-VKGS-3
.....
2007-ABDS-1225
2007-STVT-1226
2007-VKGS-1227
....
2008-STVT-1
2008-ABDS-2
2008-STVT-3
2008-VKGS-4

 

[TheAceMan1]

khicon73 . . .

Sorry to get back so!

Before I begin, let it be known that I agree with [blue]Remou[/blue] & [blue]gwinn7[/blue] on spliting the ID.

However if your stuck or have to continue with what you have, the following function will return the next [blue]ConfirmedID[/blue] (properly formatted) for the year specified.

Its up to you to specify the year of interest in the functions arguement (as numeric):

[blue]NextConfirmedID([purple][b]YearHere[/b][/purple])
   or
NextConfirmedID([purple][b]2007[/b][/purple])[/blue]

If no year is supplied, the current year is used!.

If [blue]tblCollectedData[/blue] was comprised of the following:

[tt][blue]CollectedID ConfirmedID
*********** ***********
2006-ABDS-1 T00010
2006-STVT-2 T00030
2006-VKGS-3 T00020

2007-ABDS-1225 T02550
2007-STVT-1226 T02560
2007-VKGS-1227 T02540

2008-STVT-1 T10010
2008-ABDS-2 T10009
2008-STVT-3 T10023
2008-VKGS-4 T10011[/blue][/tt]

[tt]NextConfirmedID(2005) returns [blue]T00001[/blue]
NextConfirmedID(2006) returns [blue]T00031[/blue]
NextConfirmedID(2007) returns [blue]T02561[/blue]
NextConfirmedID(2008) returns [blue]T10024[/blue]
NextConfirmedID() returns [blue]T10024[/blue][/tt] (current year is 2008)

and the function (put it in a module in the modules window of global access):

[blue]Public Function NextConfirmedID(Optional usrYear As Integer) As String
   Dim Cri As String, fldEq As String, dmaxVal
   
   If usrYear = 0 Then
      Cri = "left([CollectedID],4) = '" & CStr(Year(Date)) & "'"
   Else
      Cri = "left([CollectedID],4) = '" & CStr(usrYear) & "'"
   End If

   fldEq = "Val(Right([ConfirmedID],5))"
   dmaxVal = DMax(fldEq, "tblCollectedData", Cri)
   
   If IsNull(dmaxVal) Then
      NextConfirmedID = "T00001"
   Else
      NextConfirmedID = "T" & Format(CStr(dmaxVal + 1), "00000")
   End If

End Function[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[khicon73]

Thanks TheAceMan1 for your post. But I think I mis-explained the ConfirmedID. I don't need to get the return ConfirmedID. The users are gona to enter it in. However, when they enter the same ConfirmedID during that year, then I need to pop-up a message saying "The ConfirmedID is already existed. Please enter another one!"

sample:
Main form I have
CollectedID: 2008-VKGS-65656 , Colected Date: 01/15/2008, link with the CollectedID on the subform.

subform:
CollectedID ConfirmedID
2008-VKGS-65656 T00005
2008-VKGS-65656 T00006
2008-VKGS-65656 T00007
2008-VKGS-65656 T00007 ----> Pop up a message here.

 

[TheAceMan1]

khicon73 . . .

Sorry to get back so late . . . AceMan1 has to work too!

[blue]However, [purple]when they enter the same ConfirmedID during that year[/purple], then I need to pop-up a message saying "The ConfirmedID is already existed. Please enter another one!"[/blue]

[blue]Understood![/blue]

Try the following in the [blue]Before Update[/blue] event of [blue]ConfirmedID[/blue]:

[blue]   Dim Cri As String, fldEq As String
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   Cri = "Left([CollectedID],4) = '" & Left(Me.Parent.CollectedID, 4) & "' AND " & _
         "Val(Left([ConfirmedID],5)) = " & Val(Left(Me!ConfirmedID, 5))
   
   If Not IsNull(DLookup("[ConfirmedID]", "tblCollectedData", Cri)) Then
      Msg = "ConfirmedID Already Exist!" & DL & _
            "You'll have to enter a different ConfirmedID!" & DL & _
            "You won't be able to continue until ConfirmedID is corrected!"
      Style = vbCritical + vbOKOnly
      Title = "Duplicate ConfirmedID Detected! . . ."
      MsgBox Msg, Style, Title
      Cancel = True
   End If[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[khicon73]

Thank you very much AceMan1. You desert a star
I just changed a little on the code, and it worked like charm...

Once, thanks for million.

Private Sub ConfirmedID_BeforeUpdate(Cancel As Integer)
Dim Cri As String, fldEq As String
Dim Msg As String, Style As Integer, Title As String, DL As String

Cri = "Left([CollectedID],4) = '" & Left(Me.Parent.CollectedID, 4) & "' AND " & _
"Val([COLOR=red yellow] right [/color]([ConfirmedID],5)) = " & Val([COLOR=red yellow]right [/color](Me!ConfirmedID, 5))

If Not IsNull(DLookup("[ConfirmedID]", "tblCollectedData", Cri)) Then
Msg = "ConfirmedID Already Exist!" & DL & _
"You'll have to enter a different ConfirmedID!" & DL & _
"You won't be able to continue until ConfirmedID is corrected!"
Style = vbCritical + vbOKOnly
Title = "Duplicate ConfirmedID Detected! . . ."
MsgBox Msg, Style, Title
[COLOR=red yellow] me.undo [/color]
End If