Calculated field prevents new record

[Chrtalgo]

Hi! I have this code in Private Sub Form_current():

    If IsNull(Me.BirthDate) Then        Dim noYears As String
        noYears = "-"
        Me.Age.Value = noYears
    Else
        Dim intYears As Integer
        intYears = Year(Now) - Year(Me.BirthDate)
    
        If DateSerial(Year(Now), Month(Me.BirthDate), Day(Me.BirthDate)) > Now Then
            intYears = intYears - 1
        End If
        Me.Age.Value = intYears
        
    End If

This is to calculate the current age of the records. However, this seems to restrict me from using DoCmd.GoToRecord , "", acNewRec

When I click a button to create a new record I get Run-time error '2424': The expression you entered has a field, control, or property that Membersarchive can't find.

When debugging it highlights "intYears = Year(Now) - Year(Me.BirthDate)" in the code.


I don't get this as the first IF code should bypass the calculation and set the age to "-"? This works if I delete the birthdate from existing record, but it won't allow me to create a rew record.

me.age is not pulled from a query or table, it is calculated within the form itself.


Any ideas?

[Minty]

Try renaming your text box to txtBirthDate to make sure you are referencing the control not the field.

[Micron]

Looks like you are storing a calculated age, which is something you should not do anyway.

[Missinglinq]

First of, you really need to place all DIM statements at the top of your event.

That said, what Datatype is the Age control tied to?

First you have

Me.Age.Value = noYears

where noYears is defined as a String. Then you have

Me.Age.Value = intYears

where intYears is defined as an Integer! The obvious assumption would be that Age is defined as an Integer, in which case

noYears = "-"
Me.Age.Value = noYears

is going to pop an error, possibly the one you're getting! The Access Gnomes aren't always accurate/clear with their error messages.

Linq ;0)>

[SierraJuliet]

Just some ideas.

Dim noDOB As Integer

Dim tAge As Integer

Dim tYears As Integer

'show a zero for null birth date value

noDOB = 0

'age of person in years old

tAge = (Year(Now) - Year(Me![txtBirthDate]))

'excludes current year from years old; however, this will result in an inaccurate years of age.

tYears = (tAge - 1)

If IsNull(Me![txtBirthDate]) Then

Me![txtAge] = noDOB

Else

'Not sure why this next line needs to be here. Simply append the tYears to [txtAge] if [txtBirthDate] is not null

If DateSerial(Year([txtBirthDate]), Month([txtBirthDate]), Day([txtBirthDate])) < Now Then

Me![txtAge] = tYears

End If

End If

[SierraJuliet]

Now this is what I would do if you are doing what I think you are. You can change As Integer to As String for both noDOB and tAge if you really want the "-" but ensure Me![txtAge] field is set to short text instead of number. They have to be the same data type or you will receive a mismatch error.

Dim noDOB As Integer

Dim tAge As Integer

noDOB = 0

tAge = (Year(Now) - Year(Me![txtBirthDate]))

If IsNull(Me![txtBirthDate]) Then

Me![txtAge] = noDOB

Else

Me![txtAge] = tAge

End If

End If