having one field display when another field changes

I have a field CHSTAT that updates in the original database I want a new 
field that will display something different as the CHSTAT updates and changes
ie. CHSTAT = "10"  new field = "Printed"......CHSTAT = 20  new field= 
"Located"

Cheers!!
0
Utf
6/2/2010 3:19:01 AM
access 16762 articles. 3 followers. Follow

4 Replies
993 Views

Similar Articles

[PageSpeed] 0

In tables and fields you should have a table that has a field that has the 
CHSTAT values and a field that has the equivalent text values.

Then you join that table to the current table in a query on the CHSTAT values.

If you want to do this on a form, you could use a combobox to select the 
values.  The source of the combobox would be your current CHTSTAT field.  The 
combobox's row source would be a query on the new table.  The combobox could 
display the numeric value and then your form could have a separate text 
control to display the other information.  The source for the text control 
would be something like
    =[Name Of Combobox].Column(1)


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Norm wrote:
> I have a field CHSTAT that updates in the original database I want a new 
> field that will display something different as the CHSTAT updates and changes
> ie. CHSTAT = "10"  new field = "Printed"......CHSTAT = 20  new field= 
> "Located"
> 
> Cheers!!
0
John
6/2/2010 12:59:51 PM
Norm,
    You dpn't say how many different Chstat values there may be, but if more
than 5 or so, it would be a candidate for a combo box, (ex. cboChstat)
based against a table of preset values.  Ex. tblChstat...
    Chstat      Desc
       10         Printed
        20        Located... etc
     Bind cboChstat to your Chstat field of your table, and use the combo to
select a Chstat value.  An unbound text control on the form, with a 
ControlSource
of...
        =cboChstat.Colunm(1)
will always display the contents of the 2nd column in cboChstat.  (combo
columns are numbered left to right 0, 1, 2, 3, etc.)
    No need to save Desc... it can always be re-related to from the Chstat 
value
in any subsequent query, form, or report.
-- 
    hth
    Al Campagna
    Microsoft Access MVP 2007-2009
    http://home.comcast.net/~cccsolutions/index.html

    "Find a job that you love... and you'll never work a day in your life."

"Norm" <Norm@discussions.microsoft.com> wrote in message 
news:63022CA2-FE5D-4450-AC0C-B488B357A073@microsoft.com...
>I have a field CHSTAT that updates in the original database I want a new
> field that will display something different as the CHSTAT updates and 
> changes
> ie. CHSTAT = "10"  new field = "Printed"......CHSTAT = 20  new field=
> "Located"
>
> Cheers!! 


0
Al
6/2/2010 1:24:37 PM
Thank you for your replies...what I forgot to mention is that the data comes 
from a table that is linked to an AS400 system and not entered on a form...I 
was hoping it could be along the lines of a conditional format in Excel

"Al Campagna" wrote:

> Norm,
>     You dpn't say how many different Chstat values there may be, but if more
> than 5 or so, it would be a candidate for a combo box, (ex. cboChstat)
> based against a table of preset values.  Ex. tblChstat...
>     Chstat      Desc
>        10         Printed
>         20        Located... etc
>      Bind cboChstat to your Chstat field of your table, and use the combo to
> select a Chstat value.  An unbound text control on the form, with a 
> ControlSource
> of...
>         =cboChstat.Colunm(1)
> will always display the contents of the 2nd column in cboChstat.  (combo
> columns are numbered left to right 0, 1, 2, 3, etc.)
>     No need to save Desc... it can always be re-related to from the Chstat 
> value
> in any subsequent query, form, or report.
> -- 
>     hth
>     Al Campagna
>     Microsoft Access MVP 2007-2009
>     http://home.comcast.net/~cccsolutions/index.html
> 
>     "Find a job that you love... and you'll never work a day in your life."
> 
> "Norm" <Norm@discussions.microsoft.com> wrote in message 
> news:63022CA2-FE5D-4450-AC0C-B488B357A073@microsoft.com...
> >I have a field CHSTAT that updates in the original database I want a new
> > field that will display something different as the CHSTAT updates and 
> > changes
> > ie. CHSTAT = "10"  new field = "Printed"......CHSTAT = 20  new field=
> > "Located"
> >
> > Cheers!! 
> 
> 
> .
> 
0
Utf
6/2/2010 2:24:02 PM
Well you could build a complex calculated field in a query.

    Switch(ChStat=10,"Printed",ChStat=20,"Located",ChStat=30,"Destroyed")


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Norm wrote:
> Thank you for your replies...what I forgot to mention is that the data comes 
> from a table that is linked to an AS400 system and not entered on a form...I 
> was hoping it could be along the lines of a conditional format in Excel
> 
> "Al Campagna" wrote:
> 
>> Norm,
>>     You dpn't say how many different Chstat values there may be, but if more
>> than 5 or so, it would be a candidate for a combo box, (ex. cboChstat)
>> based against a table of preset values.  Ex. tblChstat...
>>     Chstat      Desc
>>        10         Printed
>>         20        Located... etc
>>      Bind cboChstat to your Chstat field of your table, and use the combo to
>> select a Chstat value.  An unbound text control on the form, with a 
>> ControlSource
>> of...
>>         =cboChstat.Colunm(1)
>> will always display the contents of the 2nd column in cboChstat.  (combo
>> columns are numbered left to right 0, 1, 2, 3, etc.)
>>     No need to save Desc... it can always be re-related to from the Chstat 
>> value
>> in any subsequent query, form, or report.
>> -- 
>>     hth
>>     Al Campagna
>>     Microsoft Access MVP 2007-2009
>>     http://home.comcast.net/~cccsolutions/index.html
>>
>>     "Find a job that you love... and you'll never work a day in your life."
>>
>> "Norm" <Norm@discussions.microsoft.com> wrote in message 
>> news:63022CA2-FE5D-4450-AC0C-B488B357A073@microsoft.com...
>>> I have a field CHSTAT that updates in the original database I want a new
>>> field that will display something different as the CHSTAT updates and 
>>> changes
>>> ie. CHSTAT = "10"  new field = "Printed"......CHSTAT = 20  new field=
>>> "Located"
>>>
>>> Cheers!! 
>>
>> .
>>
0
John
6/2/2010 5:17:10 PM
Reply:

