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
  #11  
Old November 10th, 2009, 03:13 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, 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,

--

--
Patrick Eaton

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

  #12  
Old November 10th, 2009, 09:42 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 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.Default Value = MainformField.
Text
End Sub

speaton wrote:
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.V alue

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,


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

  #13  
Old November 10th, 2009, 02:13 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

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


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

  #14  
Old November 11th, 2009, 01:22 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!

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.Default Value = 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!

--

--
Patrick Eaton

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

  #15  
Old November 11th, 2009, 01:26 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, 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!

--

--
Patrick Eaton

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

 




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:41 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.