Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Stop a duplicate key (97)

    Here is the code:

    Private Sub cmdMakeIncident_Click()
    DoCmd.OpenForm "frmIncident"
    DoCmd.GoToRecord , , acNewRec
    Forms!frmIncident!AssignmentID = Me.AssignmentID
    End Sub

    This works fine by pumping across the AssignmentID to my frmIncident. (The corresponding tables tblIncident and tblAssignment are not bound as other forms are feeding into it and they conflict if I bind them, hence pushing the AssignmentID to it using VBA). I have set the AssignmentID in the tblAssignment to no duplicates. If a user accidentally clicks the command button a second time obviously a new record gets that assignmentID( a foreign key) and then I get an error due to the duplication.

    How can I get the VBA to check that this AssignmentID has been used already from tblIncident and if there is a duplicate stop and have a message box say something like "This assignment has already been reported"?
    Jerry

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stop a duplicate key (97)

    You could look at post
    <!post=D Willett,277131>D Willett<!/post>

    which I recently had the same kind of issue.
    See the code below, it may be how you need it.


    Dim intAssigCount As Integer
    Dim strAssigCheck As String
    strAssigCheck = "AssignmentID=" & Me.AssignmentID
    intAssigCount = DCount("*", "tblAssignment", strAssigCheck)
    If intAssigCount > 0 Then
    MsgBox "Assignment Duplicated", vbCritical
    'Do whatever Here.............................................. ...
    Exit Sub
    End If

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Stop a duplicate key (97)

    Thanks Dave,

    Yep, moved the code around mine and it works like a dream...

    I've got to say that is a very clever way of doing <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Jerry

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Stop a duplicate key (97)

    Hi Jezza

    Glad it worked for you.
    By the way, <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> I had some help <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15> .

    Dave

Posting Permissions

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