Similar Artilces:

Two DC's acting as Backup DC, how to enable one of them as Primary DC
Hello Everyone, I inherited a network that has two domain controllers acting as backup domain controllers. How can I promote one of them to act as a primary domain controller. I noticed when opening Active Directory Users and Computer > Right clicked on the domain > Operations Masters > RID, PDC, etc. display "Error". Any help or direction is appreciated! Thanks! Hello J. Forti, If i understand you correct, 2 DCs are existing and none of them is listed as FSMO role holder. Please check this with "netdom query fsmo" in a command prompt. If ...

How to stop RichEdit color change overriding default color
I've got a handly little syntax editor going along (that--so far-- is waaay faster than the examples on codeguru.com (if you'd like to know how to speed them up, I spotted a logic flaw in two of them. If you're interested, just email me). I have a question on a different syntax editor I'm writing for an in-house language though, so you'll notice some unique features that enable me to take shortcuts. But I've got a funny little problem. If the user is typing at the end of the rich edit control, and they type in a keyword and it gets colored, any text typed after tha...

Legend Changes After Save
I am working in Excel 2007. When I delete certain Series from the legend, save and close the file, the next time I open it the deleted series are back. How can I stop it from defaulting back? Thanks! PD, I can't duplicate this problem, why don't you send me a copy of the file before you delete the item from the legend and tell me exactly what you do, so I can duplicate it. -- Cheers, Shane Devenshire shanedevenshire@sbcglobal.net Microsoft Excel MVP "WolfgangPD" wrote: > I am working in Excel 2007. When I delete certain Series from the legend, > save and...

Problem changing parent Business unit
WE have re-arranged some business units into a more appropriate hierarchy, however, we have noticed that a user in a role with deep privileges (own business unit and children I think) to read users can only see users in his own business unit and only one of the child business units (there are 2 child business units). We cannot enable him to see all the users in both child business units. We tried reassigning the parent business unit to the one child that is not appearing, but it doesn't seem to make any difference. WE also tried adjusting the privileges up and down (i.e. Global and local...

Printing changes layout
I am printing a booklet on Pub 2002 - when I print one page, it prints a page where everything has changed - resized, cut off the edges, etc. The worst part - when done printing, it goes back to normal view and it actually changes my layout to match what is printed - permanently. Any ideas what is going on? I printed on both my Lexmark and HP printers, does the same thing. I have Windows XP. Help! Thanks! Look at your page layout, has it changed? I know this will happen to me occasionally when I change printers. If I change back to the original printer the publication will revert...

Unable to add custom field to a view/preview
I added a new attribute to the Quote and I am able to add this attribute to the form but the new attribute does not appear in the list of fields when I create a new view. Further, the new field does not appear in the list of fields if I try to add it to the quote preview. I am able to store and retrieve the value of the new field using the API. Thanks for any info. I figured it out. JohnE wrote: > I figured it out. Hi John! How do you solve the problem? Publish and IIS reset don't help. I don't see custom fields in "Customize views", but i can see custom fields ...

