?
Solved

Using a function to assign a default value for a field in table definition

Posted on 2011-05-10
10
Medium Priority
?
288 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:shambalad
  • 5
  • 3
  • 2
10 Comments
 
LVL 75
ID: 35730921
I suggest not using the hard code Default value properties at the table or form level. Instead, at the Form level ... use the BeforeInsert event to set 'initial values' (aka default values), like so:

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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 35730947
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=GetSessionID()
0
 
LVL 7

Author Comment

by:shambalad
ID: 35731093
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'.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 800 total points
ID: 35731123
your guess is right.
0
 
LVL 75
ID: 35731130
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
0
 
LVL 75
ID: 35731156
"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


0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1200 total points
ID: 35731162
Showing in Bold:


INSERT INTO Table2 ( FIELD1, FIELD2, FIELD3, FIELD4 )
SELECT Table1.FIELD1, Table1.FIELD2, Table1.FIELD3, GetSessionID() AS FIELD 4 FROM Table1;
0
 
LVL 7

Author Comment

by:shambalad
ID: 35732332
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
0
 
LVL 7

Author Closing Comment

by:shambalad
ID: 35732346
I always appreciate your help. Thanks again.
0
 
LVL 75
ID: 35732373
You are welcome ...

mx
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question