Results 1 to 2 of 2
  1. #1
    kevin007 is offline Novice
    Windows XP Access 2002
    Join Date
    Jun 2010
    Posts
    21

    primary keys, cannot contain null value, Adding Records to a form

    Hi,



    Im new to Access. I just built the tables for a DB of tables that holds 10 years of sales data by product category and tables that holds ten years of external economic data (such as employment rates, housing indexes etc) that is all mostsly monthly data all tables are related to tbldates.

    tbldates has a dateID and a column of months beside it. The date ID # 1 of tbldates starts at Jan.1,1996 as the month. (please bear with me this is all going to make sence soon) ...all of my other tables start at 2000 which happens to be date id number 49.

    The reason tbldates starts at 1996 is all because of 1 table which is census data of population where the data only comes out every 6 years so I had to have a date ID as far back as 1996 to see any kind of patterns. Other than that all my tables start at 2000. Heres the point! All of the primary keys for my sales data and my other tables start at 49 so of nessesity I did not use Autonumber.

    Now I've built a form. (Im new to this remember) I pulled in all the fields I needed. I've never done this before but I did not pull in the date ID feild becasue this wouldn;t mean anything to my data entry clerk. The problem is (becasue I don;t have an autonumber populating as I add new records to the form) ...when I tried to add the latest months data to my sales form it says (primary key cannot contain a null value)

    What do I do about this? I s there a way to have my date ID feilds all be primary keys but also start at number 49? Or am I completely thinking about this the wrong way and theres a better solution for this?

    Please help

  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    The best way to handle this would be to add some sort of "tblConfig" Table for items like this. In there, you'd have a Long Integer Field called something like tbldates_NextID. This field should contain the first unused ID number for tbldates (in other words, the next number you're going to use).

    Then make VBA code that does the following each time a Record is added to tbldates:
    1. Read the tbldates_NextID field from your config table.
    2. Use tbldates_NextID as the dateID of your new Record.
    3. Increment tbldates_NextID.

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

Similar Threads

  1. Multiple primary keys
    By elektrisk in forum Access
    Replies: 5
    Last Post: 02-11-2010, 04:39 PM
  2. Primary/foreign keys + relationships
    By tripptofer in forum Database Design
    Replies: 0
    Last Post: 08-12-2009, 07:44 AM
  3. Primary and Secondary Keys...
    By LittleOleMeDesigns in forum Database Design
    Replies: 5
    Last Post: 07-24-2009, 11:33 AM
  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