How do I display daily duty timings (day/night) of employees for .
I am looking for a template that would permit me to display the work timings of my 30 employees. They either work the day shift or the night shift. ...

Drop line on only one data point in series
Is it possible to add a drop line to only one data point in a series? If so, how? I believe attaching an autoshape line to a single data point would also meet my needs. I would like to be able to have a single data point marked with a line that doesn't require repositioning every time I add new points to the end of the series, which is daily. Any ideas? An autoshape would move anytime a heavy truck drove by. You could add custom negative error bars to the series, where the range containing the values contained zero for each point except for the one you want the drop line for. Easie...

Field Service V2.0
I know that there probably isn't a lot of solid info avail about feature set for V2.0 but I have seen a number of references to planned addition of some type of Field Service functionality. My question is.... Does anybody have any insight or thoughts as to whether or not the ability to schedule/dispatch techs and the ability to generate invoicing for field service calls will be included? There is something referred to as Service Scheduling in the latest information that I have seen. "SteveT" <drumguy61-google@yahoo.com> wrote in message news:1102972818.458191.150090@z14...

How do I link a shape on a organ chart to another org chart page
If I have a org chart on one page with three shapes, A B C, and a second page with names in shapes of people reporting to C on page 1. How can I click on Shape C on first page and bring up the second page showing the employees who work under him C -right click shape C -Format > Behavior > Double-Click -Select "Go to page: Page-2" Frank Help <Help@discussions.microsoft.com> schrieb in im Newsbeitrag: 9A0D1770-05EE-42D1-ACB3-A0958688AE64@microsoft.com... > If I have a org chart on one page with three shapes, A B C, and a second page > with names in shapes of pe...

changes to message size limits
On Exchange 2003, one huge annoying feature is that even when I make configuration changes, it seems to take so long for them to actually go into effect. This time it's the message size limits. I tried changing them for an individual user in AD, even told the RUS to resync. An hour later that still hadn't taken effect, so we moved on and changed the global default in Exchange's Message Delivery properties. Another hour later and still no dice. We had a company-wide default of 15 MB (huge, I thought). I increased my account to 30 MB in AD, and then on the Message Delivery pro...

