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. 2 followers. Follow

4 Replies
772 Views

Similar Articles

[PageSpeed] 55

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:

can excel check one column against another
Hi. I have a spreadsheet with columns such as A, B, C, D, filled with numbers. Each row across such as row 1, then row 2, then row 3, etc of numbers is totaled in column E. Countless rows, each of which is totaled per row and then ends with a grand total at bottom of column E. Next comes columns F, G, H, also filled with numbers (but different numbers), which is totaled per row in Column I. At the bottom of Column I is a grand total. Even though arrived at using different sets of numbers, -both grand totals - in columns E and I must match. Sometimes they don't match. Ouch. So here is m...

why crash in the second method of one interface
Hi, Using one class generated from *.tlb to call 2 methods of one COM. The strange thing is that I got one error of the "First-chance exception in : 0xC0000005: Access Violation" in the second method. I also use ASP web page to call both methods of this COM and both methods seems is OK. ITemplateManage mytemplate; if (!mytemplate.CreateDispatch("comobj.TemplateManage")) { IDE_Error::Report (ComError|1,"ComObj.TemplateManage in FUN_Template::Add()"); return false; } result = mytemplate.Check (templatefilename); decodeparameter=mytemplate.GetInfo (templ...

Microsoft Money Plus -- Date Display in Account Registry
In one of those exasperating "tics" in a program where you say "It can't really work like that," but apparently it does, Microsoft Money Plus has a glitch when "matching" checks in one's check register with information downloaded from the bank: Money displays the date the check was written, and the date it cleared the bank. If one attempts to "accept" the matched transaction, Money then changes the "Date" displayed to the date the check *cleared* the bank, rather than the date written. This can be fixed manually, for each tran...

Duplicate Transactions, One When Pending, One When Posted
I'm sure this has been addressed, but I couldn't seem to find an answer searching: My problem is, a transaction occurs at my bank, and MM2K7 downloads the data for it. Once that transaction moves from "Pending" to "Posted" at my bank, the transaction data changes, so MM downloads it again. Sometimes I get a possible duplicate message from MM, sometimes I don't. What are people doing to handle this situation? Thank you. This is a problem with your bank, not with Money. Eventually they should fix it. Call them. In the meantime VOID the pending transacti...

MSR Credit Card Reader adds all the information on one line
Is there a way to get the CC reader to properly seperate the the acct # from the expiration date? When I swipe I currently get everything on the acct # line. Thanks in advance! What type of CC reader(MSR) to you have? Typically this is an issue with simply configuring the MSR. Rob "Steakum" wrote: > Is there a way to get the CC reader to properly seperate the the acct # from > the expiration date? When I swipe I currently get everything on the acct # > line. > > Thanks in advance! Its a IDTECH Minimag USB/Keyboard Interface Do I need to select the Wedge Key...

Field Service Help
Hi, I have Field Service installed in GP 7.5 environment whenever I am trying to Launch help by pressing F1 key or from help Icon in field service Screens it is not lauching but in other screen it is working fine. anybody phase this problem.if so what is the solution physically FSSHELP.CHM is available in path. Regards, Mans ...

Access 2003 and/or 2007 copy in objects from another mdb
Is is possible to write code in an Access MDB that will allow a user to select another Access MDB and then see a list of report and/or queries so they can select from the list. Then programatically copy in those objects from another mdb in to the current mdb? I know that access allows for the exporting and importing of objects but I have a need to do this programatically. Sure, it can be done. Here are the general outlines: 1) Use the Open/Save API that you can find here: http://www.mvps.org/access/api/api0001.htm to select the database file you want to inspect. 2) Progr...

grid off on one page
Is it possible to turn the grid off of only one page? thanks On Thu, 14 Feb 2008 10:37:11 -0600, "greg" <iuouh@ghfhg.com> wrote: >Is it possible to turn the grid off of only one page? Not really as it is a document wide setting.... however, there is workaround open the page shapesheet menu window -> Show shapesheet and set XGridSpacing and YGridSpacing to a large value. -- Regards, Paul Herber, Sandrila Ltd. SSADM for Visio http://www.saadm.sandrila.co.uk/ On Thu, 14 Feb 2008 16:47:53 +0000, Paul Herber <SubstituteMyFirstNameHere@pherber.com> wro...

