Table default value

[Steve_in_Kent]

Can i have a table, look up a value for a record, from the same table as its entered?

ie: i enter records into the table,6 of them... the 7th, is set to a numberfield. and in the default box.. i put..

Code: Select all

=DatePart ("ww", mydatefieldinthistable)

However, it comes up with an error message, saying that it doesn't know that field.

I know i can do this in a query easily enough. however, having it in the table is just easier on me. The purpose is to have the week number in the table, from the rows date field.

[HansV]

The Default Value property is only used for new records, and the value is filled in BEFORE the user starts entering data, so it won't do what you want.

Strictly speaking, you shouldn't store the week number in the table, since it is derived information, and hence redundant. But if you really want to do it, you can use the After Update event of the text box bound to the date field on the form used to enter the data:

Code: Select all

Private Sub mydatefieldinthistable_AfterUpdate()
  If IsNull(Me.mydatefieldinthistable) Then
    Me.weeknumberfield = Null
  Else
    Me.weeknumberfield = DatePart("ww", mydatefieldinthistable)
  End If
End Sub

[Steve_in_Kent]

hmm yea, my instinct was telling me i was being a bad lad for storing data that i could work out on the fly easily enough..

However, i know it will help me with queries later on, because the table is fairly fixed.

ok.. thanks Hans.. will take a re look !!