automatically select value from a calculated field

[jimmy0305]

Hello to everyone...

I have a calculated(unbound) text field named(Age) on my main form. The Age is calculated based on patient's DOB(Date of Birth) Field using datediff function, and in the subform, I have a field named (AgeNo).

There is a criteria for AgeNo Field depending on (Age) field for each patient.

Criteria:

If Age is 18-40 = AgeNo should be 0
If Age is 41-60 = AgeNo should be 1
If Age is 61-70 = AgeNo should be 2
If Age is >71 = AgeNo should be 3

MRN (Med. Rec. No) field & Age are in Main form and AgeNo field is in a subform.
I want the AgeNo field to automatically select ( 0, 1, 2 or 3 ) based on the calculated unbound text field(Age).


Many thanks...

 

[MStef]

Why you put "AgeNo" in a subform,
put it in a MAIN FORM too.

 

[Meltdown]

something like this should work...try in in the AfterUpdate event of the Age control or maybe the Current event of the form

varage = Me!Age

Select Case varage
Case 18 To 40
Me!NameofYourSubform.Form!AgeNo = 0
Case 41 To 60
Me!NameofYourSubform.Form!AgeNo = 1
Case 61 To 70
Me!NameofYourSubform.Form!AgeNo = 2
Case Is >= 71
Me!NameofYourSubform.Form!AgeNo = 3
End Select

 

[jimmy0305]

Actually I tried that but it didn't work... For some reason, the Dlookup function can't see or recognize the Age field...here's the code...

Private Sub Form_Current()
Dim bytAge As Byte

bytAge = Nz(DLookup("[Age]", "tblPatient_Demographics", "[MRN] = '" & Me.MRN & "'"), 0)

Select Case bytAge
Case 18 To 40
Me.AgeNo = 0
Case 41 To 60
Me.AgeNo = 1
Case 61 To 70
Me.AgeNo = 2
Case 71 To 150
Me.AgeNo = 3
End Select

End Sub

And Somebody suggested to use public function but i can't figure out how to use it... here's the code...

Public Function AgeNo(dteDOB As Date) As Integer
Dim intAge As Integer

intAge = DateDiff("yyyy", [DOB], Date) + (Date < DateSerial(Year(Date), Month([DOB]), Day([DOB])))

Select Case intAge
Case 18 To 40
AgeNo = 0
Case 41 To 60
AgeNo = 1
Case 61 To 70
AgeNo = 2
Case Is > 71
AgeNo = 3
End Select

End Function

I'm still a newbie and i'm trying to learn as much as i could....