Requery a field on a subform

Discussion in 'Microsoft Access Queries' started by antcraw@swbell.net, Nov 9, 2006.

  1. Guest

    Hi,
    I have an option group on a main form with four choices (radio
    buttons). I also have a txt field that takes the corresponding value
    from the option group, depending on which radio button is selected.
    Last but not least, I have a subform on this main form, which has a
    query as its control source. I would like to see a field on the subform
    updated depending on the value stored in the txt field on the main
    form, ultimately based on the selection in the option group. I've
    attempted to requery the subform through the after_update of the txt
    field, without success. Is this even possible?

    How could I approach this?

    Any suggestions/solutions are greatly appreciated.

    Regards,
    A. Crawford
     
    , Nov 9, 2006
    #1
    1. Advertisements

  2. Guest Guest

    First, I question your database design. It should not be necessary to store
    the value in the parent table and in the child table unless it is a field
    that is used to link the two tables, those being primary and foreign keys.
    Now the real problem comes into play. It is certainly possible to do what
    you want to do and no requery is required; however, this a one signficant
    problem. That is that when you assign the value to the control on the
    subform, it is going to change the value for the subform's current record.
    This is assuming the subform is a datasheet or continuous form. If it is a
    single record form, there will be no problem. What will happen, is the user
    can click the option button, but there is no assurrance the subform will be
    positioned on the correct record.

    All that being said, the correct syntax to populate a control on the subform
    with the value in a text box on the mainform follows. I assume you are
    updating the mainform control in the After Update event of the option group.
    It would also go there:

    Me.txt = Me.MyOption Group
    Me.MySubFormControl.Form!subtextbox = Me.txt

    Note that MySubFormControl is the name of the subform control on the main
    form, not necessarily the same as the name of the form that is the Source
    Object of the subform control.


    "" wrote:

    > Hi,
    > I have an option group on a main form with four choices (radio
    > buttons). I also have a txt field that takes the corresponding value
    > from the option group, depending on which radio button is selected.
    > Last but not least, I have a subform on this main form, which has a
    > query as its control source. I would like to see a field on the subform
    > updated depending on the value stored in the txt field on the main
    > form, ultimately based on the selection in the option group. I've
    > attempted to requery the subform through the after_update of the txt
    > field, without success. Is this even possible?
    >
    > How could I approach this?
    >
    > Any suggestions/solutions are greatly appreciated.
    >
    > Regards,
    > A. Crawford
    >
    >
     
    Guest, Nov 9, 2006
    #2
    1. Advertisements

  3. Guest

    Klatuu wrote:
    > First, I question your database design. It should not be necessary to store
    > the value in the parent table and in the child table unless it is a field
    > that is used to link the two tables, those being primary and foreign keys.
    > Now the real problem comes into play. It is certainly possible to do what
    > you want to do and no requery is required; however, this a one signficant
    > problem. That is that when you assign the value to the control on the
    > subform, it is going to change the value for the subform's current record.
    > This is assuming the subform is a datasheet or continuous form. If it is a
    > single record form, there will be no problem. What will happen, is the user
    > can click the option button, but there is no assurrance the subform will be
    > positioned on the correct record.
    >
    > All that being said, the correct syntax to populate a control on the subform
    > with the value in a text box on the mainform follows. I assume you are
    > updating the mainform control in the After Update event of the option group.
    > It would also go there:
    >
    > Me.txt = Me.MyOption Group
    > Me.MySubFormControl.Form!subtextbox = Me.txt
    >
    > Note that MySubFormControl is the name of the subform control on the main
    > form, not necessarily the same as the name of the form that is the Source
    > Object of the subform control.


    Actually, I am trying to just requery the text field on the subform in
    the after_update event of the option group, not populate it with the
    value from the main form. The text field on the subform has a
    calculation that I would like to see executed/refreshed depending on
    the choice in the option group on the main form.

    Sorry for the confusion.

    TIA.
    Regards,
    A. Crawford
     
    , Nov 10, 2006
    #3
  4. Guest Guest

    Okay, that make sense.
    This should do it in the After Update event of the option group.
    Remember, "MySubFormControl" is the name of the subform control on the form,
    not the name of the form that is the Source Object of the subform control.
    They can be the same (the wizard does that goofy little act), but in any
    case, you refer to the control.

    Me.MySubFormControl.Form!subtextbox.requery


    "" wrote:

    >
    > Klatuu wrote:
    > > First, I question your database design. It should not be necessary to store
    > > the value in the parent table and in the child table unless it is a field
    > > that is used to link the two tables, those being primary and foreign keys.
    > > Now the real problem comes into play. It is certainly possible to do what
    > > you want to do and no requery is required; however, this a one signficant
    > > problem. That is that when you assign the value to the control on the
    > > subform, it is going to change the value for the subform's current record.
    > > This is assuming the subform is a datasheet or continuous form. If it is a
    > > single record form, there will be no problem. What will happen, is the user
    > > can click the option button, but there is no assurrance the subform will be
    > > positioned on the correct record.
    > >
    > > All that being said, the correct syntax to populate a control on the subform
    > > with the value in a text box on the mainform follows. I assume you are
    > > updating the mainform control in the After Update event of the option group.
    > > It would also go there:
    > >
    > > Me.txt = Me.MyOption Group
    > > Me.MySubFormControl.Form!subtextbox = Me.txt
    > >
    > > Note that MySubFormControl is the name of the subform control on the main
    > > form, not necessarily the same as the name of the form that is the Source
    > > Object of the subform control.

    >
    > Actually, I am trying to just requery the text field on the subform in
    > the after_update event of the option group, not populate it with the
    > value from the main form. The text field on the subform has a
    > calculation that I would like to see executed/refreshed depending on
    > the choice in the option group on the main form.
    >
    > Sorry for the confusion.
    >
    > TIA.
    > Regards,
    > A. Crawford
    >
    >
     
    Guest, Nov 10, 2006
    #4
  5. Guest

    Klatuu wrote:
    > Okay, that make sense.
    > This should do it in the After Update event of the option group.
    > Remember, "MySubFormControl" is the name of the subform control on the form,
    > not the name of the form that is the Source Object of the subform control.
    > They can be the same (the wizard does that goofy little act), but in any
    > case, you refer to the control.
    >
    > Me.MySubFormControl.Form!subtextbox.requery


    I entered the above line into the after_update event of the option
    group and got a compile error for the Form! part of that code saying
    "Method or data member not found." So I tried to take out the
    MySubFormControl and just put it as:

    Me.Form!subtextbox.requery

    That produced the run-time error 2465 with the message "Microsoft
    Access can't find the field 'subtextbox' referred to in your
    expression."
    So I adjusted the code to:

    Me.sfrm_quote.setfocus
    Me.Form!subtextbox.requery

    and that did the trick as the target text field on the subform is now
    updating correctly.
    Would that make sense in your opinion and why am I getting the error if
    I leave the MySubFormControl in?

    Thanks in advance.
    Regards,
    A. Crawford
     
    , Nov 10, 2006
    #5
  6. Guest Guest

    Well, the problem is you took my code too literally. I did not know the name
    of the subform control, so I put that in to indicate which object to
    reference. If the name of the subform control is sfrm_quote, then the
    correct syntax should be

    Me.sfrm_quote.Form!subtextbox.requery

    "" wrote:

    >
    > Klatuu wrote:
    > > Okay, that make sense.
    > > This should do it in the After Update event of the option group.
    > > Remember, "MySubFormControl" is the name of the subform control on the form,
    > > not the name of the form that is the Source Object of the subform control.
    > > They can be the same (the wizard does that goofy little act), but in any
    > > case, you refer to the control.
    > >
    > > Me.MySubFormControl.Form!subtextbox.requery

    >
    > I entered the above line into the after_update event of the option
    > group and got a compile error for the Form! part of that code saying
    > "Method or data member not found." So I tried to take out the
    > MySubFormControl and just put it as:
    >
    > Me.Form!subtextbox.requery
    >
    > That produced the run-time error 2465 with the message "Microsoft
    > Access can't find the field 'subtextbox' referred to in your
    > expression."
    > So I adjusted the code to:
    >
    > Me.sfrm_quote.setfocus
    > Me.Form!subtextbox.requery
    >
    > and that did the trick as the target text field on the subform is now
    > updating correctly.
    > Would that make sense in your opinion and why am I getting the error if
    > I leave the MySubFormControl in?
    >
    > Thanks in advance.
    > Regards,
    > A. Crawford
    >
    >
     
    Guest, Nov 10, 2006
    #6
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. DeVille

    Requery combo box on subform

    DeVille, Oct 24, 2003, in forum: Microsoft Access Queries
    Replies:
    2
    Views:
    172
    Steve Schapel
    Oct 24, 2003
  2. Alex

    Requery Lookup based on Datasheet Subform

    Alex, Jan 27, 2004, in forum: Microsoft Access Queries
    Replies:
    0
    Views:
    207
  3. Marc

    Requery in a SubForm

    Marc, Mar 18, 2005, in forum: Microsoft Access Queries
    Replies:
    1
    Views:
    137
    Guest
    Mar 18, 2005
  4. Guest
    Replies:
    2
    Views:
    186
    Guest
    Apr 18, 2006
  5. Carlos

    Requery in a subform

    Carlos, Feb 5, 2009, in forum: Microsoft Access Queries
    Replies:
    4
    Views:
    152
    Carlos
    Feb 5, 2009
Loading...

Share This Page