Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Location
    California
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run a macro that tells me if I'm duplicating data? (Access2k)

    Okay.... I'm almost done with this basic database except for this last thing.

    Basiclly, this is what I want to do. I have a table which contains data for my submissions. There are two fields in this table (Account Name) and (Effective Date) that I need to compare against.

    In my form I would like to run a macro "after update" on the [EffectiveDate] to let me know if the [Account name] and [EffectiveDate] are already in the table?

    Please help.....I'm a newbie <img src=/S/baby.gif border=0 alt=baby width=15 height=15>

  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: Run a macro that tells me if I'm duplicating data? (Access2k)

    There is no macro that will do this for you. There are 2 possible solutions. The simplest is to have a unique index on table that is comprised of Account Name and Effective Date. That way, when Access goes to write a new record, an error message will be displayed warning of a duplicate record. You other alternative is to manually do the checking yourself using VBA code; but the best place for that is in the Form's BeforeUpdate event.

    BTW, you might want to revisit identifying accounts by an account name. My experience has been that sooner or later you will have 2 people with the same name.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Location
    California
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    Thank you Mark for getting back to me so soon. The problem is that all I need it to do is let me know if the record is already in the table by some type of message. But I need it to be able to by pass the message if the user chooses to continue.

    You see I work for an insurance company, so the same account names are going to come up every year, that's why there is going to be accounts with the same name but different effective dates.

    Any other suggestions would be helpful!! I don't know what else I can do?? <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

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

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    But you wouldn't put in a duplicate record for the same account name and date would you, at least not without a policy number or something like that to distinguish between them? In that case, indexing the fields together would prevent duplicates but allow them to enter a new or renewal policy for the same account.
    Charlotte

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

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    Well, I'm not sure I fully understand the situation, but you can easily check for a duplicate. You need to use the DLookup function, if it is not null, then you have a duplicate. You can lookup the format of DLookup in help, but the general code would be something like this:
    if IsNull( DLookup("transDate","tablename","...where criteria...") Then
    If vbno=msgbox("Duplicate exists, continue?",vbquestion + vbyesno) then
    cancel=true
    exit sub
    end if
    end if
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    New Lounger
    Join Date
    Jul 2001
    Location
    California
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    What I want from my form is a message box to appear when ever I have entered an application/submission that has the same tax number and effective date. Both the Tax number and Effective date reside in the same table.

    I have set the code to run after up-date on the Effective date but I'm having trouble with the criteria part. I keep get a syntex error.

    However, I was able to do a Dlookup on for duplicate Tax number but how do I compare it to the Effective date?

    this is what I tried under a macro:
    [dlookup]("TaxNo","Prospects")

    Now, I want it to compare against effectiveDate??? HOW???


    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

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

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    I'm having to guess at table and control names, and I'm using DCount instead of DLookup, although they function similarly. DCount will return a zero if nothing found, rather than a Null. So, you want:

    Dim strWhere as STring

    strWhere = "[AccountName]=" & chr(39) & txtAccountName & chr(39) _
    & "AND [effectiveDate=#" & txteffectiveDate & "#"

    If Dcount("#","yourtablename",strwhere)>0 then
    msgbox "duplicate exists"
    ' your logic here
    endif
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    New Lounger
    Join Date
    Jul 2001
    Location
    California
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    Hi Mark,

    Thanks again for all you help. I tried what you suggested and I get this error. Please take look and tell me what you think!!
    Attached Files Attached Files

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

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    You need to use an * and not #. So it should be DCount("*",.........)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  10. #10
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run a macro that tells me if I'm duplicating data? (Access2k)

    I had the same problem only for Employee and Week Ending Date...I didn't make any modifications to this test db...but it works PERFECTLY for what you want...all you will need to do is assign your fields names to the code rather than mine.

    Good luck.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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