how do I get access to automatically update field on a form

[Samc]

This may be abit basic, I am a self taught Access user. I have a form in my database to add soldiers details and they are assigned a cohort number depending on when they enlisted. For example if they enlisted between 31/07/03 and 01/02/04 then they are in cohort 1, if they enlisted between 01/02/04 and 01/08/04 then they are cohort 2 and so on.

I want the Cohort number field to automatically upate when I enter their enlistment date (ie if they enlisted on 02/10/03 then the cohort no will automatically update to 1).

please help

[sirdevo]

Put VBA If -Then-Else Block in the BeforeUpdate event of the textbox.

Private Sub YourTextBox_BeforeUpdate(Cancel As Integer)
If DateValue("11/25/2007") = DateValue(txtReportDate) Then
cohort = 1
ElseIf DateValue("01/30/2009") < DateValue(txtReportDate) Then
cohort = 2
Else
cohort = 3
End If
End Sub

Hope that helps,
Devo

[Megalog]

My suggestion is to create a table that holds your date ranges and the corresponding Cohort number, like such:

Table Name: tblDates

FieldName: ID
Type: AutoID

FieldName: StartDate
Type: Date

FieldName: EndDate
Type: Date

FieldName: Cohort
Type: Text

Then, on your form simply use this line of code in your AfterUpdate or LostFocus event for the Enlist Date field:

If IsDate(me.EnlistDate.value) Then
Me.txtCohort.value = DLookup("[Cohort]", "tblDates", "#" & me.EnlistDate.value & "#  BETWEEN [StartDate] AND [EndDate]") & ""
End If

If it doesnt find a value, it will simply leave the cohort field blank.

[Samc]

Thanks for your help. I have created the table and put in the code as you have suggested but when i enter the date into the form i get this message

Compile error: Method data member not found

and the txtCohort bit in the code is highlighted. Could it be a problem that the the field in the newly created tbl is called Cohort and the field in the form is also called Cohort? how do I proceed?

[Megalog]

I would rename the field you have on the form to 'txtCohort', and update any other code you have that may be referencing it. Or you could change the 'me.txtCohort.value' section of my code to 'me.Cohort.value', to match what you already have in place.

Either way should work.

[Samc]

Thanks I did manage to sort that actually and it does work now so thank you. I do have another small problem tho. Its not giving me the correct value in the cohort field. For example in the table as follows (shortened version)

Start Date End Date Cohort
01/01/1900 31/07/03 Pre
01/08/03 31/01/04 1
01/02/04 31/07/04 2

If I add 02/08/03 it answers Pre, 03/10/03 also answers Pre (both should be 1), while 02/08/04 comes out at 2 (it should be 3). The dates in the table are correct so could it be a formatting issue. I really have no idea how to correct it.