Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Primary and Secondary Keys...

    I'm trying to figure out how to do this with Access 2003 and I'm not sure at all how to go about it. I haven't done much with Access, but I do know Visual Basic.

    I need to set it up so that I have auto-incrementing numbers based on a first field. So it would be something like this (including starting at 70000 instead of 0)::

    Primary Secondary
    AB 70000
    AB 70001
    AB 70002
    DB 70000
    DB 70001
    DB 70002
    RD 70000


    RD 70001
    RD 70002

    I need to put in the records using a form. I need to get the next number in each set... so when I type AB into the first field, I need to get the next number in the second field for AB automatically. When I type RD into the first field, I need to get the next number in the second field for RD. And I need that auto-incrementing number to start at 70,000 for each of the two letter first fields.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    This link should be able to help.

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    Thank-You for that. That takes care of making sure that I can do the starting at 70,000 instead of 1. It's a really good example for handling a custom auto-increment event.

    I think this sample is kind of showing how to do it, but it's just not sinking in. How do I get it where I can auto-increment a number for each 2 letter combo... AB, DC, RD... each starts at 70,000. Do I have to do something with subforms, or multiple tables, or .....????

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I haven't looked at that sample in a while but it just takes a WHERE clause in the DMax().

  5. #5
    Join Date
    Jul 2009
    Posts
    3
    Thank-You... I think I understand how to do it now. I'll post up my code if I get it to work like I'm trying and I'll mark it solved. Wish me luck... lol

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Great! Here's a good link for the Domain function syntax.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting tables containing autonumber keys
    By kgash in forum Import/Export Data
    Replies: 0
    Last Post: 06-21-2009, 01:49 PM
  2. about the primary key!
    By Yuesko in forum Access
    Replies: 1
    Last Post: 05-29-2009, 04:20 PM
  3. How to make use of Function Keys (F1, F2 etc)
    By rommelgenlight in forum Access
    Replies: 1
    Last Post: 05-15-2009, 02:51 PM
  4. Are text primary keys less efficient than autonumbers?
    By bar tomas in forum Database Design
    Replies: 4
    Last Post: 05-11-2009, 09:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums