Can I immediately 'refresh' data in a textbox ? refrsh

[liamfitz]

Here's the difficulty. I have two text boxes ( one as a date type, the other just 'normal' ) These are on the main form, and 'related'. Here's how. As the main form loads, and one navigates through the recordset the form is based on, one sees in these 2 text boxes - D.O.B. and Age Range ( the latter being calculated, based on the former ). However, if for whatever reason, one needs to change that D.O.B., I'd like the corresponding Age Range to change ( if indeed it needs to ), and that change be reflected in the text box immediately. It does so, if I move a record back ( or forward ), and then return to the record in question, but doesn't allow it to 'refresh' the data there and then. I've tried Me.Requery ( knowing the value of the field in question has changed ) Me.txtAge_Range.Requery ( the name of the control ), and one or two other 'contrivances'. Does anyone know how to do this, or if it's possible ? Many thanks.
:banghead::banghead:

 

[vbaInet]

How are you calculating the Age Range?

 

[liamfitz]

Re: Can I immediately 'refresh' data in a textbox ?

Here's the code :

Private Sub txtDOB_AfterUpdate()
'use datediff to compare DOB to today's Date() then update Age_Range_ID in tblClients accordingly - Select Case 1-7
Dim a, b, c, d As Long
Dim dt As Date
dt = Me!txtDOB.Value
a = DateDiff("d", dt, Date)
b = DateDiff("yyyy", dt, Date) / 4
c = Int((a - b) / 365)
If c > 13 And c < 18 Then
    d = 1
ElseIf c > 17 And c < 25 Then
    d = 2
ElseIf c > 24 And c < 35 Then
    d = 3
ElseIf c > 34 And c < 45 Then
    d = 4
ElseIf c > 44 And c < 55 Then
    d = 5
ElseIf c > 54 And c < 65 Then
    d = 6
ElseIf c > 64 Then
    d = 7
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblPersonalInfo.Age_Range_ID " & _
                           "FROM tblPersonalInfo WHERE (((tblPersonalInfo.Client_ID)=" & Me!txtClient_ID.Value & "));", dbOpenDynaset)
If Not (rst.BOF And rst.EOF) Then
    With rst
        .Edit
        !Age_Range_ID = d
        .Update
    End With
End If
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Call repaint_Age
End Sub
Sub repaint_Age()
Dim age As String
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblAge_Ranges.Age_Range " & _
                           "FROM tblAge_Ranges " & _
                           "INNER JOIN tblPersonalInfo ON tblAge_Ranges.Age_Range_ID = tblPersonalInfo.Age_Range_ID " & _
                           "WHERE (((tblPersonalInfo.Client_ID)=" & Me!txtClient_ID.Value & "));")
With rst
    age = .Fields("Age_Range")
End With
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
Me.txtAge_Range.SetFocus
Me.txtAge_Range.Text = age
End Sub

As you may be able to glean from the code, I call a sub-routine from the last line of the After_Update() event. This could easily be included in the event procedure. It works perfectly well, up to but not including, the final instruction line i.e. 'Me.txtAge_Range.Text = age' ?

 

[vbaInet]

Here's what you do:

1. Move the function to a module and call it CalculateAgeRange and the function should take in one parameter (which would be the DOB)
2. In the Age Range textbox do this:

=CalculateAgeRange(txtDOB)

Make sure to write the name of the DOB textbox in the call to the function and not the field name. If you refer to the textbox it will update once you move away from the textbox. Whereas if you refer to the field it will update only when the record is saved.

 

[liamfitz]

Re: Can I immediately 'refresh' data in a textbox ?

Is this what you mean ?

Function CalculateAgeRange()
Dim a, b, c, d As Long
Dim dt As Date
dt = Me!txtDOB.Value
a = DateDiff("d", dt, Date)
b = DateDiff("yyyy", dt, Date) / 4
c = Int((a - b) / 365)
If c > 13 And c < 18 Then
    d = 1
ElseIf c > 17 And c < 25 Then
    d = 2
ElseIf c > 24 And c < 35 Then
    d = 3
ElseIf c > 34 And c < 45 Then
    d = 4
ElseIf c > 44 And c < 55 Then
    d = 5
ElseIf c > 54 And c < 65 Then
    d = 6
ElseIf c > 64 Then
    d = 7
End If
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT tblPersonalInfo.Age_Range_ID " & _
                           "FROM tblPersonalInfo WHERE (((tblPersonalInfo.Client_ID)=" & Me!txtClient_ID.Value & "));", dbOpenDynaset)
If Not (rst.BOF And rst.EOF) Then
    With rst
        .Edit
        !Age_Range_ID = d
        .Update
    End With
End If
rst.Close
db.Close
Set rst = Nothing
Set db = Nothing
End Sub

 

[vbaInet]

Not quite. The function must have a parameter and the parameter must be the DOB textbox. I mentioned this in my last post. That parameter is the driver.

 

[liamfitz]

Point taken. Will do. Thanks.

 

[RainLover]

If you are interested then this is a good method to calculate age in year.

Years Months and Days is a bit more complicated but I can give you that if you want.

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

There is a link in my signature where you can go to my Sky Drive and download a working sample.

 

[liamfitz]

Thank you. I'm going to use a calcuoated field, and use the expression builder to calculate it. I'll et you know how I get on.

 

[RainLover]

Out of interest, which formula are you going to use.