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?
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.
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.
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.
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.
Field: Wine ID (autonumber) Primary Key
Field: Name of Wine (text)
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.