Automatically changing cell content (newbie question)

[BigBrown]

I wasn't sure which section this belonged to (tables/forms/visual basic?) so I posted it here. Keep in mind I'm a database and VB novice so please bare with me.

I'm trying to create a database based on an Excel document. In the Excel document different entries selected from a dropdownlist will automatically generate content in other columns (for example cell C1 might be generated by "=A1+B1" in Excel).

I created a main table and linked it to other tables using the wizard (sorry, don't know the name for it in English), which allowed me to select values in the main table using a dropdownlist. See the attached picture. Then I created a form using the wizard. To be able to automatically change the value on one cell depending on the value of another I used this bit of code:

Me!KlarTextboxName.Value = Me!DatumTextboxName.Value + 7

With the above example I could change the date in column "Klar" to seven days ahead of column "Datum", by setting this to be activated on the "Datum" update event.

But I have no idea how to change the value on one of the columns that are linked to another table! I tried using the same method, and changing the numerical value of the cell works (it will link to the numerical key value of the sub table) but that isn't very dynamic. If for some reason the key values of the sub table are changed then the script will link to the wrong entry.

I'm grateful for any help or advice you might have to offer me. If I'm not being clear enough on what the problem is please tell me so I can clarify. Thanks.

 

[neileg]

It would help if you tell us why you are doing this. In general, it is bad practice to store data that can be calculated from other data. Any time you need the calculated data, you can do this in a query or a form.

 

[BigBrown]

It would help if you tell us why you are doing this. In general, it is bad practice to store data that can be calculated from other data. Any time you need the calculated data, you can do this in a query or a form.

They do alot of adding and removing rows of data, and the changes would be mainly out of convencience. An example: They set the start date of a project, and by doing that it automatically calculates the end date, seven days later. But that is not always the case because they need to be able to change the end date, if for some reason the project is supposed to finish earlier or later than normal.

 

[neileg]

OK, I understand.

In your first post, are you suggesting that a field that is used to link two tables is being changed? That will never work. You should use a meaningless field such as an autonumber to link tables. That way you can change the fields with meaning without spoiling the link.

 

[BigBrown]

What I'm trying to say is I can change the value of the cell by changing its reference key (number) value but that can lead to trouble in the future.

See the picture below. This is the main table. There is a column there named "Kund". It is linked to another table using a number as primary key, and you can select different values from a dropdownlist. The dropdownlist is what is present in the sub table.

Here is the sub table "Kund".

Using an event in Forms, I can change the value of the Kund entry in the main table Order. For example, if I want to set the Kund to "Hald" (see picture above), I use the following code:

Me!Kund.Value = 3

I set it to 3 because 3 is the numerical primary key of Hald. The problem would be that if for some reason "Hald" no longer is primary key 3 in the Kund table then the code would be incorrect. Is there some way to link it to the name "Hald" instead of the number 3?

I'm not sure I'm making my self clear here... Please ask more questions if you need to.

 

[Rabbie]

I set it to 3 because 3 is the numerical primary key of Hald. The problem would be that if for some reason "Hald" no longer is primary key 3 in the Kund table then the code would be incorrect. Is there some way to link it to the name "Hald" instead of the number 3?

I'm not sure I'm making my self clear here... Please ask more questions if you need to.

It is good practice to use the primary key as the link as you have done. Is there any reason why you think that you would change the primary key of the record holding "Hald"?

 

[neileg]

I agree with Rabbie.

I would also say that using these table level lookups is a bad idea. They cause all sorts of problems. You should not be entering data into a table, you should use a form where you can use a combo or a list box to do this.

 

[BigBrown]

Sorry. Again, I think it is me who isn't expressing myself good enough. I'm using a form and changing the value of the cell in the main table which points to the Kund table using the primary key number.

So by changing the value of that cell to 1, I will point it to Presto, 2 to Vivestra and so on.

I'm not changing the primary key, I'm just worried that if for some reason an entry gets deleted and they create a new one, the code that referenced primary key number x will no longer do what it is supposed to.

Maybe I should attach a sample database to better describe what I'm doing/trying to do?

 

[BigBrown]

Okay, I made a quick sample database on what my troubles are. Access 2003.

Use the form to enter data. You will see that it automatically sets the finishedDate column based on your start Date. Also it will automatically suggest bananas for monkeys and seeds for birds in the Act / React columns (which both got their own sub tables). They need to remain suggestions, since sometimes I will want to feed bananas to my birds and seeds to my monkeys
The VB codes are activated on the "after update" event.

I'd highly appreciate it if you would take a look. Thanks alot for helping me so far.