Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Check if data item already exists (2003)

    Hi. I would like to do following: (pseudocode)

    On form data input
    Search field(s) in underlying table(s)
    If value is already present then
    msgbox: "Warning. etc. etc.
    allow entry
    End If

    I won't bore you with a full description of the various methods I have tried to achieve this - a combination of self-join queries and macros -'cos (a) it would probably only confuse the issue and ([img]/forums/images/smilies/cool.gif[/img] I couldn't get anything to work 100% anyway.

    Also... my MSAccess knowledge. I'm studying ECDL - impressed huh! So I need the 'Janet and John' version. If you assume I'm totally thick you won't be too far off the mark.

    Any and all help most gratefully appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Check if data item already exists (2003)

    You haven't provided enough information. Are you searching for the entire record, a particular field, a combination, or what? There are a variety of ways to check for an existing value in the table, but the details differ with the situation. Self-join queries and macros seem like the long way around when a small bit of code will do, but without specifics, that is strictly a guess.
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if data item already exists (2003)

    If you had wanted to disallow entering duplicates, it would have sufficed to create a unique index on the combination of fields that you want to prevent from being duplicated. But since you indcate that you want to allow the duplicate after a warning, you will need to write some VBA code.

    Before doing so, you will have to decide which combination of fields you want to test for being duplicate. The following code (based on <post#=189780>post 189780</post#> by <!profile=patt>patt<!/profile>)uses two fields as an example: a text field Model and a numeric field Elevation. The text boxes on the form bound to these fields are named txtModel and txtElevation. The name of the underlying table is TableName.

    Open the form in design view.
    Activate the Properties window (View | Properties)
    Activate the Event tab.
    Click in the Before Update event.
    Select [Event Procedure] from the dropdown list.
    Click the ... to the right of the dropdown arrow.
    You will be taken to the Visual Basic Editor, with the first and last line of the event procedure already created for you. You can enter code in between.
    Here is an example of what the finished code could look like. Of course, you will have to adapt it to your situation.
    <code>
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String

    ' Only continue if new record
    If Me.NewRecord = False Then Exit Sub

    Set dbs = CurrentDb
    strSQL = "SELECT * FROM TableName" & _
    " WHERE Model = '" & Me!txtModel & _
    "' AND Elevation = " & Me!txtElevation
    ' Open recordset to test for duplicates
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    ' Test if recordset is not empty
    If Not rst.EOF Then
    ' Ask user whether (s)he wants to continue
    Cancel = _
    (MsgBox("A record already exists for that Model/Elevation." & _
    vbCrLf & "Do you want to continue anyway?", _
    vbQuestion + vbYesNo + vbDefaultButton2) = vbNo)
    End If

    ' Clean up
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Sub
    </code>
    Use Alt+F11 or the Windows task bar to return to Access.

    Note: there are other solutions, using a separate unbound form to enter new records, but you would need to write a lot of VBA code to handle that.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if data item already exists (2003)

    The code supplied by Hans is DAO code. You have to set a reference to DAO.
    In the Visual Basic Editor, select Tools, References.
    In the reference window, scroll down until you find Microsoft DAO 3.6 Object Library.
    Put a check in front of it and click OK.
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if data item already exists (2003)

    You are entirely correct, but there is a reference to DAO by default in Access 2003 (there wasn't in Access 2000 and 2002). See References that you must set when you use Microsoft Office Access 2003.

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if data item already exists (2003)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> I don't work a lot with 2003, sorry.
    Francois

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if data item already exists (2003)

    Your observation is useful, in particular for Access 2000/2002 users reading this thread. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  8. #8
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if data item already exists (2003)

    Hi Hans

    I copied and pasted your code as per instructions and amended it to suit my table/text box names:

    <extract>
    Set dbs = CurrentDb
    strSQL = "SELECT * FROM tbl_PlantTreatment" & _
    " WHERE TreatmentRef = '" & Me!TreatmentRef & _
    "' AND BatchRef = " & Me!BatchRef
    ' Open recordset to test for duplicates
    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
    ' Test if recordset is not empty
    If Not rst.EOF Then
    ' Ask user whether (s)he wants to continue
    <end extract>

    When the code actions I am getting a run-time error 3464 "Data Type mismatch in criteria expression". The de###### says that the offending line is:

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    I don't know if this is any help but the two fields I am using are both Foreign Keys and both are Data Type Number; Field Size Long Integer

    Many thanks

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if data item already exists (2003)

    The example I posted tried to match on a text field and a number field for illustration purposes; I indicated this in the introduction. The code surrounds the text value with single quotes. Since you match on two number fields, you shouldn't use the single quotes:

    <code>strSQL = "SELECT * FROM tbl_PlantTreatment" & _
    " WHERE TreatmentRef = " & Me!TreatmentRef & _
    " AND BatchRef = " & Me!BatchRef</code>

  10. #10
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if data item already exists (2003)

    Hi Hans

    Thanks very much for this. Apologies that my base level of knowledge is so low!. The code now works fine. The Message Box displays and if YES option button is selected all is fine and the entry is accepted. However, if the NO option button is selected then the only way out is to exit the form. If you try to delete the contents of the text boxes you get an error message: Run-time error '3075': Syntax error (missing operator) in query expression 'Treatment Ref = AND BatchRef =1'. Is it possible for the code for the NO option button to revert the form to its 'pre data entry' state?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Check if data item already exists (2003)

    You can use Me.Undo to undo the data entry:
    <code>
    ...
    If Not rst.EOF Then
    ' Ask user whether (s)he wants to continue
    If MsgBox("A record already exists for that Treatment/Batch." & _
    vbCrLf & "Do you want to continue anyway?", _
    vbQuestion + vbYesNo + vbDefaultButton2) = vbNo Then
    Me.Undo
    Cancel = True
    End If
    End If
    ...
    </code>
    Note that I changed the way Cancel gets its value slightly, because now, we have to do two things when the users clicks No.

  12. #12
    Star Lounger
    Join Date
    Apr 2004
    Posts
    66
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Check if data item already exists (2003)

    Hi Hans

    Works like a dream. Thank you very, very much.

Posting Permissions

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