Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    requery textbox in subform (2000)

    I have a form that I use to enter data into a table. Below the form is a subform that gives related information based on the values that I enter into the form. In the subform I have a text box that has for its source a calculation of 2 of the fields from the form. If I hit F9 after I enter information in the last field of the form, the text box in the subform has the correct value. I would like to have it so I would not have to hit F9.
    I have tried to requery the form or the subform on the last field being dirty or on change but it does not update the value in the text box. Most of the combinations I tried makes the form go to a new record when you start to type anything in the last field of the form. I tried recalc also but it filled all the fields in with errors.
    Can someone please help and point me in the right direction? Thanks in advance.
    John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: requery textbox in subform (2000)

    Can you provide some more information about the calculated text box? E.g. what is its control source? And does it refer to fields in the subform or to fields in the main form?

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery textbox in subform (2000)

    Try this:

    Private Sub txtBOX_Exit(cancel as integer)
    Forms!subFORM!txtCALCULATEDVALUE.requery
    End Sub
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: requery textbox in subform (2000)

    So now you know what it takes to get a third star around here...

  5. #5
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: requery textbox in subform (2000)

    The control source for the text box in the subform is =1000/([Forms].[frmcard].[apcs]/[Forms].[frmcard].[ahrs]). APCS and AHRS are fields on the main form. The text box name is CARD_RATE. APCS is the last field on the main form. As I type numbers into the APCS field, the CARD_RATE field should change with each key stroke. If I tab off this last field it goes to a new record to be added. The subform name is sfrmtblstdrate. Thanks Hans.

  6. #6
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: requery textbox in subform (2000)

    Hi Jeremy,
    Congrats on your 3 stars.
    I tried your suggestion but it does not calculate a value. I am not sure how it would since I never exit the calculated text box.

    Private Sub Card_Rate_Exit(Cancel As Integer)
    Forms!sfrmtblStdRate!Card_Rate.Requery
    End Sub

    Did I put it in the wrong place?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: requery textbox in subform (2000)

    Sorry to be a pest, but why do you need to do this calculation in the subform? The result will be the same for each record in the subform. It would be more efficient to have a text box with control source =[apcs]/[ahrs] on the main form.

  8. #8
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: requery textbox in subform (2000)

    Hans,
    I think it is possible to do as you suggest. The reason I put the text box in the sub form was to do a conditional formatting with one of the fields in the subform.
    1. If I move the text box to the main form, I still need to have the text box update each time a key stroke is entered into the APCS field if that is possible. I would also have the text box as No for a tab stop.
    2. How would I refer to the field in the subform when I configure the conditional formatting? I tried forms!sfrmtblstdrate!rhrs, [forms].[sfrmtblstdrate].[rhrs] and [forms]![sfrmtblstdrate]![rhrs].
    Thank you for all your help.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: requery textbox in subform (2000)

    1. If you want the value to be updated as the user types, you must clear the control source of the calculated text box. Use the On Change event of the APCS text box to update CARD_RATE (assumed to be on the main form now):

    Private Sub APCS_Change()
    If Nz(Me.AHRS, 0) = 0 Then
    Me.CARD_RATE = Null
    Else
    Me.CARD_RATE = Val(Me.APCS.Text) / Me.AHRS
    End If
    End Sub

    2. If you move CARD_RATE to the main form, shouldn't you refer to that?
    You must include the main form when you refer to a control on a subform:
    [Forms]![MainFormName]![sfrmtblstdrate]![rhrs]
    where MainFormName is the name of the main form, and sfrmtblstdrate is the name of the subform as a control on the main form. This is not necessarily the same as the name of the subform in the database window.
    I don't know if it will work, I have never tried to refer to a control on another form when defining conditional formatting, either directly or indirectly.

  10. #10
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: requery textbox in subform (2000)

    Yes, too bad I don't have my own emoticon (maybe a fly?) All it takes is to be a pest <img src=/S/poke.gif border=0 alt=poke width=60 height=17>.

    <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  11. #11
    2 Star Lounger
    Join Date
    Nov 2002
    Location
    Mt. Olive, North Carolina, USA
    Posts
    170
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: requery textbox in subform (2000)

    Hans,
    This is the code I ended up with. I formatted the card_rate field to a percentage. I have conditional formatting for the card_rate field if the value is not between 80% and 120%. This works great.

    Private Sub APcs_Change()
    If Nz(Me.ahrs, 0) = 0 Then
    Me.Card_Rate = Null
    Else
    Me.Card_Rate = [Forms]![frmcard]![sfrmtblStdRate]![Rate_HRS] / (1000 / (Val(Me.apcs.Text) / Me.ahrs))
    End If

    End Sub

    I did try and refer to the subform field in the conditional formatting of the text box on the main form since you had never tried it and it works if you refer to it by [forms]![frmcard]![sfrmtblstdrate]![rhrs]. I ended up not needing to refer to it but I wanted to know if it would work.

    Thank you again for your help.

    Best Regards,
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •