Update two fields from one data entry in a Form

[KavJack]

I have a Form where one field in the Table is Transaction_Date which is entered by the operator. I also have another field in the same Table which just contains the TYear.
This field is not needed to be in the Form.
I want the TYear to be automatically updated after the Transaction Date has been entered.
Tyear = Year(Transaction_Date)
I realise that many people will feel that this is redundant data but having a separate field for TYear helps me visually see the records that are needed for, say, a Tax Year. Anyway despite the feelings that you have about this design can anyone help me by providing me with a solution to this problem.
I am using Windows Office XP.

 

[dhookom]

You could use code in the after update event of the transaction date text box to update the TYear text box.
If IsDate(Me.txtTransactionDate) Then
Me.txtTYear = Year(Me.txtTransactionDate)
ENd If

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KavJack]

I get error Type mismatch when I run this .

What is wrong ?

 

[dhookom]

Is your transaction year field Text or Numeric?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KavJack]

My Year field is numeric (double integer)

Option Compare Database
Dim NYear As String
Dim INYear As Integer
Dim XNyear As Variant


Private Sub Date_of_Expense_AfterUpdate()

'XNyear = Year(Me.[Date_of_Expense])

'INYear = NYear
'Me.Year = Year(Me.[Date_of_Expense])


If IsDate(Me.[Date_of_Expense]) Then
Me.txtYear = Year(Me.[Date_of_Expense])
End If
End Sub

 

[dhookom]

KavJack,
Does your current code still cause an error and if so, what is the error and what line causes the error? Did you rename both of your controls?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[KavJack]

I had to redo the form starting with the wizard. Trying to edit my old form wouldn't work. Now it's working. Thanks.
Now my next questions is :
What if the Year Filed is NOT on my Form but I still want to update it as above ?
There now is no Me.txtYear. "Me." implies that the field is in the Form, n'est-ce pas ?

 

[dhookom]

Try the same code but change it to
If IsDate(Me.[Date_of_Expense]) Then
Me.YearField = Year(Me.[Date_of_Expense])
End If

If that doesn't work, you may need to add the year field to the form and make it invisible.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.