How to update field with contents of another field from a form control

[keun]

I have an update which is very simple:

UPDATE export SET export.level_2 = "20100527", export.level_3 = [level_2], export.level_4 = [level_3], export.level_5 = [level_4], export.level_6 = [level_5];

What I want to do is run this query from a form. The problem is, I cannot update using the field contents, because rather than (for example) updating [level_3] to whatever is in [level_2] the query puts the text "[level_2]" into the field.

Is there a way around this?

 

[dhookom]

You are holding back information. The update query that you have provided will not put "[level_2]" into any field. It will use the value of the field.

Your subject suggests "form control" but you haven't told us anything about form controls.

I assume you have a good reason for the un-normalized table structure.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Just a guess here, but if your [tt]level_2[/tt] is a TextBox, you may want to have:

strSQL = "UPDATE export SET export.level_2 = '20100527', export.level_3 = '" & Me.level_2.Text & "', export.level_4 = '" & Me.level_3.Text & "', export.level_5 = '" & Me.level_4.Text & "', export.level_6 = '" & Me.level_5.Text "'"

Have fun.

---- Andy

 

[keun]

I'm out of it. I included the WRONG bit of code. Here is the proper code:

UPDATE export SET export.level_1 = Forms!export!levelupdate.form!level1, export.level_2 = Forms!export!levelupdate.form!level2;

Basically, the table has the various fields populated, and I need to "insert" a value and have the other values "copy" to other levels. When I need to specify the inserted value, I want to be able to insert text. This inserted text will be the same for every record. But for the fields that are copied, I need to refer to the value of an existing field. These values are not the same for every record.

So, in the text box level_2 on my form I might enter an arbitrary text value, or I might want the query to grab the value of any other field, which I want to specify. So, Andy's code gets me close.

And Duane - thanks for pointing out my mistake. And yes, there is reason that the tables are not normalized. It is totally annoying.


Here is what we aer doing. I have a table with a document ID and then the level fields. Each level represents a directory level in a path structure. So level_1 is the highest level, level_2 is one deeper, etc.

So, if the level structure in the table represents that Document0001 is in folder MyDocuments\2010\00a and Document0001 is in folder MyDocuments\2010\00b but the files are really in MyDocuments\JUNK\2010\00a and 00b, then I want to run a query which updates level_4 to the value in level_3, updates level_3 to the value in level_2, updates level_2 to "JUNK", and leaves level_1 alone. My form has a textbox for each level and displays the data below. In this form I want the user to indicate if a level should stay the same, be updated to a new user-created value, or if the value should be updated to the contents of an existing field.