Hi all,

I have an Access 2010 Table which contains about 20 fields, mostly text data type. One of the fields has the heading "Status", and the default value for this is "Open". There is another heading called "Issue Closer" which by default is blank. What I would like is when somebody enters data into the "Issue Closer" Field it automatically changes the "Status" field from "Open" to "Closed"

The data entered into "Issue Closer" will be a name but it doesnt have to be specific, e.g. if "Issue closer" is null then "Status" = "Open" and if "Issue" closer has a text string in it then "Status" = "Closed"

Can anyone advise how I should go about this, or even if it's possible? I read a bit about calculated fields but that seemed more to do with adding up values and performing mathmatical functions.

how are your users updated that field? from within a form ?

If the Field1 value is entirely dependent on the Field2 value, Field1 is redundant. A calculated field in a query can provide the necessary Open or Closed value.

A difficulty with one field's value depending on another field's value is that it is difficult to maintain, especially if you import data or otherwise enter data by means other than the user interface (e.g. an Update or Insert query).

Bob already asked about another important point.

Sort of, the database is split and users are entering data via a FE form, thats how the record is started, and but the "Issue Closer" field is entered via a query running within another seperate form and it is only used by administrators such as myself.

The "Status" value is entirely dependent upon the "Issue Closer" value.

I will need to import data from Excel to begin with but once it's running I will not need to import further data.

"Status" value is a default value, nobody will be entering data into this value from a UI and I can automate it they wont need to do it from a query either.

Then "Status" needs to be a calculated field in a query (or it could be an epression in a control, but the query option is probably the most efficient).


It should not be in the table at all, based on what you have described.

Use a query for this but if your a stickler for data inserted (though not recommended since what you describe is best as a calculated query) use an insert statement with a where clause based on the record in tblA

The trouble with writing to a field whose value is entirely dependent on another field is not so much the initial data insertion as when the value of the "parent" field is changed over the lifetime of the record. Every possible scenario must be accounted for, to no overall advantage.

Sounds a lot simpler to leave it alone and hope people remember to change it manually.

I would have thought it would be a simple thing to put in a table, if text appears in this box then then change the value of the other one.

I dont understand quite the relationship between query and table? The data I'm writing is stored in a table, and a query is like a search?, you run a query like you run a search and it displays the data that your looking for. The status field (Open or Closed) is part of the data I want to store so it should be in the table shouldnt it, but you say it should be in a query, which is a kinda search tool? how can I save data to a search?

One of the critera for my query is the Open/Closed status field, I run a query to show all the records with status set to Open, and another one for all those with status set to closed, so the Open/Close data should be in the table in order to be able to run the query?

I'm a bit lost I'm afraid.

I think you could use this syntax in a form, using the control AfterUpdate event:

Sounds worrisome to me.


It's a simple enough thing to put in a form, but a table should be for storage and some related business such as requiring data in a field and other validation tasks. But the point several of us have been trying to make is why you would want to store data that is entirely dependent on another field. If you store date of birth in a table you can either calculate a person's age, or store their age and manually update the records from time to time. I expect you would opt for the former. This is no different.


A table is for storage. A query is a view of the data, and can also be used to perform calculations. For instance, perhaps in a People table you store FirstName and LastName in separate fields. There is no need also to store FullName. In a query you can add a field that is not in the table:

FullName: [FirstName] & " " & [LastName]

You could also do:

LastFirst: [LastName] & ", " & [FirstName]

If there is a MiddleInitial field you can add that, or not, as needed. You have a lot of flexibility. Another advantage is that you can sort data by LastName, then FirstName, while displaying FullName or LastFirst. This can be useful when selecting names from a drop-down box, for instance.

A query can also be used to limit the records. Perhaps you want to see just the records for the past month or the past year (assuming there is a date field in the records). Criteria can be combined, so that you find all records

Or you can combine records from several tables. This is especially helpful for a report, where users can only view the data. Some queries, because of the nature of how tables are joined or records are filtered, do not permit data entry, so queries used as the basis for interactive forms must take this into account.

In any case, just as you can base a form or report on a table, you can base it on a query that is based on the table. Unlike a table, the query can be used to limit the number of records according to criteria of your choice, sort records, calculate dates, designate Status as Open or Closed, or whatever you need.

well i guess the problem you're facing right now is you don't have enough knowledge with ms access. but that's what we are here for to guide to properly. what you really like to do is set a query for it and have that query be the record source for the table. basically it's saying like this

