Results 1 to 5 of 5

Thread: DCount Function

  1. #1
    Join Date
    May 2008
    Posts
    48

    Unanswered: DCount Function

    I am little confused with the behavious of DCount Function.

    To prevent duplicate records, where Text Field is a Primary Key, I have used following code and is the same is perfectly working in Before Update event & does not move cursor to the next field when duplicate entrt is punched & as a warning, message flashes.

    Private Sub Txtstation_BeforeUpdate(Cancel As Integer)
    Dim Criteria As String
    Dim SID As String
    SID = Me.Txtstation.Value
    Criteria = "[Station]=" & "'" & SID & "'"
    If (DCount("Station", "Customer", Criteria) > 0) Then
    MsgBox ("The Station " & SID & " has already been used"), vbOKOnly
    Cancel = True
    End If
    End Sub

    But, when I am trying to execute such code in Before Update event to prevent duplicate date in DateofEntry Field which is also a Primery key, same is not working. When the same has not worked in Before Update event, I have also tried to execute the same in lost focus, On Enter events but duplicate entry is being punched & no message is being flashed.

    Following code is being used in DateofEntry Field

    Private Sub DateofEntry_BeforeUpdate(Cancel As Integer)
    Dim Criteria As String
    Dim SID As Date
    SID = Me.Txtstation.Value
    Criteria = "[Station]=" & "#" & SID & "#"
    If (DCount("DateofEntry", "FO", Criteria) > 0) Then
    MsgBox ("This date has already been used"), vbOKOnly
    Cancel = True
    End If
    End Sub

    Can anyone tell me where the error exist?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    Criteria = "[Station]=" & "#" & SID & "#"
    What is the data type of Station? Looks like you have changed the code here compared to the working code.

  3. #3
    Join Date
    May 2008
    Posts
    48
    There was some mistake in coding, which should be read / corrected as under:

    Private Sub DateofEntry_BeforeUpdate(Cancel As Integer)
    Dim Criteria As String
    Dim SID As Date
    SID = Me.Txtstation.Value
    Criteria = "[DateofEntry]=" & "#" & SID & "#"
    If (DCount("DateofEntry", "FO", Criteria) > 0) Then
    MsgBox ("This date has already been used"), vbOKOnly
    Cancel = True
    End If
    End Sub

    This code (with some modification) is perfectly works in text control, but not in date control. Now, please let me know the use of DCount with Date format.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It is still wrong.
    txtStation.Value is used as text in your first code and a date in your second. Please correct your code again and repost if it still still fails to work. If you are correcting it on the website please just copy and paste directly from your code module - we don't want to waste time debugging typos in this thread that don't exist in your database.

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Here is the problem.

    You are using variable SID to pick up the key value that must not be duplicated. When you fire this code from Txtstation, you are populating SID with the value in Txtstation. However, when you fire this code from DateOfEntry, you are STILL populating SID from the value in Txtstation - therefore, you are trying to find a text ID in a date field.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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