Subform field default value

Question

Hello,

I have a form called "frm_List_Items_Details" and on this form I have a subform.

In the datasheet subform I have field called "Asset Location". When I create a new record in the subform I would like the "Asset Location" to have a default value derived from fields in the main form. I typed up the below expression in the "Asset Location" default value but when I change the view to Form View, in the subform, Asset Location field, I have a little error message "#Type!". How can I fix this so that the below fields are concatenated as the default?

=[Forms]![frm_List_Items_Details]![Location Sum ID] & " -- " & [Forms]![frm_List_Items_Details]![Location User] & " -- " & [Forms]![frm_List_Items_Details]![Division] & " -- " & [Forms]![frm_List_Items_Details]![General Location Desc] & " -- " & [Forms]![frm_List_Items_Details]![Location Description] & " DNS " & [Forms]![frm_List_Items_Details]![DNS Name] & " SN " & [Forms]![frm_List_Items_Details]![Serial Number]

Your help is greatly appreciated.

Iram

Answer 1

Do you have some good reason to store this field in a table, as opposed to just displaying it for the user? It would appear to violate two basic relational principles: that fields should be "atomic", storing only one piece of information (you're concatenating six), and - even more important - that data should not be stored redundantly! If this combined "location" can always be derived from the six fields in the main form's table, then it should simply not exist, since you can always generate it as needed.

If, on the other hand, it's a true default - you will often or usually store that string, but allow the user to edit it at will - you may want to use VBA in the Subform's BeforeInsert event to generate it. That would still be questionable in my opinion (not atomic and partially or totally redundant)!

Answer 2

The table field data type is "Text"

Then I don't immediately see why your DefaultValue expression isn't working.  A very similar setup works for me in a simple test.  Note that you can simplify your expression by replacing "[Forms]![frm_List_Items_Details]" with "[Parent]", since that property is a reference to the subform's parent form.  That would give you this:

=[Parent]![Location Sum ID] & " -- " & [Parent]![Location User] & " -- " & [Parent]![Division] & " -- " & [Parent]![General Location Desc] & " -- " & [Parent]![Location Description] & " DNS " & [Parent]![DNS Name] & " SN " & [Parent]![Serial Number]

However, if that's not working, it's not clear to me why not.  You could certainly do as John suggested and use the subform's BeforeInsert event, if the concatenated string is not supposed to be changeable by the user.  If the user should be able to change it, you could use the subform's Dirty event instead, and only set the value of [Asset Location] if the subform's NewRecord property is True.

Answer 3

Hi John,

Actually the reason why I am doing this is to keep historic records of what the data was on particular dates.

So when I add a new record and the default value concatenates some of the most important fields in the main form, all I am doing is tracking over time how any of the data is changing.

I tried the VBA method you recommended and it worked great!

Thanks.

Iram

Answer 4

Hi,

The table field data type is "Text"

Answer 5

What is the data type of the Asset Location field?