Access -- Make a macro and relational lookup

Discussion in 'Microsoft Access Macros' started by Mac Hardy, Dec 31, 2009.

  1. Mac Hardy

    Mac Hardy Guest

    I have a database with 2 tables: Brds and Numbers.
    Birds contains 250,000 records and several fields, including one named
    SPECIES and one called SEQUENCE. Numbers contains only 350 records (all
    unique) and 4 fields: SPECIES, SEQUENCE, ABBREV, TAXON.
    I want a macro to enter a new record in birds as follows. I type a 4-letter
    abbreviation into the SPECIES field and the macro will find the record in
    Numbers, ABBREV field, and return the SPECIES field of Numbers to the SPECIES
    field of Birds, then return the SEQUENCE field of Numbers to the SEQUENCE
    field of Birds.
    There is more but I will take any progress offered. I have made none so
    far. Thanks. Mac Hardy
     
    Mac Hardy, Dec 31, 2009
    #1
    1. Advertisements

  2. Mac Hardy

    tighe Guest

    Mac,

    there be a more elegant way but:
    I would make it so where you type the ABBREV a combo box so you know you are
    using one that exists, then on the after update event of the combo box, run
    an append query that uses the Combo box data as filter to add the addition
    fields information to the new record, or you could use the setvalue action in
    a macro to Dlookup the other data based on the abbreviation.

    hope this helps.

    "Mac Hardy" wrote:

    > I have a database with 2 tables: Brds and Numbers.
    > Birds contains 250,000 records and several fields, including one named
    > SPECIES and one called SEQUENCE. Numbers contains only 350 records (all
    > unique) and 4 fields: SPECIES, SEQUENCE, ABBREV, TAXON.
    > I want a macro to enter a new record in birds as follows. I type a 4-letter
    > abbreviation into the SPECIES field and the macro will find the record in
    > Numbers, ABBREV field, and return the SPECIES field of Numbers to the SPECIES
    > field of Birds, then return the SEQUENCE field of Numbers to the SEQUENCE
    > field of Birds.
    > There is more but I will take any progress offered. I have made none so
    > far. Thanks. Mac Hardy
     
    tighe, Dec 31, 2009
    #2
    1. Advertisements

  3. Mac,

    There is a problem with the design of your tables. What you are proposing
    will mean that the same information is stored in two different places within
    your database. This is called un-normalised, and flouts one of the basic
    rules of database design, so this leads me to believe you have misunderstood
    one of the principles of relational data. Please let us know if you can
    consider a schema change, and if so somebody will be happy to help.

    --
    Steve Schapel, Microsoft Access MVP


    "Mac Hardy" <> wrote in message
    news:...
    > I have a database with 2 tables: Brds and Numbers.
    > Birds contains 250,000 records and several fields, including one named
    > SPECIES and one called SEQUENCE. Numbers contains only 350 records (all
    > unique) and 4 fields: SPECIES, SEQUENCE, ABBREV, TAXON.
    > I want a macro to enter a new record in birds as follows. I type a
    > 4-letter
    > abbreviation into the SPECIES field and the macro will find the record in
    > Numbers, ABBREV field, and return the SPECIES field of Numbers to the
    > SPECIES
    > field of Birds, then return the SEQUENCE field of Numbers to the SEQUENCE
    > field of Birds.
    > There is more but I will take any progress offered. I have made none so
    > far. Thanks. Mac Hardy
     
    Steve Schapel, Jan 1, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Steve Schapel

    Re: Password lookup with macro

    Steve Schapel, Oct 9, 2003, in forum: Microsoft Access Macros
    Replies:
    0
    Views:
    511
    Steve Schapel
    Oct 9, 2003
  2. Guest

    Use macro to open form from lookup field

    Guest, Jan 24, 2006, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    252
    Steve Schapel
    Jan 24, 2006
  3. Guest
    Replies:
    2
    Views:
    356
    Guest
    May 14, 2007
  4. Can you make a copy+paste macro in access?

    , Mar 28, 2008, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    1,315
    Steve Schapel
    Mar 29, 2008
  5. Timo

    Macro question lookup useful?

    Timo, Aug 14, 2009, in forum: Microsoft Access Macros
    Replies:
    1
    Views:
    280
Loading...

Share This Page