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

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

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)

I was slow on that one :D