set value in subform

I want to use the set value macro to set a control in a subform based on a 
control in another form.  

I have three forms: FormA, FormB, FormC.  FormC is a subform within FormB.  
What I want is to be able to enter a value in FormA and then have FormC 
automatically updated.    

Currently, if I open FormA and FormC I am able to use the SetValue macro to 
do this.  

However, if I open FormA and FormB (recall that FormC is a subform within 
FormB) and try to run the macro, Access gives me an error.  I'm assuming that 
it is because Access does not think FormC is open (even though it is open 
indirectly as a subform within FormB).  

Any advice?  Thank you in advance.      
0
Utf
1/7/2008 5:11:03 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
2722 Views

Similar Articles

[PageSpeed] 48

It may be a problem with the syntax you are using to refer to form C when it 
is a subform. To refer to a control on a subform use the following syntax;

Forms!MainFormName![SubformControlName].Form![ControlName]

The [SubformControlName] is the name of the control that holds the subform, 
which may or may not be the same as the name of the subform itself.


HTH
-- 
_________

Sean Bailey


"cuyeda" wrote:

> I want to use the set value macro to set a control in a subform based on a 
> control in another form.  
> 
> I have three forms: FormA, FormB, FormC.  FormC is a subform within FormB.  
> What I want is to be able to enter a value in FormA and then have FormC 
> automatically updated.    
> 
> Currently, if I open FormA and FormC I am able to use the SetValue macro to 
> do this.  
> 
> However, if I open FormA and FormB (recall that FormC is a subform within 
> FormB) and try to run the macro, Access gives me an error.  I'm assuming that 
> it is because Access does not think FormC is open (even though it is open 
> indirectly as a subform within FormB).  
> 
> Any advice?  Thank you in advance.      
0
Utf
1/7/2008 6:40:00 PM
As Beetle suggested, the syntax is your most likely issue.  However, you 
have a larger issue and that is - precisely which record of form C do you 
think you are updating?  I suspect a flaw in your logic and possibly your 
table design.  If you'll tell us more about what you are trying to do, we 
can give you some advice.

"Beetle" <Beetle@discussions.microsoft.com> wrote in message 
news:7BABF794-AB77-41CF-8570-0DA7ADF948BA@microsoft.com...
> It may be a problem with the syntax you are using to refer to form C when 
> it
> is a subform. To refer to a control on a subform use the following syntax;
>
> Forms!MainFormName![SubformControlName].Form![ControlName]
>
> The [SubformControlName] is the name of the control that holds the 
> subform,
> which may or may not be the same as the name of the subform itself.
>
>
> HTH
> -- 
> _________
>
> Sean Bailey
>
>
> "cuyeda" wrote:
>
>> I want to use the set value macro to set a control in a subform based on 
>> a
>> control in another form.
>>
>> I have three forms: FormA, FormB, FormC.  FormC is a subform within 
>> FormB.
>> What I want is to be able to enter a value in FormA and then have FormC
>> automatically updated.
>>
>> Currently, if I open FormA and FormC I am able to use the SetValue macro 
>> to
>> do this.
>>
>> However, if I open FormA and FormB (recall that FormC is a subform within
>> FormB) and try to run the macro, Access gives me an error.  I'm assuming 
>> that
>> it is because Access does not think FormC is open (even though it is open
>> indirectly as a subform within FormB).
>>
>> Any advice?  Thank you in advance. 


0
Pat
1/7/2008 7:38:11 PM
Thanks Beetle - you are correct it was a syntax issue.  

Pat, here's some more on what I'm doing.  I would not be surprised if I had 
a flawed design or logic and would appreciate any expert advice.  

TABLE DESIGN:
I have four tables. Table 1 records the scientific name of a species, Table 
2 records the "population" of the species.  Table 1 and 2 are linked in a one 
to many relationship as a species can have multiple populations.  Table 3 
records "petitions".  Because a petition can have multiple species and a 
species can be on multiple petitions Table 2 and 3 are related with a join 
table (Table 4).  

