Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55

    Show different value from different table based on existing value

    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

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    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")

  4. #4
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    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?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    Quote Originally Posted by ssanfu View Post
    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.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Expression in textbox must be preceded with = sign.

    Post your attempted code/query/expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    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..)
    Code:
    =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
    Code:
    =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!
    Last edited by June7; 11-12-2014 at 03:35 PM. Reason: Forgot something

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    Quote Originally Posted by June7 View Post
    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.
    Code:
    =Switch([ObscureCode] = "001", "Code1",[ObscureCode] = "002", "Code2",[ObscureCode] = "003", "Code3",[ObscureCode] = "004", "Code4")
    Thanks as always June

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Glad it now works, but Switch() appears to be more calc than needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    Quote Originally Posted by June7 View Post
    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!

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Did you use the full expression I provided?

    ="Code" & Val([ObscureCode])
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    robbeh is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    55
    Quote Originally Posted by June7 View Post
    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?

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The expression should convert the text "002" to number value 2 and concatenate with string "Code" to result in "Code2".
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 12-02-2018, 11:43 PM
  2. Replies: 2
    Last Post: 09-27-2014, 09:48 AM
  3. Replies: 29
    Last Post: 04-25-2014, 03:49 PM
  4. Replies: 2
    Last Post: 03-30-2014, 04:57 PM
  5. Update Query based on existing date
    By axdxnco in forum Queries
    Replies: 1
    Last Post: 06-12-2013, 02:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums