Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2008
    Posts
    163

    Unanswered: auto-searching for duplicates

    In my form, I would like to be able to type a name on a text box, have the computer automatically search if it has duplicates in the db or not. If it matches with an existing record, it will go to the existing record. If it does not match any, I would like the computer to go to the "add new record" and input the name in the name field.

    I have a field for student names, it's indexed, no duplicates. The primary key is the student ID field.

    I have a form for managing this table, the process for adding new data is:
    1. type name
    2. select code from combo box
    3. press generate ID button

    At this point, the computer does not allow the ID field to be empty and refuses to accept duplicate names. Which is good because that's what it is supposed to do, but also...

    Step 4 is press the "add section" button to open a pop-up form in order to enroll the student into the appropriate sections.

    The whole process works fine. What I'd like to change is the "what if the student is already enrolled in an old section?" process. In this case, there is already an existing record for the name and ID of the student. What should happen is that I look for the record (via filter or find record) then just do step 4.

    Unfortunately, the data to be encoded (the hardcopy) does not say whether the data I'm holding is that of a new student or an old one (which is one of the reasons why I'm making this db, the old paper records are too unreliable). So more often than not, the encoder will go through steps 1 to 3, then be told by the computer that the name is a duplicate.

    Then the encoder would have to delete the new record, find the matching name, then add the section to the student's data. It works, but it is time consuming (well, still less time consuming than the old pen to paper method, but it would be nice to smooth out all the kinks).

    Thanks!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    the old paper records are too unreliable
    Actually the old paper records are very reliable. They don't suffer from corruption or have to be compacted and repaired ^^

    What is the step 2 for? For that matter, what is the point of step 3?

    I think you are making it all too hard personally... why on Earth should a user hit a "generate ID" button... surely that is something that Access should be doing all by itself...
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jun 2008
    Posts
    163
    What is the step 2 for? For that matter, what is the point of step 3?
    The code selection step decides what ID the student is given. It selects a prefix for the ID sequence that is useful for the coordinators in keeping track of students. Originally, the combo box would generate the ID on after update. But this meant a new ID would be generated and stored even if the wrong prefix is selected. So I placed the generate code on a separate button that would only be pressed once the encoder is happy with the selection, this gave the user a little room for mistakes (selecting the wrong option in the combo box is a pretty common thing for us here). I hope that makes sense.

    Back to the topic, is this (the question I posted) even possible in access?
    type a name on a text box, have the computer automatically search if it has duplicates in the db or not. If it matches with an existing record, it will go to the existing record. If it does not match any, I would like the computer to go to the "add new record" and input the name in the name field.
    and by duplicate, I meant in a specific field in a specific table. =D
    Last edited by coffeecat; 08-19-08 at 02:23.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by coffeecat
    The code selection step decides what ID the student is given. It selects a prefix for the ID sequence that is useful for the coordinators in keeping track of students. Originally, the combo box would generate the ID on after update. But this meant a new ID would be generated and stored even if the wrong prefix is selected. So I placed the generate code on a separate button that would only be pressed once the encoder is happy with the selection, this gave the user a little room for mistakes (selecting the wrong option in the combo box is a pretty common thing for us here). I hope that makes sense.
    Why not have an OK button that silently generates the ID? Or for that matter, why not just use an AutoNumber for the ID?

    Quote Originally Posted by coffeecat
    Back to the topic, is this (the question I posted) even possible in access?
    As I tell my customers all the time... everything is possible in Access.

    Quote Originally Posted by coffeecat
    I would like to be able to type a name on a text box (AfterUpdate), have the computer automatically search if it has duplicates in the db or not. If it matches with an existing record, it will go to the existing record. If it does not match any, I would like the computer to go to the "add new record" and input the name in the name field.
    This is effectively the pseudocode for the VBA you need to write.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Jun 2008
    Posts
    163
    My ID uses multiple sequential codes: A(05)-(0001) to Z(08)-(9999), you guys told me that I can't use autonumber to generate that and I need to use vba for it. =D

    This is effectively the pseudocode for the VBA you need to write.
    Well, yeah, that is what I've been trying to do on my own. =p


    everything is possible in Access
    in before make toast. ^_^

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My ID uses multiple sequential codes: A(05)-(0001) to Z(08)-(9999), you guys told me that I can't use autonumber to generate that and I need to use vba for it.
    Uggh... I won't ask why. You're gonna have some fun with that later. Oh and BTW, of course VBA would be needed... what I was saying was that the VBA for the ID could be done at the same time as this "auto search" feature.

    Well, yeah, that is what I've been trying to do on my own.
    Ok, so where are you getting stuck? Have a go at it and if you get stuck, come back with the code and you can be guided.

    Sorry, but I don't have the time to write your code for you... I have my own to do
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jun 2008
    Posts
    163
    Works now except for the part where the search field is supposed to input the searched unmatched name into the new name textbox.

    Dim rs As Object
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[name] = '" & Me![namesearch] & "'"
    If rs.NoMatch = False Then
    Me.Bookmark = rs.Bookmark
    Else
    DoCmd.GoToRecord acActiveDataObject, , acNewRec
    me.name = me.namesearch
    End If
    The only part that's giving me a problem right now is the
    "me.name = me.namesearch"

    I get error 2135, property is read-only and can't be set. Which is odd since I didn't set properties to locked/disabled.

    any ideas? thanks!

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Name is a reserved word. You've been told that already, yet you're still using it

    Me.Name is the NAME OF THE FORM!!!!

    Try:
    Me.[Name] = Me.NameSearch

    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jun 2008
    Posts
    163
    oh yeah, brackets! also replaced form textbox 'name' with 'stname', works perfectly now.

    Thanks for the help!

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No problem

    In the end I did nothing but correct a reserved word... I just forced you down the right path ^^

    BTW, I'd personally go back to the tables and change Name to StName or something more definitive, but that can spawn a bit of work. The advantage is that you won't run into any errors with that reserved word.... and you'll never call a field Name again!!

    Or is that... "you won't name a field name name again?" ^^
    Last edited by StarTrekker; 08-20-08 at 04:05.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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