One to One relationship
I have an Events entity that is an offshoot from the Contacts entity. Basically, it tracks what events each contact has been invited to, and is used mainly by the Events Coordinator. I've set it up as a One (Contact) to Many (Events) relationship. However, I really only want one event form for each contact form. The reason I've set up a different entity rather than just adding a tab is that access to this entity needs to be tightly controlled. Obviously, if many events forms are created for the one contact, it would be very difficult to tell which one is accurate etc. Is it possibl...

Changing IP address
We just changed ISP's and changed the IP address on the server. How no one can get to there e-mail, WHY? The server now has a 192 address and before has an external address. Care to provide more details, like type and version of client and server used, error messages (if any), name resolution setup etc? Bob wrote: > We just changed ISP's and changed the IP address on the server. How no one can get to there e-mail, WHY? The server now has a 192 address and before has an external address. -- Help fight spam - designate sending servers for your domain. http://spf.pobox.com The s...

Next Date given one date
At this time, I cannot use VBA. Here is my objective: For a given treasury note security: Cell A1, Date 6/17/2004 - today's date Cell A2, Date 7/15/1997 - first coupon date I want to identify the "previous coupon date" and "next coupon date" from today's date based on the frequency of the "first coupon date". Logically speaking: * Security pays 2 times each year * Based on first coupon date, pay dates are: 1/15 & 7/15 * Given today is 6/17/04, the next pay date is 7/15/04. * Given today is 6/17/04, the previous pay date is 1/15/04. Can this be...

User-defined fields in Tasks . . .
Two questions please: 1. How can I RENAME a user-defined field in Tasks, once it has been created. (not just in a column heading, but the actual field name) 2. How can I DELETE all fields whose value is (NONE) from all records so that my files do not become too large (after I have deleted obselete user-defined fields). I know how to delete field entries on individual records one at a time, but this would take me days. Thank you. ...

route inbound email from ip to one folder
Is there a way to route any incoming email from a certain IP address to a single folder? Our company is receiving emails from a source that we do not wish to receive, and would like these email to be directed to a singe folder rather than blocking them. Any ideas? Thanks. you could send them to the turf dir... http://support.microsoft.com/default.aspx?scid=kb;en-us;245465 "daine" <anonymous@discussions.microsoft.com> wrote in message news:11d501c4263d$f4d05110$a101280a@phx.gbl... > Is there a way to route any incoming email from a certain > IP address to a single ...

Search criteria with empty field
I am trying to search two fields by rst.Seek "=", Data1, Data2 where Data2 should be case 1 "" case 2 not empty. Any idea? I can't get real result. Alex ...

To view, specifically Units in one report
I have report with a Reporting Tree (9 Unit), when the report is generated, I select only two unit, but I need that the drill down show one report with these unit only If you want multiple units in a single report you will need to use the column layout with unit restrictions on multiple columns Leslie "Pedro" wrote: > I have report with a Reporting Tree (9 Unit), when the report is generated, I > select only two unit, but I need that the drill down show one report with > these unit only Leslie; Each Column layout have the unit restrictions Thanks :-) "Lesl...

One client (Server 2k8-R2/HyperV) will not sync with DPM
We have been running DPM successfully for about a year now without any major problems. Recently we added a new server - a fairly robust PowerEdge that will be housing 4 HyperV VMs. For whatever reason, I can't get anything but the HyperV "Initial Store" to sync correctly. I thought initially it might be a problem with the HyperV machines, but it also fails to back up a small directory of files on the host drive. The oddest part about this is I really don't get an error. It will try an initial sync and run for a day or two and show very minimal data tr...

workflow fires twice
I have a workflow associated with quotes that is set to fire when the status changes. The first line of the workflow checks the status. If the status is not won, the workflow cancels, if the status equals won, then the steps continue. The workflow fires twice most of the time. Both of the workflows that fire say they fired because a record attribute changed. I even created a very simple test workflow to rule out an issue with any of the steps in my real workflow as being part of the issue. What people are doing is taking a draft quote and activating it, then turning the draft quote in...

