Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2010
    Posts
    5

    Unanswered: lock a specific field in a relationship

    I'm building a database for my wine list at my restaurant. I've got two tables, one for the details of the wine (WINE DATA TABLE), and one for tracking my purchasing (PURCHASE DATA TABLE). I have the Wine ID in the wine data table autocomplete when I enter a new wine. In the purchase table, I do the same thing with the Purchase ID. I've added the wine ID in the purchase table so that when I create the relationship, each unique wine's purchases are tracked in the purchase table.

    the problem is that the Wine ID in the Purchase Table can be changed by a user. If that happens, a purchase of a specific wine can transfer to another wine. NOT GOOD. Is there a way that I can lock this field, so it can't be manipulated?

  2. #2
    Join Date
    Oct 2009
    Posts
    204
    If the Wine ID is autonumber, they won't be able to change that field. That way, Access assigns the next number in that field and it can't be touched. The other option is to remove that field from any forms or queries any other people use, or, if in a form, lock the field.

  3. #3
    Join Date
    Dec 2010
    Posts
    5
    access will not allow me to change to autonumber. probably because the the primary key of that table is set to autonumber.

  4. #4
    Join Date
    Oct 2009
    Posts
    204
    Is there any issue with using the primary key as the autonumber? When you said you had a wine id I thought you meant that was your primary key.

  5. #5
    Join Date
    Dec 2010
    Posts
    5
    The master table is table 1. This categorizes my wine. This also has the Wine ID which is the primary key. Table 2 is for purchasing information. It has a primary key called Purchase ID. The second field of this table has Wine ID, but it is the foreign key. The relationship between Table 1 and Table 2 is created using the Wine ID from Table 1 and Wine ID from Table 2. I tried setting the Wine ID from Table 2 to "autonumber" but access does not like that. It won't let me build the relationship.

  6. #6
    Join Date
    Oct 2009
    Posts
    204
    You should get rid of the wind id in table 2. Build a query with the fields in table 2 and the wine id from table 1, and find some other field with like info that you can build a relationship upon, then use that query as the data source for the form the users will be using.

    Edit: On second thought, have you tried using the lookup wizard for that field, then limiting the field to the drop down menu? That might be easier and more of what you are looking for.

  7. #7
    Join Date
    Dec 2010
    Posts
    5
    I can't do that because it's the Wine ID itself must join the relationship. The reason being is that throughout the month, I may purchase the same wine three or four times. So there needs to be that connection with first table. Let me lay it out a little better.

    Table 1
    Field: Wine ID (autonumber) Primary Key
    Field: Name of Wine (text)

    Table 2
    Field: Purchase ID (autonumber) Primary Key
    Field: Wine ID (number) Foreign Key
    Field: Date Purchased (date)

    These two tables are joined by Wine ID. So when I'm in Table 1, I can just drop down and enter the date I purchased that wine. The problem is, if someone goes into Table 2 they can accidentally change the number in Wine ID, and therefore the purchase will apply to a different wine. I need to lock that Wine ID cell in Table 2 ONLY, so that it can be manipulated. It seems so simple, there has to be a way.

  8. #8
    Join Date
    Oct 2009
    Posts
    204
    Can you post a copy of your .mdb? I guess I can't look at it at work but I can play with it when I get home tonight.

Posting Permissions

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