if tbla field 1 is 5 then tblb field 2 is 6. the 6 in part would better be a calculated field but if you have the need to store it then you'd need to go into vba for that.

It is worrisome to leave it and hope people remember.

So in my table the "Status" field is "open" by default and, at the moment, manually changed to "Closed" when data is entered into the "Closer" field. What you suggest is that in a query I can calculate the "Status" (age) field based upon the "Closer" (date of birth) field. So if there's data in the "Closer" field then "Status" would be calculated as "Closed"

My project is a technical Issue reporting and tracking system, the idea is for it to work like this

1)User fills out a form to raise a new issue, the form generates a ref number, emails the details to the tech support team, and enters the issue details into the table, at this point the issue is considered "Open"

2)Somebody from tech support team (5 people) deals with the issue

3)Once the issue is dealt with we close it off. Currently there are two saved queries, one of which performs a query for records with an "Open" status, tech support member then manually edits that record to add in the resolution details and the "Closer" field (Then remembering to change the status from open to closed)


Presumably instead of running a query for records with an "Open" status I could run one for records with no data in the "Closer" field (since it's the same thing) and then calculate the "Status" field.

I think I'm getting it, so your right I wouldnt actually need a status field at all in my table since the ony way we view the table is via a query.

Exactly!


You've got it.


No status field. Just use the calculated field as the control source for a text box in a form or report.

One thing about which you need to take a little care is how you define no data. Null ("unknown", essentially) is not the same as a zero-length string (""), so to cover all contingencies you could check the length of the field:

Status: IIf(Len([Closer] & "") = 0,"Open","Closed")

By combining (concatenating) the [Closer] field value with a zero-length string, you will get the correct result whether [Closer] is null or a zero-length string (you can't tell just by looking, since it is blank in either case).

I have shown this as a calculated query field. Bind your form or report to the query, and select [Status] as the Control Source for a text box on a form or report.

Ok thanks for the info, it does seem a better way of doing it. Will have a try at your suggestion.

Useful tip about defining null data, I have one or two statements in my project regarding null value, will have to double check they are checking the string length too.

It's not always necessary to do this, but it is a good idea when you are uncertain about the nature of a blank field, or when a zero-length string (ZLS) is possible. For instance, a text field in an Access table has an "Allow Zero Length" property. I tend to set that to No unless there is a (rare) specific reason to allow a ZLS. Also, some functions (Format, for instance) return text, which in some cases can lead to unexpected results if you are checking for Null.

It is also worth noting that 0 is neither Null nor a ZLS.

Another tool for handling Null is the Nz function. If the value passed to the function is Null, Nz returns an alternative value.

Nz([YourField],"") returns a ZLS if [YourField] is null. So you could have:



Both Nz and testing for length have their place. I'm afraid I can't come up with a specific instance of when to use one or the other, although testing for Len is probably the preferred choice when testing several values at once.

Hope I haven't muddied the waters. Here's some more information about Null, which is a very useful but somewhat tricky concept:
http://allenbrowne.com/vba-NothingEmpty.html

That works a treat thank you

I would like some advice on how to modify the expression though for another query on a different table.

It has a "Status" field just the same, and it has a "Closer" field just the same, however it does not have simple Open/Closed states, for the Closed state I have modified the expression as follows:

Status: IIf(Len([Closer] & "")=0,"Open","Goods Returned / Job Completed")

This works fine for closed issues, if there is some data in the "Closer" field it calculates the "Status" field as "Goods Returned / Job Completed" not a problem, the "Closer" field is only ever populated when the issue is in a "Goods Returned / Job Completed" state.

For an Open state however it's a bit trickier, there are 3 open states lets say "Requirment Identified", "Request Submitted", "Approval Notification Recieved"

The user enters the status into the table via a form, they might initially enter Requirment Identified, but as the issue progresses it might change to "Request Submitted" or "Approval Notification". This change would currently be achieved by manually editing the Table via a query.

It would be great if it could be automated the same way. I've been trying to think what these status's relate to and depend upon:-

"Requirment Identified" status requires data to be entered into "Summary" field
"Request Submitted" status requires data to be entered in "Ref No."
"Approval Notification Recieved" status requires data to be entered into either "PO No." or "Date Approved" field

It's also possible for data to exist in all 3 of those fields simultaneously, but they definitly have an order of precedence, i.e. "Requirment Identified" Status could be overruled by "Request Submitted" status if data is entered into "Ref No." field.

Sorry it's so complicated, If it was just one of those things I could modify the expression, but I'm not sure how to modify it for multiple things or even if it's possible. If I can get it to work I can dispense with the user entering the status at all.

Is there a single field that contains one of these three phrases? If so, what is the field name, and are there any other possible values for that field?

The only field that contains these phrases is the "Status" field

The only other possible value for that field is "Goods Returned / Job Completed" which is only ever displayed when "Closer" has data entered into it.

So "Status" field can have 3 phrases for an open state, and 1 phrase for a closed state, but it actually uses the phrases as a status rather than simply open closed.

It sounds like this is a case where Status is not entirely dependent on other fields. That is, the Status is a value in its own right rather than a value derived from other values. Is that correct?

Status "Closed" or "Goods Returned / Job Completed" in this case, is entirely dependent upon the "Closer" field, same as before.

Status "Open" or one of the 3 phrases in this case, is a bit more of a grey area.

"Request Submitted" will only ever be a set as "Status" if data is entered into "Ref No." Field. So you could say that it is dependent upon that field.

"Approval Notification recieved" will only ever be set as status if there is data in "PO No." or "Date Approved" field. So you could say that this is also dependant upon those fields.

"Requirement identified" Will only be set as "Status" if there is data in "Summary" field, however if there is also data in the "Ref No.", "PO No." or "Date Approved" field then "Request Submitted" or "Approval Notification recieved" will be set as the Status. This is the complicated part I think.

I'm not sure if it would happen that there is a "Ref No." and a "PO No." together. Need to account for that possibility though.

well on reflection you probably DO want a status field. most systems have a status field of some sort.

your problem is in defining which events cause the status to change. I would have thought the best way might be to select the status, and then determine whether the other fields are appropriate for that status.

This is why you cannot do stuff like this directly in tables or queries.

You need a form, and you need to use some code to test all the values before accepting the entry.


I assume the users are manually entering data to manage these jobs. If so, let them pick the status, and you use some code to check the status. You can also ensure that the status does not change to some "daft" value, that could not make sense.



-------
hsaving said all this- instead of entering the status as text, it is much more usual to have a table of status codes, each with linked text (a lookup table). you just store the status code (generally numeric, but could be alpha) and the system looks up the text assocaited with that code. Then you can change the text descriptions without any issues being caused.

I agree with Dave that a Status field seems to make sense in this case. The alternative would probably be a user-defined function that accounts for all possibilities (an expression in a query would be awkwardly complex, with several layers of nested IIf statements). It may be a bit of a gray area to store a value when it is possible to calculate the same value, but it is also possible that the business rules will be modified some day, which could mean rewriting chunks of code.

As for testing the values as Dave suggested, the form's Before Update event is often the place to do that. First, make the Status field in the main table a Number field. Set up a table of status text with corresponding numbers. I will assume the following:



Create a query containing the two fields (sorted by StatusText, perhaps), and use the query as the Row Source of a combo box on the form. Set the combo box Column Count to 2, Column Widths something like 0";1.5", and Bound Column 1. I will call it cboStatus.



In the above, txtCloser is the text box bound to the Closer field, etc.

This only looks at two possible values in the Status field, but it is an example of one possible approach. You could approach it from the other direction: look at the values in Closer, RefNo, etc., and see if Status matches. That would probably be a series of If statements rather than Select Case. How you proceed depends on what would work best for your situation.

There are other things you may be able to do, such as use the After Update event of, say, txtCloser to set cboStatus to 1, but that could get complex with conditional requirements (for instance, if one of two fields has a value but a third field does not, do one thing; but if the third field has a value, do another thing; if none of the three has a value, do yet another thing).

Thanks for the info guys, I'll look into giving it a go and get back to you. Learning all this as I go along so it takes me a while. I'll try and see if I can simplify the Status field a bit too.

Still struggling with this one a bit, if I'm having a Status field in the table then "Requirement Identified" and "Request Submitted" would be entered from a form using a combo box. Thats not a problem.

Once the initial data has been entered the form moves on ready for the next record. At some point at a later date somebody will close the issue or an approval will be received, the record then needs to be edited/updated with the new information, I dont see how you can do that from a form. The only way I found to do that was running a query within a form and manually editing the appropriate record in the query. "Closer" and "Approval Recieved" will only be entered in this way.

Trouble is you can edit the record in the query and then exit without updating the status to match. Quite happy to have a msgbox popup and work like Bruce suggested, it checks values in Closer, RefNo, etc., and see if Status matches, then pops up a message if it doesnt. Will this work though if the code is in a form can it check fields in a query which is running within the form?

Why not? Does the form not allow you to edit an existing record, or what exactly?


If the form is based on a query, the form's Before Update event can check values in the current record, which means it is checking values in the table on which the query is based. Is there another query that is "running within the form"?

I didnt think you could edit an existing record using a form.

I created a blank form and drag/dropped the query into it, does that mean the form is based on the query?

There is no other query running within the form. So I could use a vba code in the forms Before Update Event and it would "see" the fields and values in the query running within the form. I didnt know Access could do that (there's a lot I dont know)

Ok I think my Query is a Subform/Object, in design view if I click on the Query running within my Form, in the property sheet it says Selection type: Subform/Subreport, and the Name is "Open Issues"

I've just been experimenting with something simple like, make sure "Closer" is not empty before exiting. I created an "On Exit" procedure and entered the following vba

Private Sub Open_Issues_Exit(Cancel As Integer)
If Len(Me.Closer & vbNullString) = 0 Then
MsgBox "You need to select Closer"
Cancel = True
Me.Closer.SetFocus
End If
End Sub

The Form opens fine and the Query within it runs fine, and upon exiting the form it tries to run the code but throws an error "Run-time error '2465': Microsoft Access can't find the field 'I1' referred to in your expression." When I click Debug it shows the following code highlighted Yellow "If Len(Me.[Closer] & vbNullString) = 0 Then"

After a bit of Googling I suspect I'm perhaps not referencing the subform properly? maybe the code I entered is looking for the "Closer" field in the form rather than the subform?

To the contrary, that is what forms are for: to create and edit records. The limitations are the form's properties (which can be set not to allow edits or additions) and the query that is the form's Record Source, which needs to be of a type that allows changes. A basic (Select) query based on a table will allow edits.


I never tried that. Maybe it does something I don't know about in Access 2010. The way to base a form on a query is to set the form's Record Source to the name of the query, same as you would a table.


I'm going to guess that dropping a query into the form created a subform by way of a wizard, but I don't know. If so, the main form's Before Update event only acts upon the main form's fields and controls. The subform record is saved as soon as you exit the subform, just as the main form's record is saved (and the main form's Before Update event runs) as soon as you enter a subform or otherwise leave the main form.


It will see the fields in the query or table that is the main form's Record source, and it will see the properties of any controls on the main form. It can see fields and controls on subforms, but it can't perform validation of subform records, which as I mentioned are already saved when you go back to the main form.

I suggest you do some reading on how Access works. You seem to have morphed a few things together into a bit of a general misunderstanding. In the Tutorials section of UtterAccess Discussion Forums are a number of articles. Crystal's is a good place to start:
http://www.UtterAccess.com/forum/Access-Ba...d-t1595005.html


I expect your speculation is correct: that you are trying to reference a field on a form or subform other than the one where the code is located.


If you cancel the Exit from Open_Issues, you will not be able to leave the control, which means you can't set the focus elsewhere. If you want to perform validation of this sort (at the control rather than the form) you would do better to use the control's After Update event, something like:


As mentioned, Closer needs to be on the same form (not a subform) as Open_Issues. It is possible to reference a control on another form, but it doesn't sound like that would be helpful in this situation.

For more about referencing subform controls:
http://access.mvps.org/access/forms/frm0031.htm

Thanks Bruce for the info, I will have to get and do some background reading, those links were useful.

Your comment about Forms being intended for creating and editing records has given me an idea. I need all the open records to be displayed as I currently have it (a query within a form) if I lock the Query so nobody can directly edit it and then on the main form underneath the query box I put text or combo boxes for the fields which need to be edited.. that gives me a whole lot more control and would be easier to implement, rather than trying to control/check fields in the query itself. Probably the way it's intended to be done, and how I should have done it in the first place.

It sounds as if you need something like a detail view of individual records, while at the same time looking at a listing of all records. Maybe Access 2010 automates this in some way. In any case, the tutorials will no doubt give you some ideas of what will work best for your sitation.


Forms are for entering and editing records. That's definitely how it was designed.