Help with a simple Update Field problem

[SimonForrister]

I've created a database application that allows an organisation to keep track of maps that it issues for its agents to work from. In particular, I have a Form called CONTROL based on a table called CONTROL and on this form is a Subform called ADMIN Subform based on a table called ADMIN which simply has 7 fields. The ID field; the field that links this table to the CONTROL table; a field called ADMIN STATUS (This field has a default value of 1 - indicating that the Map is presently in my sticky little hands);The other fields are viewable fields in the subform and are DATE ISSUED; DUE DATE; ASSIGNED; & RETURNED DATE.

So, each time a Map is issued to an agent, I enter the date in the DATE ISSUED field - thereafter moving to the next field, during which, the DUE DATE automatically gets populated with a value 50 days from the DATE ISSUED, indicating when the Map should be returned (bit like a library would do).

The ADMIN STATUS also gets changed at this point too, to a value of 0 - meaning that the map is loaned out.

The ASSIGNED field can then be populated with the persons name, via a drop down box.


QUESTION:

When the Map is returned, how do I tell Access (2007) that if the DATE RETURNED is actually filled in with a date, then change the ADMIN STATUS back to the value "1", but if not, keep the value at "0", and indeed, if I were to make a mistake, and need to delete the DATE RETURNED I may have wrongly entered for that map, then the ADMIN STATUS would need to return to "0" again?

I'm sure this is a VB job, but I am a complete novice at that, and though I have had a try, I either get all records reverting back to ADMIN STATUS = 1 or I get referred back to the code part which says "Private Sub Returned_Date_AfterUpdate()" (which is highlighted in yellow) and Else: is also selected.

This is what I thought might work:

Private Sub Returned_Date_AfterUpdate()
If [Returned_Date] Is Not Null Then [Admin Status] = 1

Else: [Admin Status] = 0


End Sub

Hope this makes sense, and look forward to anyone that can help

Thanks

Simon

 

[DCrake]

In Theory you have supplied your own answer, actually you do not need an admin status field this can be derived from fact that the field has a date in it. Test for null date = 0 else 1

 

[boblarson]

You shouldn't be updating a field like this to reflect status. You should be relying on DateIssued and DateReturned to DISPLAY the status but not try to store it.

On a control in your form you can use

=IIf(Not IsNull([DateAssigned]) And IsNull([DateReturned]), 0,1)

 

[boblarson]

I was slow on that one