Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Dec 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    BeforeUpdate (Access 2000 SR-1)

    Hi there - I am trying to use a Before update event procedure to check if the information just entered into the box matches records that were created earlier. I basicaly need something that runs along the lines of:

    before updating the record, if the text entered matches text that has already been entered for that box on another record then open this form. Or else update and move to the next tab.

    The back ground is that we have a contacts database - if a user enters a new contact with the same name as one that already exists we want the database to check whether they really wanted to creat a new record (ie one with the same name, but say a different organisation) or simply edit information for one that already exists.

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

    Re: BeforeUpdate (Access 2000 SR-1)

    Use an unbound textbox for the name entry and use the AfterUpdate event of the textbox to search for the name or create a new record. You can't use a BeforeUpdate to move to another record or form.
    Charlotte

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

    Re: BeforeUpdate (Access 2000 SR-1)

    Welcome to Woody's Lounge.

    You could use code like this; it assumes that you have a table tblContacts, and a field named LastName. If there is a match for the last name entered, the form moves to the record for that match.

    Private Sub LastName_BeforeUpdate(Cancel As Integer)
    Dim strFilter As String
    strFilter = "LastName = " & Chr(34) & Me.LastName & Chr(34)
    If Me.NewRecord Then
    If DCount("*", "tblContacts", strFilter) > 0 Then
    MsgBox "Duplicate name!", vbExclamation
    Me.Undo
    Cancel = True
    Me.RecordsetClone.FindFirst strFilter
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End If
    End If
    End Sub

    BTW, in such situations, I usually create a separate unbound form for entering new records. This allows me to perform more extensive validity checks, and only create a new record (in code) if all conditions are met. I use AutoNumber ID fields in most of my tables, and letting the user start a new record, then cancel it if it turns out to be a duplicate wastes an AutoNumber value.

  4. #4
    New Lounger
    Join Date
    Dec 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeUpdate (Access 2000 SR-1)

    Thanks for this.

    I think this is sort-of getting there but: my objective is not to take them to the record with the same lastname, but to get them to choose from a list of records with the same last name or choose to create a new record with the same last name, but that is different in some other respect. I should probably explain the scenario in more depth - some of our contacts will represent different organisations at different times - ie they may respond to us as Bob Smith from the Parish council or Bob Smith from the county council. We want to keep the record of communication with the 'two' bob smiths separate, and allow for any other persons that may also have the name bob smith but be a different individual.

    Also - I tried the code you suggested, but now I get the following error when I test it:
    'Error acessing file: network connection may have been lost'

    Although I am not sure if this is related as I have received this error before on trialing other things (the database is not even on a network drive!)...

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

    Re: BeforeUpdate (Access 2000 SR-1)

    In a similar situation, I used three forms:
    1. <LI>An unbound form to enter a new contact. When the user clicks the OK button, the data entered by the user are checked. If a potential duplicate is detected, I pop up
      <LI>A bound (but read-only) form listing the potential duplicates. The user can select one of them and click on a button to switch to the 3rd form and view details for the selected contact, or click on a button to create a new record anyway.
      <LI>A bound form to view and edit existing records.
    I used global variables to pass information from one form to the other.

    I'm not sure what caused the error you got. I tested the code I posted and it worked without problems, but I'm using Access 2002 SP3, not Access 2000.

  6. #6
    New Lounger
    Join Date
    Dec 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeUpdate (Access 2000 SR-1)

    Yep thisis along the lines I was thinking where the user clicks on a button 'enter/view rocords' then goes to form 1, which asks them to enter a LastName - but how do I get the form to get (upon the user hitting enter) the form to check for potential duplicates?

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

    Re: BeforeUpdate (Access 2000 SR-1)

    You can use DLookup, as in my first reply in this thread, or you can open a DAO or ADO recordset on the contacts table, and use FindFirst (DAO only) or Seek to search for duplicates.

  8. #8
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: BeforeUpdate (Access 2000 SR-1)

    "Error accessing file: network connection may have been lost"
    See <!mskb=304548>Microsoft Knowledge Base Article 304548<!/mskb>. The best solution is to upgrade to the latest Service Pack (SP-3).
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  9. #9
    New Lounger
    Join Date
    Dec 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: BeforeUpdate (Access 2000 SR-1)

    Hmmm- sorry about this (I am basically at retard level with Access!), but I am not sure how to use the DLookup function. Based on the example in 'Help' I am using the following:

    Private Sub Text6_Enter()
    Dim varX As Variant
    varX = DLookup("[LastName]", "Contacts", "[ContactID] = " _
    & Forms!Form1!Text6)
    End Sub

    Where Form 1 is the first form the user comes to with only one unbound text box (text6) on it. I am guessing that DLookup would be triggered on the user typing a name and hitting enter. Cant quite get the above to work though - any ideas? Cheers heaps for the help by the way!

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

    Re: BeforeUpdate (Access 2000 SR-1)

    In the first place, the On Enter event of a text box does not occur when the user presses the Enter key but when (s)he moves to the text box from somewhere else ("enters" the text box). In the second place, your code doesn't do anything with the lookup value.

    You could use the After Update event of the text box, or place a command button on the form and handle it in the On Click event of the command button. The code needs to use an If ... Then ... Else ... construction to test whether duplicates exist, and act accordingly:

    Private Sub Text6_AfterUpdate()
    If IsNull(Me.Text6) Then
    ' Nothing entered - ignore?
    ElseIf IsNull(DLookup("LastName", "Contacts", _
    "LastName = " & Chr(34) & Me.Text6 & Chr(34))) Then
    ' No duplicate found
    ' Code to create new record goes here
    Else
    ' Duplicate found
    ' Code to display potential duplicates goes here
    End If
    End Sub

    You'll notice that I didn't write the actual code to be executed - that depends on what exactly you want to do.

Posting Permissions

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