WHAT I'M DOING:  
I'm developing a form so that a user can enter the species/populations that 
were included in a petition

HOW I'M DOING IT:
I use three forms.  FormA is for entering information regarding the petition 
that is NOT related to species (eg. the date the petition was received).  
FormB is a subform within FormA.  It has two combo boxes.  One for selecting 
the species, the other for the population.  Then the form displays the unique 
ID associated with that species/population.  
FormC is also a subform within FormA.  The form allows the user to enter the 
species ID of the petitioned species (which was already found in formB) and 
then other data associated with that petitioned species.  Since a petition 
can have multiple species the form allows the user to enter multiple species. 
 

Is there a better or more efficient way of doing this?  This is the best I 
could do with my current knowledge of Access.  Thanks.    

"Pat Hartman" wrote:

> As Beetle suggested, the syntax is your most likely issue.  However, you 
> have a larger issue and that is - precisely which record of form C do you 
> think you are updating?  I suspect a flaw in your logic and possibly your 
> table design.  If you'll tell us more about what you are trying to do, we 
> can give you some advice.
> 
> "Beetle" <Beetle@discussions.microsoft.com> wrote in message 
> news:7BABF794-AB77-41CF-8570-0DA7ADF948BA@microsoft.com...
> > It may be a problem with the syntax you are using to refer to form C when 
> > it
> > is a subform. To refer to a control on a subform use the following syntax;
> >
> > Forms!MainFormName![SubformControlName].Form![ControlName]
> >
> > The [SubformControlName] is the name of the control that holds the 
> > subform,
> > which may or may not be the same as the name of the subform itself.
> >
> >
> > HTH
> > -- 
> > _________
> >
> > Sean Bailey
> >
> >
> > "cuyeda" wrote:
> >
> >> I want to use the set value macro to set a control in a subform based on 
> >> a
> >> control in another form.
> >>
> >> I have three forms: FormA, FormB, FormC.  FormC is a subform within 
> >> FormB.
> >> What I want is to be able to enter a value in FormA and then have FormC
> >> automatically updated.
> >>
> >> Currently, if I open FormA and FormC I am able to use the SetValue macro 
> >> to
> >> do this.
> >>
> >> However, if I open FormA and FormB (recall that FormC is a subform within
> >> FormB) and try to run the macro, Access gives me an error.  I'm assuming 
> >> that
> >> it is because Access does not think FormC is open (even though it is open
> >> indirectly as a subform within FormB).
> >>
> >> Any advice?  Thank you in advance. 
> 
> 
> 
0
Utf
1/7/2008 8:53:05 PM
Well, here are some thoughts (FWIW)

First, do you petition a species or do you petition a population? If you 
petition a species, do you automatically petition every population within 
that species? What I'm getting at is the actual nature of the many-to-many 
relationship. Is it between the petition and the species or the petition and 
the population?

Second, if all you're doing with FormB is selecting a species/population, 
I'm not even sure it's necessary. Since you're selecting the species again in 
FormC, any information related to that species ID can be retrieved and 
displayed on your form in unbound controls.