Exchange 5.5 - One Organization
Hello - I am having a problem that I would like some more information on. I have One Organization (5.5) and 16 Sites (5.5) and would like to know why I can't stop the other sites from sending mail through me. If I can, I would like to know how. Thanks for any help. what do you mean by "sending mail through me"? What mail? Internet email? Does each site have its own Internet Mail Service? Or is your site kind of the "hub" where all Internet email comes in and goes out? -- Susan Conkey [MVP] "dave.mudgett" <davemudgett@discussions.microsoft....

change the value of a combo box
I would like to change the value on a combo box I am using: Form_fmain!fmainp.pstatus.Value = "Design/Rev" The "Deisgn/Rev" is one of the choices. What I am doing wrong? Thank you all Bre-x Forms!fmain!fmainp.Form!pstatus.Value = "Design/Rev" thanks to: http://www.mvps.org/access/forms/frm0031.htm The column that displays the value "Design/Rev", is that the column that the field is bound to? Check BoundColumn property. "Bre-x" wrote: > I would like to change the value on a combo box > I am using:...

See more than one tab at a time
I would like to be able to see more than one Tab at a time in Excel. Currently I'm able to see two Tabs from different worksheets, but not two or more tabs from the same worksheet On Sat, 28 Aug 2004 14:57:03 -0700, "rocky59" <rocky59@discussions.microsoft.com> wrote: >I would like to be able to see more than one Tab at a time in Excel. >Currently I'm able to see two Tabs from different worksheets, but not two or >more tabs from the same worksheet That would be because each tab represents a single worksheet. Clicking on the tab would move you from where ...

update two fields at once
Hi Access Gurus, In A2003 I want to write an update query which updates about ten fields at once. The idea is that if any one of these fields is null, I update it with a value read from a linked Excel table. For the sake of simplicity, let's say I have only 2 fields, F1 and F2. Either one of them might have a null value. The logic is: IF F1 is null, update it, and IF F2 is null, update it, but if the field already has a value (is non-null), let it alone. Can I do this in one update query, or do I have to write a separate query for each field I am updating? In the quer...

Why Would You Lose Sound On One User
WXP Pro Version 2002 Service Pack 3 with all current updates Dell Pentium(R) D CPU 2.80Ghz 2.79Ghz, 512 MB RAM System has three users all with admin rights - User 1(primary - me), User 2 (wife), and System Admin. (which I made visible on desktop). I (User 1) have lost sound when attempting to play CD's, and other audio files using WMP 11, however, I can still hear system sounds. User 2 and System Admin. still have full sound. System restore does not help ( in this case I only had a restore point from 5 days ago. Any thoughts? -- Skeeter "Old age and ...

Extract Numbers from a text field whereever they are
If I have a text field and the data starts with numbers I know I can use VAL to extract the number but if the data starts with a letter VAL returns 0 - i.e A109 = 0 whilst 109A =109. How can I convert to a number wherever the text data actually is? I'm hoping there is a function for this as I don't really write code. Thanks for any help On Thu, 6 Mar 2008 07:45:01 -0800, Sheila D wrote: > If I have a text field and the data starts with numbers I know I can use VAL > to extract the number but if the data starts with a letter VAL returns 0 - > i.e A109 = 0 whilst 109A ...

Another credit card payee problem
I am having a problem for about a month or two now where downloaded credit card transactions will have the wrong payee in them. This is not the result of mismatching with the wrong transaction. These transactions don't get matched at all. So far it has only happened to transactions that I have not entered manually so there is no match. I recognize the payee but I know I didn't charge anything there so I go to the on line account info center (it's a Discover card) and see that there is a transaction for that amount on that date but the payee is somebody else. Anybody else have or ha...

Changing COSTS in Supplier tab
I changed cost all pricing in headquarters and successfully updated costs in store manager. Is there a way to update those changes for the SUPPLIER tab? Those did not reflect the new changes. ...