Old emails not displayed (Multiple PC's using same inbox)
I have 2 machines running XP and Outlook 2000. Both mahcines are setup to using the same Exchange Server email account. When i open outlook in one machine all mail are visable is the inbox, and new mail is received correctly. But if i opne Outlook on the second machine my inbox is empty. Any new mails will appear quickly then disappear. I cannot get the second machine to display old emails. If I leave the first machine off, all new mail will stay in the second machines inbox, but if I open outlook on the second machine all mail disappears and is shown in the first machines inbox. A...

Font size change
Is there a way to increase the size of the font. I have a large, hi-res screen and MS-Money insists on using something the size of a 6-7 point font. Thanks, In microsoft.public.money, Linda wrote: >Is there a way to increase the size of the font. I have a large, hi-res >screen and MS-Money insists on using something the size of a 6-7 point font. Try changing the font setting in IE to a larger size. View->TextSize. If you want to see the size of the font in money. You must change the computer's font size. within money there is no option to do so. you would have to go to...

pivot table changes when data is refreshed.
Hi, I have a pivot table with grouping based on a field called "period which is of date type. I have grouped it into months and quarters However when i refresh the pivot table whenever the data (which is in seperate sheet) is changed, the groupings are going out. I want t preserve the groupings as they are like a template. How do i stop th changing the format and layout of the pivot table whenever i refres the data? any ideas? please help. regards Kiran:mad ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages d...

right align int fields
I would like to right align integer fields. I would like to do this in a while/for loop in onLoad event, because I have many fields. How can I see in javascript if a field (I do not mean the value of a field) is an integer. Marc ...

One Record Per Page Printing-Access 2007
Report for daily appointments for senior transportation drivers. Report is grouped by Driver. Keep together is selected. Detail band: Keep together-Yes; Force New Page-Before Section; Can Grow-Yes; Can Shrink-Yes Goal: Report generated so each driver has his/her own appointments. Orientation: Landscape, paper size 8.5 x 14, column width: 13.5729"; column height: 0.3069" When previewing in Print Preview one record per page is appearing rather than all appointments for a driver for a given date. I have the bands as narrow as I can make them. I am missing somethi...

Redefine field data type
Hi, I am trying to write a function in a module in MS Access 2000 that will change the data type of a field called 'Start' in table 'bo_cpm_CS01ALL'. Here is the code that I have done so far but when I run it nothing happens...no errors or changes to the table. The code finds the table and field, creates a new field called 'temp' then copys 'Start' to 'temp' then deletes 'Start' and renames 'temp'. The field 'Start' has data type dbDouble. Any help would be great!! Public Function ChangeFieldType() 'Purpose: Changes a f...

User Display name
I have a minor issue I cannot figure out. One of my CRM users display's as FirstName, LastName the rest as Lastname,FirstName. How do I get them all to display correctly? Thanks in the organization settings of CRM you can set the full name format. unfortunately when you change this is does not update existing records...only newly created ones. "MattB" <MattB@discussions.microsoft.com> wrote in message news:ECC6A9CF-6E69-478E-8D41-F4A1C0EEACAF@microsoft.com... >I have a minor issue I cannot figure out. > One of my CRM users display's as FirstName, LastName ...

Change default view of Activies to Service Activities.
When clicking on any "Activities" tab, CRM shows list of activities of the object, and it's the view: "Open Activity Associated View". Can we customize so that the view "Open Service Activity Associated" will be shown instead? Only show the Service Activities. Hi, Here is something for your interest. Thanks to Michael. http://www.stunnware.com/crm2/topic.aspx?id=JS11 -- PLEASE do click on Yes or No button if this post is helpful or not for our feedback. uMar Khan :: CRM Freelance Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.word...

Help with displaying the contents of the last populate cell.
I have numerous sheets within a book where all cells in column C in all sheets have the following formula “=IF(ISBLANK(P4),"",(R3-P4))”. For you reference both columns P and R hold a monetary value and are formatted as Currency. Is there a way that cell D1 can automatically be populated with the contents of the last cell in column C that has a value in it. E.G. Sheet 1, cell C19 has a value of 200, therefore cell D1 should be 200. Sheet 2, cell C25 has a value of 250, therefore cell D1 should be 250. Sheet 3, cell C99 has a value of 900, therefore cell D1 should be 900. Any h...

Charts not recognizing source data if original linked data is changed.
I am very frustrated by Excel (2003) at the moment. I'm relatively new to using Excel in depth. I have always been able to work my way through most intricacies and pitfalls. But now I have been working in a workbook with linked sheets (and with linked workbooks too) and have two problems that I cannot solve. Right now, I have about 47 sheets that are set up as follows: detail data: this is the raw data the the user enters daily totals: this is calculated data from the detail sheets summary sheets: further sums from the daily totals and a chart object that charts these sums (a line or sc...

Outlook 2003 Adding several Emails to Block Sender list at One Tim
I just upgraded to Windows 2003 SP 2. In the older version of Outlook, I could highlight several emails and add them to the junk senders list by selecting: Action/Junk Email/Ad to junk senders list With 2003, the option to “Add Sender to blocked senders list” is grayed out and I am not able to select it. What can I do to be able to use this? It is available when I select an individual email. ...

Restricting SubForm to One Record Entry Per Parent: Access 2007
Hello All, I have a one-to-one relationship between two table. I have successfully added the subform, but I get the option to add additional child records in the Datasheet View. This means that when a user tabs to the next row and enters data they get an error--and they should. Question: How do I restrict the subform to show only one row in Datasheet View? I hope I was clear. Feel free to ask for clarification. Thank you in advance for your assistance. Alcide, You have two options here. If the records are already added to the source of the subform you could set the &qu...

Changing Report Fields
What happens to the reports if we change screen fields. Does anyoneknow what happens in the event of: - adding a field – presumably doesn’t appear on any report - changing a field – label or properties - removing a field? Any help please? Hi, - Adding a field would change nothing to a report. You will have to alter the report itself to make it visible there. - Labels are set inside the report itself, so this won't change if you change them on the form. - Removing the field will not change the report, but it will be empty becaus the user cannot enter data for that field (unless som...

Changing of Cell protections after saving Excel File (2002)
This problem occurs when I protect a document using a macro 4.0 function: =PROTECT.DOCUMENT(TRUE,,,TRUE,TRUE). When I use the function within a macro4.0 macro, on an original file, everything works fine. The sheet has unlocked cells, and when the sheet is protected, it allows me to access those cells. But if I save the file, or save.as another name, then the fun begins. The enable selection of the sheet( view codes) has gone from 0-xlNoRestrictions to -4142- xlNoSelection. This locks me out of doing anything in the sheet. When I unprotect and then re-protect the sheet using the T...