Third, FormC should be based on your junction table (if it's not already - 
you don't specify in your post). Likewise, any fields that are related 
*specifically* to that petition/species record should be in the junction 
table.
-- 
_________

Sean Bailey


"cuyeda" wrote:

> Thanks Beetle - you are correct it was a syntax issue.  
> 
> Pat, here's some more on what I'm doing.  I would not be surprised if I had 
> a flawed design or logic and would appreciate any expert advice.  
> 
> TABLE DESIGN:
> I have four tables. Table 1 records the scientific name of a species, Table 
> 2 records the "population" of the species.  Table 1 and 2 are linked in a one 
> to many relationship as a species can have multiple populations.  Table 3 
> records "petitions".  Because a petition can have multiple species and a 
> species can be on multiple petitions Table 2 and 3 are related with a join 
> table (Table 4).  
> 
> WHAT I'M DOING:  
> I'm developing a form so that a user can enter the species/populations that 
> were included in a petition
> 
> HOW I'M DOING IT:
> I use three forms.  FormA is for entering information regarding the petition 
> that is NOT related to species (eg. the date the petition was received).  
> FormB is a subform within FormA.  It has two combo boxes.  One for selecting 
> the species, the other for the population.  Then the form displays the unique 
> ID associated with that species/population.  
> FormC is also a subform within FormA.  The form allows the user to enter the 
> species ID of the petitioned species (which was already found in formB) and 
> then other data associated with that petitioned species.  Since a petition 
> can have multiple species the form allows the user to enter multiple species. 
>  
> 
> Is there a better or more efficient way of doing this?  This is the best I 
> could do with my current knowledge of Access.  Thanks.    
> 
> "Pat Hartman" wrote:
> 
> > As Beetle suggested, the syntax is your most likely issue.  However, you 
> > have a larger issue and that is - precisely which record of form C do you 
> > think you are updating?  I suspect a flaw in your logic and possibly your 
> > table design.  If you'll tell us more about what you are trying to do, we 
> > can give you some advice.
> > 
> > "Beetle" <Beetle@discussions.microsoft.com> wrote in message 
> > news:7BABF794-AB77-41CF-8570-0DA7ADF948BA@microsoft.com...
> > > It may be a problem with the syntax you are using to refer to form C when 
> > > it
> > > is a subform. To refer to a control on a subform use the following syntax;
> > >
> > > Forms!MainFormName![SubformControlName].Form![ControlName]
> > >
> > > The [SubformControlName] is the name of the control that holds the 
> > > subform,
> > > which may or may not be the same as the name of the subform itself.
> > >
> > >
> > > HTH
> > > -- 
> > > _________
> > >
> > > Sean Bailey
> > >
> > >
> > > "cuyeda" wrote:
> > >
> > >> I want to use the set value macro to set a control in a subform based on 
> > >> a
> > >> control in another form.
> > >>
> > >> I have three forms: FormA, FormB, FormC.  FormC is a subform within 
> > >> FormB.
> > >> What I want is to be able to enter a value in FormA and then have FormC
> > >> automatically updated.
> > >>
> > >> Currently, if I open FormA and FormC I am able to use the SetValue macro 
> > >> to
> > >> do this.
> > >>
> > >> However, if I open FormA and FormB (recall that FormC is a subform within
> > >> FormB) and try to run the macro, Access gives me an error.  I'm assuming 
> > >> that
> > >> it is because Access does not think FormC is open (even though it is open
> > >> indirectly as a subform within FormB).
> > >>
> > >> Any advice?  Thank you in advance. 
> > 
> > 
> > 
0
Utf
1/8/2008 3:55:00 AM
Reply:

Similar Artilces:

Paste Values not working in Excel 2003 (?)
Hi, Using Excel 2003 with Win xp Pro I copy a range of cels, then I choose EDIT > PASTE SPECIAL > VALUES and it does NOT paste ALL the cel contentent... It does paste all the cels, but only the first 3 lines of each cel's content. Is there any limitation in the number of characters ? Thanks ALex This is not the exact problem you describe, put perhaps it's related: XL2000: "255 Characters in Cell" Error When Copying Worksheet, at: http://support.microsoft.com/default.aspx?scid=kb;en-us;213548 -- HTH - -Frank Isaacs Dolphin Technology Corp. http://vbapro.com ...

How can I set Outlook, I have problems with that.
I Try to use Outlook in my PC but I don't have a clue to set Outlook, I know if I can set the program I see my mail bye Outlook but is difficult to me understand... please can you help me how to set the program step by step? Thanks... You will need the server names from your ISP in order to set it up. We cannot help you with that. Most ISPs will post instructions on how to setup Outlook Express (they will not support Outlook) but you can use the same information to set up Outlook account(s). --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion in...

Set Focus 05-22-07
I have a command button that displays and filters 2 forms. I would like to set the focus to a specific field on another form when these are opened. TIA In news:1179848189.605344.149570@r3g2000prh.googlegroups.com, myxmaster@hotmail.com <myxmaster@hotmail.com> wrote: > I have a command button that displays and filters 2 forms. I would > like to set the focus to a specific field on another form when these > are opened. Something along the lines of: DoCmd.OpenForm "Form1", WhereCondition:="[SomeField]=1" DoCmd.OpenForm "Form2", WhereConditi...

Assign Values to String Value
Hello, I am trying to assign a numeric value to cells with string/text variables. For instance, 10-2 would signify 4 hours... so the value I would like to calculate would be 4. Is there an easy way to do this? Thanks "PC-Renew" wrote: > I am trying to assign a numeric value to cells with string/text variables. > For instance, 10-2 would signify 4 hours... so the value I would like to > calculate would be 4. Is there an easy way to do this? VLOOKUP would be, imo, the easiest. Eg: Assume textstring in A1: 11-7 Then in B1: =VLOOKUP(A1,{"10-2",4;"1...

How to set view titles in this scenario.
Hi My application always creates two view on file new, file open . Its a MDI application. I have refered following link. http://www.codeguru.com/cpp/w-d/doc_view/viewmanagement/article.php/c3275/ Problem is , when I execute the application, and new document is automatically created. But the both view titles remain same. I want them to be xxx1:1 and xxx1:2 but both of them display xxx1 only. This is not the case when I manually click file->new. How to set the proper view titles here. thanks leee. The link you mentioned does not work. If you are setting titile of MDI child frame, you can...

Last Value in a Column when Value <> 0
Hi, can someone provide me a formula that populates a cell with the last value in a column that does not = 0? Below is an example 3 6 7 0 0 0 The goal is to populate a cell with the value of 7. I am currently using the below formula that populates the last value of a column: =INDEX('Retirement Total'!B:B,MATCH(9.99999999999999E+307,'Retirement Total'!B:B)) I do not however know how to change this to not populate the last value when it is zero. Your help is appreciated. Assuming you want the last *numeric* value that <>0. Try something like this: =LOOKUP(1E100,1/'...

I can't set up my account on Windows Mail
Hi . I need help, I tried to set my email up on Windows mail using, gmail accounts, gmx and everything you could think. I worked on it for days but nothing ever worked. I'm pretty sure I put in the right POP and all. What else can I do? Does Gmail and gmx and all not work on a mail server on the actual desktop? Please help me? asiankelsey;1224241 Wrote: > Hi . I need help, I tried to set my email up on Windows mail using, > gmail > accounts, gmx and everything you could think. I worked on it for days > but > nothing ever worked. I'm pretty sure I put i...

Setting a rule to look for a word in the 'from' header?
Hi O' 20002 I can't seem to set a rule that looks for a word in the 'From' header in the inbox. It's the part that is in the 'Displayed Name' box when of the senders email address. Any Ideas? Thanks in advance Dave F. It is a standard Wizard Rule so what is the problem? Make sure that the peolpe in the rule are in your Address Book/Contacts folder as well. -- Roady www.sparnaaij.net Microsoft Office and Microsoft Office related News Also Outlook FAQ, How To's, Downloads and more... ----- "Dave F" <david.fox@burohappold.com> wrote in mes...

Copy a value without linking?
I am trying to automatically set the value of a text box within a subform when the user creates a new record in that subform. I want the value to be pulled from another form (the main form which the subform is linked to) in the database, but I do not want the value to ever be updated. In other words, I want to just copy the value from the main from to the test box on the subform-- once and only once. Example: MainForm: field="Name" (field is a combo box) Subform: field="NameAtTimeRecordWasCreated" (field is a text box) The "Name" field on MainForm...

Average w/o Zero Values
Was wondering if anyone could kindly suggest a method of determining the average of a column (or row) of numbers but not including any cells with a value of zero? Also, if a range is conditionally formated to flag numerical values not within a certain range, how can one prevent it from flagging cells which are set = blank or empty? TIA for any assistance............. Laurie Forbes Hi Laurie: For averages excluding zeros you can use: =AVERAGE(IF(MyRange,MyRange)) Entered as an array by pressing Ctrl + Shift and then pressing Enter With your conditional formatting problem. Use a first c...

how do i use format cells to alpha / numeric values
HAVE A STRING OF DATA i WANT TO ENTER into a cell, but I also want to set format to set the values. eg. if I put in asd123 I want it to appear as ASD123 but there are also occasions when I will put in a different mix of letters/numbers a12s3d, which I also want to come out as A12S3D. I can do this in Access but cant seem to crack the code in excel. Please help. Try =UPPER(E2) HTH Regards, Howard "Samvid69" <Samvid69@discussions.microsoft.com> wrote in message news:701E3143-A79D-4FDE-8661-325795CE50F0@microsoft.com... > HAVE A STRING OF DATA i WANT T...

Setting cell tabulations( ranges) in excell
I would like to set a ranage for tabulation; the row starts with "Week day and ends with END". You can automatically tab from WEEK day to END but to speed things up I want the tab to go to the next row once it reaches" End". instead of going to the next empty cell on the same row. How can I set a tabulation range? Example: Week Day Date Shift Start Lunch In Lunch Out Shift End Wednesday 18-Aug-04 Thursday 19-Aug-04 If you select your cells (say B2:H999) first, you can tab between the cells in the selection. dizzy kay wrote: >...

How do Bar chart where Bar colour changes with different data Valu
hi, I would like to develop a Bar chart, which changes its bar colour for different data values. e.g for a cell format and to change colour of a cell for different values we can use conditional format. Hi, Have a look an Jon's example of conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy Zeeshan wrote: > hi, > I would like to develop a Bar chart, which changes its bar colour for > different data values. e.g for a cell format and to change colour of a cell > for different values we can use conditional format. -- Andy Pope, Micro...

Setting Up a Novel
I would like to write a book using Publisher 2002. How can I set this up to achieve optimum results in the shortest period of time. Thanks Barry MS Word would be a better option for the writing, thereafter using Publisher (maybe) to lay it out.. -- Mike Hall MS-MVP Windows Shell/User "Barry" <cbj97@yahoo.com> wrote in message news:ebL81lg%23GHA.2408@TK2MSFTNGP05.phx.gbl... >I would like to write a book using Publisher 2002. How can I set this up to >achieve optimum results in the shortest period of time. Thanks Barry > Barry, You should contact your...

How do I create new default color settings for charts in Excel?
I do not like the default color settings for my charts in Excel. I always end up modifying them extensively. I'd like to make as few changes as possible when my charts are created. Can I set new color defaults for charts created in Excel? Hi, You can set the colours for chart lines and fill. Menu Tools > Options. On the Color tab is a set of colours for Line and Fill. Cheers Andy pro user wrote: > I do not like the default color settings for my charts in Excel. I always end > up modifying them extensively. I'd like to make as few changes as possible > when my char...

Saving Report Print Settings in Access 2007
I've upgraded to Office 2007. I have several reports that I want to print using a certain printer, 2 sided and hole punched. I've used Page Setup to select the printer and properties I want. However, Access doesn't save my settings. According to the Help section, it should save the settings. What am I doing wrong? Yes, this is a bug in Access 2007. Microsoft has not promised, but we are hoping it is fixed when SP1 is released. In the mean time, I'm not aware of a solution, but is is possible to achieve the same effect by setting the Printer object to the right prin...

Dynamics GP 9.0 setting up 5000 Inventory items quickly
Hi folks: Need a suggestion on how to set up 5000 new Inventory Items in a quick, effecient way. The system does have integration manager, but is there another way to mass import an excel list that will automatically populate the accounts, and pricing method based on the inventory class that these inventory items all are? Regards -- Clay Tait Tait Distributors Having done this job a number of different ways, I would suggest Integration Manager. It is fairly complete. You could create a macro to create an inventory item and then mail merge the macro with the list, but error hand...

Page Setting
I want to change Default page setting so whenever i open new or existance excel file my described settings should be apply on that file. Maaz 1. A macro to set up the page settings as you wish. 2. If you want a default solution for all NEW workbooks............ Open a new workbook. Customize as you wish. Note: you can set all sheet footers at the same time by "grouping" the sheets. Right-click on a sheet tab and "select all sheets". Set up a footer on the active sheet and will be done to all. Ungroup when setup is complete. File>Save As Type: scroll down to E...

Count of records on subform inside a tab
I have two subforms, each inside a single tab control (one tab for each subform). I'm trying to count the records on those subforms so I can determine to 'disable' a tab when the subform has no records returned. I can't get the subforms to count their records when none are returned. I get a blank field, but the field isn't equal to 0, null, empty or "". Anyone know what's going on? Robert_L_Ross wrote: >I have two subforms, each inside a single tab control (one tab for each >subform). > >I'm trying to count the records on those subfor...

Setting up a form to input into selected worksheet
I am sure this is kinda simple but haven't messed around with it since the late 90's so here it goes. I have a workbook with about 40 different sheets each sheet has its basically for accounting issues each sheet has a about 1500 rows prefilled in with every date for a year on each row I have 5 withdraw columns and a deposit column and a running balance column. what I need to do is create a form on the first worksheet that will give you a list box of all the worksheets and then another field that will let you input the date and 2 more boxes one for withdraws and 1 for deposits a...

DOS Console and Setting It Up for Text Completion, etc.
I'd like to set up the console, so the default properties allow me to use text completion, 120 character with by 200 deep lines, allow text completion and allow me to copy from the black window? What do I need to do? Some part of this has to do with Quick Edit, which is probably to turn it off. Have you checked the "Layout" tab in Properties? -- JS http://www.pagestart.com "W. eWatson" <wolftracks@invalid.com> wrote in message news:hevd9u$3ft$1@news.eternal-september.org... > I'd like to set up the console, so the default propertie...

Setting chart data range automatically
Hi from Loucas, This is my second posting to the forum. I hope someone out there ca help me! I am trying to set the data range for my chart automatically. Som macros change the data in my worksheet with the data, and as a resul my chart data range may need to cover more/less rows and/or columns. So, every time I run the macros for my data worksheet, I need anothe macro to re-define the data range for my graph. More info: 1. I have variables that reflect the column and row numbers for m range that I can use 2. The data range does not consist of consecutive cells (there is a ga of data colum...

Using a cell's value in a macro command
Hi, I need to use a value of a cell in a spreadsheet in a macro command to copy data. For Example. cell A1 = 28/09/2009 Sub Workbooks.Open Filename:= _ "C:\(Value from A1)" End Sub Thanks, Tom hi try something like this......... Dim r As String r = Range("A1").Value Workbooks.Open Filename:= _ "C:\your\file\path\" & r & ".xls" but in your example data, you have slashes. these are illigal characters in a file name so i doubt that you have a file with slashes in the name. exc...

WLMD Cannot Be Set As Default Email Application
I runs on Windows 7 Ultimate, and I already set Windows Live Mail Desktop as my default email application of my OS, but there often a message box appears and tells me my OS has no default email client application, why? How can I solve this problem? Thanks! Are you in IE trying to use the Send Page / Send Link feature when you get this message? "Michael Cheng" <Michael Cheng@discussions.microsoft.com> wrote in message news:3B051DB9-CD98-41FC-96E3-410F3C2C1F11@microsoft.com... > I runs on Windows 7 Ultimate, and I already set Windows Live Mail Desktop > ...

How do I set number formats that will be applied only if a number. #2
How do I write a formula to change the color of the results? Example: If a cell is feeding back 0 – 100%, and I want 90 – 100% in green, 80 – 89% in blue and less than 79% in red. Hi see 'Format - Conditional Format' for this -- Regards Frank Kabel Frankfurt, Germany "Jim Gentile" <JimGentile@discussions.microsoft.com> schrieb im Newsbeitrag news:4ED401D6-0434-4E9B-A89A-DAB45EE407C1@microsoft.com... > How do I write a formula to change the color of the results? > > Example: > > If a cell is feeding back 0 – 100%, and I want 90 – 100% in green, 80 –...