Show different value from different table based on existing value

[robbeh]

I feel like this is one of the standard uses for Access but for the life of me can't get it to work with an existing Table.



Essentially a table with a ton of records is imported via VBA. One of the fields is ObscureCode, for argument sake there are three codes: 001, 002, 003.

Is there any way to say when the ObscureCode cell value = 001, display Code Red. When it's 002, display Code Yellow.

I have tried setting up a relationship but that just clears all the fields in ObscureCode column. Is there a way to do this through VBA? I ask because I imagine when someone imports new data (the table is deleted and re-created with new data) the relationship will break. SO I will need to put in the VBA a way to link them back up.

Thanks a lot! Robb

[June7]

Is data structure always the same? Don't delete and recreate the table, delete the records and append data to existing table, therefore the link won't be broken.

CurrentDb.Execute "DELETE FROM tablename"

Why would relationship clear data in ObscureCode field?

How many codes are there? An expression could handle 3 but if there are say 20, a lookup table probably better.

[ssanfu]

If the table structure remains the same, you shouldn't delete the table. Delete the records and append the new records.

Doesn't matter what the value of the field is in the table. The table is for storage.

My choice would be to use a query with the Switch function. Or in a form, use the Switch function in the control.
Switch(ObscureCode = "001", "Code Red", ObscureCode = "002", "Code Yellow", ObscureCode = "003", "Code green")

[robbeh]

Thanks for the tip, very helpful as I run a similar process to backup the table information first. I will delete the records and append new ones as suggested.

There is only three codes and the table structure will always stay the same.

I am not sure why data was cleared from the ObscureCode column. There was some message that popped up when I made a link and when I clicked ok, boom, all fields blank. But then they were linked up with a nice dropdown with the three options. It seems the relationship was created but I needed it to update based on the current value.

Right now to display the data, I have a form that queries all the records in the table (just created by the form wizard) and I added a bunch of Text boxes that then filter the data based on the information entered.

What might be the easiest is keep the code in the table (that's fine) but when displayed, have it show the proper name. Is that possible on the form i have created?

[June7]

Advise not to build lookups in tables http://access.mvps.org/access/lookupfields.htm

I expect the data is still there but the lookup is obscuring that. Remove the lookup from the table and see what shows up.

Use combobox on form instead. http://www.datapigtechnologies.com/f...combobox3.html

I seldom use build wizards because they do things I don't like and have to fix later anyway.

[robbeh]

If the table structure remains the same, you shouldn't delete the table. Delete the records and append the new records.

Doesn't matter what the value of the field is in the table. The table is for storage.

My choice would be to use a query with the Switch function. Or in a form, use the Switch function in the control.
Switch(ObscureCode = "001", "Code Red", ObscureCode = "002", "Code Yellow", ObscureCode = "003", "Code green")

I tried following your adivce (there is only 4 codes) so it seems like the most sense. In the Control Source...right now it shows the column name: ObscureCode

I tried changing it to your code and I get an error, "The expression you entered contains invalid syntax", "You may have entered an operand without an operator".

June7, thanks for your feedback, removing the link worked. Now I just need to find a way to display values based on their current value. I like the switch idea and I feel it's what I need right now but I can't get it to work.

[June7]

Expression in textbox must be preceded with = sign.

Post your attempted code/query/expression.

[robbeh]

It did that by default so I was wondering if it was needed, here are the two I tried:

This one produced the error in the last message (The expression you entered contains invalid syntax..)

=Switch(ObscureCode = "001", "Code1",ObscureCode = "002", "Code2",ObscureCode = "003", "Code3",ObscureCode = "004", "Code4")

This one has a warning Invalid Control Property: Control Source --> Cicular Reference

=Switch([ObscureCode] = "001", "Code1",[ObscureCode] = "002", "Code2",[ObscureCode] = "003", "Code3",[ObscureCode] = "004", "Code4")

Edit: I am putting this in the Control Source of the textbox.

Thanks again for all your help!

[June7]

Textbox and field have the same name which causes a circular reference in the expression. Name the textbox different, like: tbxCode.

I don't see anything wrong with the expression but try this instead:

="Code" & Val([ObscureCode])

[robbeh]

Textbox and field have the same name which causes a circular reference in the expression. Name the textbox different, like: tbxCode.

I don't see anything wrong with the expression but try this instead:

="Code" & Val([ObscureCode])

You are the best. I always name all my textboxes with naming conventions I was taught way back in the day so I didn't think the textbox was named the same as the control source. Problem is I used a form from the design wizard.

I think someone warned me about using the design wizard

Changed to txtObsCode and boom this one worked like a charm.

=Switch([ObscureCode] = "001", "Code1",[ObscureCode] = "002", "Code2",[ObscureCode] = "003", "Code3",[ObscureCode] = "004", "Code4")

Thanks as always June

[June7]

Glad it now works, but Switch() appears to be more calc than needed.

[robbeh]

Glad it now works, but Switch() appears to be more calc than needed.

Would you suggest using the ="Code"... suggested above?

I tried it but it changed all the fields to "Code". I am always open to optimized approaches!

[June7]

Did you use the full expression I provided?

="Code" & Val([ObscureCode])

[robbeh]

Did you use the full expression I provided?

="Code" & Val([ObscureCode])

I did but I am starting to think I should have a table set up again with the codes and their meaning? Or better yet, how does that expression work?

[June7]

The expression should convert the text "002" to number value 2 and concatenate with string "Code" to result in "Code2".