Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    walkthru a string to find dupes (Access 2003, XP SP 3)

    This is really more of a VBA problem. i iterate thru selected values in a form and build a string with the values like so:

    2;3;2;4;5;

    how do i walk thru the string and flag all cases where there's a duplicate value? perhaps an array? what i am trying to do is implement a flexible "forced range" set of questions where the user cannot enter the same value more than once across all questions. i'm trying to keep the UI interface to drop-downs.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: walkthru a string to find dupes (Access 2003, XP SP 3)

    Build your string like this: ;2;3;5;8;

    Essentially, every value is found betwee 2 semi-colons. Then for each control you encounter, you can merely look to see if its value is in your string. Like this:

    if instr(yourstring, ";" & cboWhatever & ";") > 0 then
    msgbox "Duplicate!"
    exit sub
    end if
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: walkthru a string to find dupes (Access 2003, XP SP 3)

    You could insert a semi-colon before the first entry:

    ;2;3;2;4;5;

    Take the first part ;2; and do an Instr on the remainder ;3;2;4;5;
    This is > 0 so there is a duplicate.
    Then take the second part ;3; and do an InStr on the remainder ;2;4;5;
    This is 0 so the 3 is unique.

    Or you could add the values to a Collection object. A Collection can't contain two items with the same key, so VBA will raise an error if you try to add an item for the second time. You could trap this error.
    (When you're done, the Collection will contain the list of unique items; this can be a useful technique)

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

    Re: walkthru a string to find dupes (Access 2003, XP SP 3)

    Oops, I was held up while replying. I see that you posted the same idea in the meantime. Sorry about that!

    (At least we don't disagree <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: walkthru a string to find dupes (Access 2003,

    I'm having trouble getting the instr to work as, if i parse the str beginning across all the combo boxes it thinks the first one is a "duplicate". you suggested doing instr against all but the first, then all but the second, etc. which sounds a bit cumbersome. at one point i tried loading the string to an array and do a walk across the array members but kept getting errors on referencing the array collection in a loop.

    the collections suggestion sounds a bit more interesting - will try that. I don't quite understand it but that's where VBA Object Browser comes in handy... or some more nudges from the Loungers <img src=/S/angel.gif border=0 alt=angel width=15 height=21>

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

    Re: walkthru a string to find dupes (Access 2003,


  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: walkthru a string to find dupes (Access 2003,

    yes that looks about right.

  8. #8
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: walkthru a string to find dupes (Access 2003, XP SP 3)

    >>.Oops, I was held up while replying. I see that you posted the same idea in the meantime. Sorry about that!<<

    No problem. I just look at it as validation! Besides, it looks better if we present a united front!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: walkthru a string to find dupes (Access 2003,

    Problem solved - i was thinking of doing this "on the fly" but putting the validation in a faux "submit" button works just as well, esp. if you're modeling a web app.

    I just put a for-next loop iterating thru the combo values to load into a Collection. since, as Hans pointed out, Collections choke on duplicates by default, I just leveraged the error and customized it a bit. Not perfect (as the error handler assumes that this is the Only Thing That Can Go Wrong) but good on several fronts:

    1. works on any number of controls (since the loop just finds 'em).
    2. no need to do arrays and compares, which was really starting to bug me.

Posting Permissions

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