Sub Form Default Values

[PPCO]

I have a default value in a sub form with only one field. When the default
value is correct, the user doesn't change it. However, it appears that it
only gets recorded when the user actually enters something and doesn't pick
up the default value otherwise. Any way to change that so it picks up the
default value even if no change is made? Thanks

 

[Beetle]

Something has to happen for a new record to be created
from the subform. If there is only one field in the record source
of the subform (which seems a bit odd), and the user never
changes it, then no new record is created, unless you do it
via some other means (i.e. use the Click event of a command
button or something). The default value in and of itself won't
do anything.

 

[PPCO]

Oops, forgot that part...it does have a job number field that is linked to
the main form. It automatically fills in when the main form creates a new
record. Can't figure out why that wouldn't create a new record in the sub
form though...

Before a record can be saved an edit condition has to be initiated; the
form's Dirty property then becomes True. While you can set this property's
value to False in code you cannot set it to True. However, you can do so by
setting the value of the one and only field to itself in the form's Current
event procedure if the form is at a new record:

If Me.NewRecord Then
Me.[The FieldName] = Me.[TheFieldName]
End If

'Form' in this case means the form object which is the underlying source
object of the subform control in the main parent form.

Having said that, its difficult to envisage a situation in which a table
would have one column (field) in which the value in multiple rows can be the
same. For one thing this would mean the table has no 'candidate key', i.e. a
columns or columns which uniquely identify each row in the table. Adding an
autonumber column would allow the table to be given a primary key of course,
but that is really only fudging the issue. I'd be interested to hear what
this table represents.

Ken Sheridan
Stafford, England

I have a default value in a sub form with only one field. When the default
value is correct, the user doesn't change it. However, it appears that it
only gets recorded when the user actually enters something and doesn't pick
up the default value otherwise. Any way to change that so it picks up the
default value even if no change is made? Thanks

 

[PPCO]

So that works, however in my main form, I have fields that can't be null. As
soon as I enter a value in the main form, it starts giving multiple error
messages that index or primary key can't contain null values. I guess it's in
a way jumping to the sub-form and making the auto entry before I've completed
the main form's entries. Any work-around for this? Thanks!

The fact that you see the default value in the control does not mean that a
new record has been initiated. The DefaultValue property does not assign a
value to a control's underlying field per se, it merely sets the value which
will be assigned when a new record is initiated. This normally happens when
a user manually enters data in another field, but in the absence of that a
new record is not initiated, and consequently not saved, unless you
programmatically initiate a new record, which is what the code for the
subform's Current event procedure does.

In fact it doesn't matter which field's value you set to itself, so the code
could set either the job number or the other field's value to itself, but
unless you do one or the other the only way a record will be initiated is if
the user manually updates the value in the visible control.

Ken Sheridan
Stafford, England

Oops, forgot that part...it does have a job number field that is linked to
the main form. It automatically fills in when the main form creates a new
record. Can't figure out why that wouldn't create a new record in the sub
form though...

Before a record can be saved an edit condition has to be initiated; the
form's Dirty property then becomes True. While you can set this property's

[quoted text clipped - 25 lines]

up the default value otherwise. Any way to change that so it picks up the
default value even if no change is made? Thanks

 

[PPCO]

That did the trick! Thanks!

That will probably occur if you are entering a new record in the parent form
as the subform's Current event procedure will execute, but before a value has
been passed to the job number foreign key in the subform's underlying
recordset via the linking mechanism between the parent and subform.

You could cater for that by removing the code from the subform's Current
event procedure and putting code in the parent form's AfterInsert event
procedure to set the value of whatever control in the subform has the default
value to itself. The subform will at this stage be at a new record, so this
will Dirty the new record so that it is then saved. The code would be
something like this:

Dim frm as Form

Set frm = Me.YourSubformControl.Form

frm.[YourControl] = frm.[YourControl]

In the above YourSubformControl is the control in the parent form's Controls
collection which houses the subform, NOT the control on the subform.

However that will only handle situations when a new record is inserted in the
parent form, not when a new record is inserted in the subform where the
record in the parent form already exists. Also it automatically insert a row
into the subform's underlying recordset whether you want this or not. If
the latter is acceptable, then this might be a viable solution as it would
mean that for each parent record the subform would have one row automatically
inserted with the default value. The automatic insertion of further rows
with the same default value would violate the key of the subform's underlying
recordset, so even if it was possible to engineer this, it would result in a
data error.

Ken Sheridan
Stafford, England

So that works, however in my main form, I have fields that can't be null. As
soon as I enter a value in the main form, it starts giving multiple error
messages that index or primary key can't contain null values. I guess it's in
a way jumping to the sub-form and making the auto entry before I've completed
the main form's entries. Any work-around for this? Thanks!

The fact that you see the default value in the control does not mean that a
new record has been initiated. The DefaultValue property does not assign a

[quoted text clipped - 23 lines]

up the default value otherwise. Any way to change that so it picks up the
default value even if no change is made? Thanks