Using a function to assign a default value for a field in table definition
When I define a date type field i am able to set a default value using the functions Date() or Now(). Are these unique exceptions, or is there a way I could assign other functions to load a default field default value? I have a public function the retrieves a Session ID (i.e. 'Public GetSessionID() As Long). I use this value to identify who, what and when a record was modified. I'd like to assign a default value of 'GetSessionID()' to the field SessionID, but I get an 'Unknown Function' error when I try to save it. I suspect I could accomplish this in Access 2010 with the new macros that are available there, but I'm currently at Access 2007.
Is there some way I can accomplish this at the table definition level in Access 2007?
Is there some way I can accomplish this at the table definition level in Access 2007?
you can, assign the default value using a form.
the control in the form must be bound to that field,
you can then use, in the current event of the form an expression
me.txtSessionID=GetSession
the control in the form must be bound to that field,
you can then use, in the current event of the form an expression
me.txtSessionID=GetSession
ASKER
I'm aware of the VBA options. I was hoping for something that would just automatically take care of itself, especially in instances such as when I'm doing an SQL Append. I'm guessing the short answer is 'No'.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Well, as you have discovered ... you cannot call a User Defined Function from the Default Value property. However, you *can* ... call that UDF from a Query ... specifically an Append Query as you noted.
mx
mx
"I'd like to assign a default value of 'GetSessionID()' to the field SessionID,"
You *can* call that Function from a query. So, if that is what you are wanting, you are good to go. For example:
INSERT INTO Table2 ( FIELD1, FIELD2, FIELD3, FIELD4 )
SELECT Table1.FIELD1, Table1.FIELD2, Table1.FIELD3, GetSessionID() AS MyFld
FROM Table1;
mx
You *can* call that Function from a query. So, if that is what you are wanting, you are good to go. For example:
INSERT INTO Table2 ( FIELD1, FIELD2, FIELD3, FIELD4 )
SELECT Table1.FIELD1, Table1.FIELD2, Table1.FIELD3, GetSessionID() AS MyFld
FROM Table1;
mx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks guys. I've used UDFs in queries before; can be quite handy. I appreciate your taking the time to look at this question.
Todd
Todd
ASKER
I always appreciate your help. Thanks again.
You are welcome ...
mx
mx
Private Sub Form_BeforeInsert(Cancel As Integer).
' set your control values here
Me.txtSomeTextBox = <SomeValue>
End Sub
This gives you a lot more flexibility. Also, it keeps the New Record position on a Form looking 'clean' - no default values hanging in some controls, making it look like there is already a record. The BI event triggers when any Bound control is edited.
mx