Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83

    Unanswered: MS Access. Duplicate primary key message: handling

    Morning chaps/chapesses,

    Assistance please.

    I have two tables which are pre-populated with date values (usually a month's range) along with categories of other data corresponding to that date. I've done this using macros in a loop and the Setvalue function. Basically, this emulates a standard form that is used, and ensures that the user only has to fill in a quantity against the particular date and category. Would've been easier in VBA I know, but I still need to jump on that band wagon. I also feel reluctant to go down a flat file route unless forced into it.

    I have the two tables populating correctly. The issue is if the user were to pre-populate the tables again in a manner that violates the Primary Key. Access takes great pleasure in identifying the fact that the primary key is violated hundreds of times over. Has anyone got any ideas to stop the macro running on the FIRST occurence of a key violation? I just know I'm gonna get a VBA solution!

    Cheers.

  2. #2
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    Are you not inputting the data through a form? If you were, you could check for duplicates before the entry was accepted!

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by jonesyfella
    ....Would've been easier in VBA I know, but I still need to jump on that band wagon.

    I just know I'm gonna get a VBA solution!.....

    Cheers.
    mebbe its time to jump on the VBA bandwagon.....
    come over to the dark side, young Luke, give up your foolish ways of macros

    being a bit more serious:-
    VBA is there for a purpose, when macros & other tricks start to run out of steam then its time to switch to VBA.
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2004
    Location
    Harrogate, N.Yorks, UK
    Posts
    83
    I have a form with unbound StartDate and EndDate fields which would be set to a months date range. After populating the ContactDate field on the subform with the unbound StartDate and possible combinations of ContactType and AgeRange, the macro then sets the value of the unbound StartDate using the date it has just copied to the subform i.e ContactDate+1and repeats the process until it reaches the unbound EndDate value. Complicated yes!

    The macro is setting all of the values so there is nothing to check as such until the macro has run (or at least part of it has) and already violated the key.

    I'm finding myself at the end of the macro rope increasingly healdem! But you have to understand that I was scarred by VBA from the off when I upgraded my first database with VBA in it and it all stopped working! Plunge time inevitably coming up though I know. Maybe the flat file is the lesser of the two evils for now. I need to think through the potential headaches when it comes to aggregating the data...

Posting Permissions

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