A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Create subform default values based on value set in main form



 
 
Thread Tools Display Modes
  #1  
Old November 5th, 2009, 08:20 AM posted to microsoft.public.access.forms
speaton via AccessMonster.com
external usenet poster
 
Posts: 13
Default Create subform default values based on value set in main form

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

--
Message posted via http://www.accessmonster.com

  #2  
Old November 6th, 2009, 08:46 AM posted to microsoft.public.access.forms
speaton via AccessMonster.com
external usenet poster
 
Posts: 13
Default Create subform default values based on value set in main form

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

--

--
Patrick Eaton

Message posted via http://www.accessmonster.com

  #3  
Old November 9th, 2009, 08:14 AM posted to microsoft.public.access.forms
Benjamins via AccessMonster.com
external usenet poster
 
Posts: 68
Default Create subform default values based on value set in main form

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

speaton wrote:
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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

  #4  
Old November 9th, 2009, 08:42 AM posted to microsoft.public.access.forms
speaton via AccessMonster.com
external usenet poster
 
Posts: 13
Default Create subform default values based on value set in main form

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!

--
Message posted via http://www.accessmonster.com

  #5  
Old November 9th, 2009, 09:09 AM posted to microsoft.public.access.forms
Benjamins via AccessMonster.com
external usenet poster
 
Posts: 68
Default Create subform default values based on value set in main form

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?


speaton wrote:
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!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

  #6  
Old November 9th, 2009, 09:15 AM posted to microsoft.public.access.forms
Benjamins via AccessMonster.com
external usenet poster
 
Posts: 68
Default Create subform default values based on value set in main form

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

speaton wrote:
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!


--
Message posted via http://www.accessmonster.com

  #7  
Old November 9th, 2009, 01:25 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Create subform default values based on value set in main form

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.


Benjamins wrote:
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!


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200911/1

  #8  
Old November 9th, 2009, 01:25 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Create subform default values based on value set in main form

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.


Benjamins wrote:
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!


--
Message posted via http://www.accessmonster.com

  #9  
Old November 9th, 2009, 01:25 PM posted to microsoft.public.access.forms
BruceM via AccessMonster.com
external usenet poster
 
Posts: 448
Default Create subform default values based on value set in main form

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.


Benjamins wrote:
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!


--
Message posted via http://www.accessmonster.com

  #10  
Old November 10th, 2009, 02:34 AM posted to microsoft.public.access.forms
speaton via AccessMonster.com
external usenet poster
 
Posts: 13
Default Create subform default values based on value set in main form

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.Va lue

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,

--

--
Patrick Eaton

Message posted via http://www.accessmonster.com

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:45 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.