Create subform default values based on value set in main form

[speaton via AccessMonster.com]

Hello,

I have a form into which summary data is entered, on which there is a
datasheet-style subform for entering related records. In many cases, one of
the fields in the main form makes a handy default value for many (but not all)
of the records in the subform, so I would like to have a field on the subform
take its value from a field on the main form when it acquires focus (assuming
that it doesn't already have a value).

It seems like something along the lines of the example below should do the
trick, but Access keeps telling me it cannot find the subform in question
(which is present in the database--I've checked!):

---

Private Sub SubformField_Enter()

Dim SubformValue as String
Dim MainFormValue as String

SubformValue = Forms! ' ... path to the subform field goes here
MainFormValue = Forms! ' ... path to the main field goes here

If IsNull(SubformValue) Then SubformField = MainFormValue

End Sub

---

Can anyone point out what might be causing the problem? Better still, can
anyone suggest a better way of implementing this idea?

With many thanks in advance,

speaton

 

[speaton via AccessMonster.com]

Hello, again.

No takers for this question?

As far as I can tell, the problem seems to stem from the fact that I am not
referring to the field in the subform correctly in VB. I've tried referring
to it like this:

Forms!frmMainForm![frmSubform Subform].Form.FieldName

This doesn't seem to be correct, but I haven't been able to get any
variations of this to work, either. Can anyone point me in the right
direction? How does one refer to a field on a subform in VB?

With many thanks for any advice,

speaton

 

[Benjamins via AccessMonster.com]

Hi,

If you are refering the subform field from the parent form:
[SubformName].Forms.Fieldname.Value

If you are refering the subform field from the subform itself:
Fieldname.Value

Hello, again.

No takers for this question?

As far as I can tell, the problem seems to stem from the fact that I am not
referring to the field in the subform correctly in VB. I've tried referring
to it like this:

Forms!frmMainForm![frmSubform Subform].Form.FieldName

This doesn't seem to be correct, but I haven't been able to get any
variations of this to work, either. Can anyone point me in the right
direction? How does one refer to a field on a subform in VB?

With many thanks for any advice,

speaton

 

[speaton via AccessMonster.com]

Thank you very much for the response, Benjamins!

If you are refering the subform field from the parent form:
[SubformName].Forms.Fieldname.Value

I tried this, and Access is showing me an error indicating that an object is
required. I've checked the name of the subform a number of times now, and it
is correct, so I'm wondering if there is something preventing Access from
recognizing that subform for some reason.

If you are refering the subform field from the subform itself:
Fieldname.Value

I also tried this, just to see if it would work, but it did nothing, so I'm
not sure what else to try at this point.

If there are any other suggestions, I'd be very glad to hear them!

 

[Benjamins via AccessMonster.com]

Hi,

My Mistake. It should be [SubformName].Form.Fieldname.Value. Have and extra
"s".

There is a thing i need to understand: Is the SubformField a textbox?

Thank you very much for the response, Benjamins!

If you are refering the subform field from the parent form:
[SubformName].Forms.Fieldname.Value

I tried this, and Access is showing me an error indicating that an object is
required. I've checked the name of the subform a number of times now, and it
is correct, so I'm wondering if there is something preventing Access from
recognizing that subform for some reason.

If you are refering the subform field from the subform itself:
Fieldname.Value

I also tried this, just to see if it would work, but it did nothing, so I'm
not sure what else to try at this point.

If there are any other suggestions, I'd be very glad to hear them!

 

[Benjamins via AccessMonster.com]

Hi,

Below is a code that i change base form your coding

Private Sub SubformField_Enter()
If IsNull(SubformField.Value) Then SubformField.Value= Me.Parent.
MainformField.Value
End Sub

Thank you very much for the response, Benjamins!

If you are refering the subform field from the parent form:
[SubformName].Forms.Fieldname.Value

I tried this, and Access is showing me an error indicating that an object is
required. I've checked the name of the subform a number of times now, and it
is correct, so I'm wondering if there is something preventing Access from
recognizing that subform for some reason.

If you are refering the subform field from the subform itself:
Fieldname.Value

I also tried this, just to see if it would work, but it did nothing, so I'm
not sure what else to try at this point.

If there are any other suggestions, I'd be very glad to hear them!

 

[BruceM via AccessMonster.com]

I would use Me.SubformField.Value, same as referencing the main form. The
Value property is default, so it is optional. That is, if it is left off the
Value will be used.

Be sure SubformField is not 0 (for a number field) or a zero-length string
(text). Neither of these are the same as Null, which essentially means
"unkonwn".

Another possibility, if the SubformField value is to be entered in new
records, is to use DefaultValue. In the subform's Current event:
Me.SubformField.DefaultValue = Me.Parent.MainFormField

DefaultValue applies only to new records, so it may not be what you need, but
it is sure to come in handy at some point if not here.

The subform control is the "box" on the main form containing the subform. If
you are referencing a subform field or control from the main form you need to
reference the Form property of the subform control:
Me.[SubformControlName].Form.[FieldName].

Note that the subform control may not have the same name as the subform
itself.

If the field and control names contain only alphanumeric characters and
underscores you do not need to use square brackets, but I used them here in
the hope it makes it easier to sort out.

Hi,

Below is a code that i change base form your coding

Private Sub SubformField_Enter()
If IsNull(SubformField.Value) Then SubformField.Value= Me.Parent.
MainformField.Value
End Sub

Thank you very much for the response, Benjamins!

[quoted text clipped - 13 lines]

If there are any other suggestions, I'd be very glad to hear them!

 

[BruceM via AccessMonster.com]

I would use Me.SubformField.Value, same as referencing the main form. The
Value property is default, so it is optional. That is, if it is left off the
Value will be used.

Be sure SubformField is not 0 (for a number field) or a zero-length string
(text). Neither of these are the same as Null, which essentially means
"unkonwn".

Another possibility, if the SubformField value is to be entered in new
records, is to use DefaultValue. In the subform's Current event:
Me.SubformField.DefaultValue = Me.Parent.MainFormField

DefaultValue applies only to new records, so it may not be what you need, but
it is sure to come in handy at some point if not here.

The subform control is the "box" on the main form containing the subform. If
you are referencing a subform field or control from the main form you need to
reference the Form property of the subform control:
Me.[SubformControlName].Form.[FieldName].

Note that the subform control may not have the same name as the subform
itself.

If the field and control names contain only alphanumeric characters and
underscores you do not need to use square brackets, but I used them here in
the hope it makes it easier to sort out.

Hi,

Below is a code that i change base form your coding

Private Sub SubformField_Enter()
If IsNull(SubformField.Value) Then SubformField.Value= Me.Parent.
MainformField.Value
End Sub

Thank you very much for the response, Benjamins!

[quoted text clipped - 13 lines]

If there are any other suggestions, I'd be very glad to hear them!

 

[BruceM via AccessMonster.com]

I would use Me.SubformField.Value, same as referencing the main form. The
Value property is default, so it is optional. That is, if it is left off the
Value will be used.

Be sure SubformField is not 0 (for a number field) or a zero-length string
(text). Neither of these are the same as Null, which essentially means
"unkonwn".

Another possibility, if the SubformField value is to be entered in new
records, is to use DefaultValue. In the subform's Current event:
Me.SubformField.DefaultValue = Me.Parent.MainFormField

DefaultValue applies only to new records, so it may not be what you need, but
it is sure to come in handy at some point if not here.

The subform control is the "box" on the main form containing the subform. If
you are referencing a subform field or control from the main form you need to
reference the Form property of the subform control:
Me.[SubformControlName].Form.[FieldName].

Note that the subform control may not have the same name as the subform
itself.

If the field and control names contain only alphanumeric characters and
underscores you do not need to use square brackets, but I used them here in
the hope it makes it easier to sort out.

Hi,

Below is a code that i change base form your coding

Private Sub SubformField_Enter()
If IsNull(SubformField.Value) Then SubformField.Value= Me.Parent.
MainformField.Value
End Sub

Thank you very much for the response, Benjamins!

[quoted text clipped - 13 lines]

If there are any other suggestions, I'd be very glad to hear them!

 

[speaton via AccessMonster.com]

Thank you, Benjamins!

My Mistake. It should be [SubformName].Form.Fieldname.Value. Have and extra
"s".

Yes, I noticed that and removed the extra "s," but I've just figured out
another problem that could be what has been tripping me up so far: I'm
actually working with a subform on a subform. I can't believe I'm just now
noticing that.

In that case, is it appropriate to refer to the subsubform field value like
this?

MainForm.Form.Subform.Form.Subsubform.Fieldname.Value

That's what I'm trying at the moment, and Access is telling me that either my
application definition or the object definition is incorrect.

There is a thing i need to understand: Is the SubformField a textbox?

The subsubform field is a combobox (as is the field I'm trying to use as the
default value--Access picks up that value with no trouble at all). Does that
change what I need to be doing?

With many thanks for the continuing advice,

 

[speaton via AccessMonster.com]

Thank you, BruceM!

Another possibility, if the SubformField value is to be entered in new
records, is to use DefaultValue. In the subform's Current event:
Me.SubformField.DefaultValue = Me.Parent.MainFormField

This seems to be working quite nicely, but I'm now running into a problem
where the values input in that subform (which I recently discovered is
actually a sub-subform) do not "stick".

I have three fields on the subform, one of which now gets the default value I
want thanks to your suggestion above, a second one that probably doesn't have
any bearing on this discussion, and a third field that causes the main form
to Requery when its AfterUpdate event is triggered.

Whenever that third field is updated, I'm now seeing that the values set in
the first two fields disappear, and the number of records in the subform
(which appears as a datasheet, so that the user can enter multiple child
records for each parent record) returns to 1. When that happens, the first
field does indeed take the expected default value, but the other information
is lost.

DefaultValue applies only to new records, so it may not be what you need, but
it is sure to come in handy at some point if not here.

I'll keep playing with this to see if I can find a way to make it work, but
if you have additional suggestions, I would be very glad to hear them!

With many thanks,

 

[Benjamins via AccessMonster.com]

Hi speaton,

To call the subsubform fileds form the mainform:
Me.Subform.Form.Subsubform.Fieldname.Value

To call the mainform fields from subsubform :
Me.Parent.Parent.Fieldname.Value or Forms!Mainform!Fieldname

Regard to the default value, you can set it the following after update method
for the mainform field

Private Sub MainformField_AfterUpdate()
Me.Subform.Form.Subsubform.SubSubFormField.DefaultValue = MainformField.
Text
End Sub

Thank you, Benjamins!

My Mistake. It should be [SubformName].Form.Fieldname.Value. Have and extra
"s".

Yes, I noticed that and removed the extra "s," but I've just figured out
another problem that could be what has been tripping me up so far: I'm
actually working with a subform on a subform. I can't believe I'm just now
noticing that.

In that case, is it appropriate to refer to the subsubform field value like
this?

MainForm.Form.Subform.Form.Subsubform.Fieldname.Value

That's what I'm trying at the moment, and Access is telling me that either my
application definition or the object definition is incorrect.

There is a thing i need to understand: Is the SubformField a textbox?

The subsubform field is a combobox (as is the field I'm trying to use as the
default value--Access picks up that value with no trouble at all). Does that
change what I need to be doing?

With many thanks for the continuing advice,

 

[BruceM via AccessMonster.com]

Let's be clear on the difference between a field and a control. A field is a
column in a table or query. In a table it is where the information is stored.
In a query it is where table data or calculated values are displayed. If you
bind a form to a query (that is, use the query as its Record Source) you can
use any of the fields, including calculated fields, in the same way as if you
used a table as the Record Source.

A control is just about anything you can place on a form or report. A bound
control is something such as text box, combo box, check box, etc. that serves
as a "portal" to a field in the form's underlying Record Source (table or
query). A combo box, etc. can also be unbound, which mean it has no Control
Source. This is usually the case for a search combo box, text box for
entering parameters for filtering, and so forth. Some controls such as
labels can only be unbound.

A form with a subform first needs a "box" for the subform. The box is the
subform control. Its source object is a form. Its Link Child and Link
Master properties are set to the linking fields (that is, the fields that
relate the main form's table to the subform's table). The same principle
applies if it is a sub-subform on a subform (nested subform).

So, your sub-subform needs a Record Source, which needs to be related to the
subform's Record Source. Each control on the form intended for entering data
or viewing stored data needs to be bound to a field in the Record Source.

It sounds to me as if controls are not bound to fields. If the above does
not sort things out, ddscribe the second control briefly, and post the After
Update code for the third control. Be sure there is a Control Source from
the form's Record Source for all three text boxes (controls) in question.
Also, a brief description of the database's purpose and structure
(relationships) may help.

 

[speaton via AccessMonster.com]

Thank you, Benjamins!

To call the subsubform fileds form the mainform:
Me.Subform.Form.Subsubform.Fieldname.Value

To call the mainform fields from subsubform :
Me.Parent.Parent.Fieldname.Value or Forms!Mainform!Fieldname

Excellent. I am glad to see that both of these are possible. I assumed both
were possible, but until now I didn't quite know how to go about it. Thank
you!

Regard to the default value, you can set it the following after update method
for the mainform field

Private Sub MainformField_AfterUpdate()
Me.Subform.Form.Subsubform.SubSubFormField.DefaultValue = MainformField.
Text
End Sub

Yes, this does seem to be a superior approach to the one I was attempting
before. Thank you very much for the advice!

 

[speaton via AccessMonster.com]

Thank you, BruceM, for the clear and useful explanation!

With the advice both you and Benjamins have offered in this thread, I think I
have now resolved this particular problem. Thank you very much for pointing
me in the right direction!