Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

OnChange Event Changes all records?!

Discussion in 'Information Technology' started by Simon Harris, Jul 28, 2006.

  1. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Hi All,

    I have an on change event on a text box which does the following:

    If Me.GasMeterType.Value = "m²" Then
    Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    DLookup("Gas_Per_Mtr3", "tbl_constants")
    ElseIf Me.GasMeterType.Value = "ft²" Then
    Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    DLookup("Gas_Per_Ft3", "tbl_constants")
    Else
    MsgBox ("Warning: No gas meter type recorded for this pitch")
    End If

    Trouble is, when fired, this updates the GasAmount field for all
    records...not just the current record as I expected. The form is normally in
    data sheet mode, although I have tried continuous form, which causes the
    same problem.

    Odd? Or am I not understanding something here?

    Thanks,
    Simon.

    --
    -
    * Please reply to group for the benefit of all
    * Found the answer to your own question? Post it!
    * Get a useful reply to one of your posts?...post an answer to another one
    * Search first, post later : http://www.google.co.uk/groups
    * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
     
  2. Loading...


  3. Graham Mandeno

    Graham Mandeno
    Expand Collapse
    Guest

    Hi Simon

    If GasAmount is an unbound textbox then it cannot display different values
    in different records.

    You will need to make it a calculated control and write a function to
    calculate the value:

    Private Function CalcGasAmount()
    If Me.GasMeterType.Value = "m²" Then
    CalcGasAmount = (Me.Gas_Reading - Me.Gas_Reading_Previous) _
    * DLookup("Gas_Per_Mtr3", "tbl_constants")
    ElseIf Me.GasMeterType.Value = "ft²" Then
    Calc.GasAmount = (Me.Gas_Reading - Me.Gas_Reading_Previous) _
    * DLookup("Gas_Per_Ft3", "tbl_constants")
    Else
    CalcGasAmount = "No meter type"
    End If
    End Function

    Then, set the ControlSource of your textbox to:

    =CalcGasAmount()

    However, there is a better way to do it...

    Instead of storing your factors in tbl_Constants, create a new table
    tbl_GasMeterTypes, with three fields:
    GasMeterTypeID (numeric primary key - could be byte or autonumber)
    GasMeterUnit (text, no duplicates)
    GasPerUnit (the factor for this unit)

    Now you can add this table to your form's recordsource query and the
    GasPerUnit factor will be available as a field in the query.

    Furthermore, the GasAmount can be calculated as another field in the query:

    GasAmount: ([Gas_Reading] - [Gas_Reading_Previous]) * [GasPerUnit]

    and this field can be bound to your textbox.

    Or, if you prefer, just enter the calculation in the controlsource of the
    textbox.

    By the way, you should have been using the AfterUpdate event, not the Change
    event for what you were attempting. The Change event fires every time the
    text in the textbox changes (i.e. with every keystroke). The AfterUpdate
    event fires only when the value of the textbox is changed - when editing the
    contents is complete.
    --
    Good Luck!

    Graham Mandeno [Access MVP]
    Auckland, New Zealand


    "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    news:v_2fg.5173$O7.3183@newsfe5-win.ntli.net...
    > Hi All,
    >
    > I have an on change event on a text box which does the following:
    >
    > If Me.GasMeterType.Value = "m²" Then
    > Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    > DLookup("Gas_Per_Mtr3", "tbl_constants")
    > ElseIf Me.GasMeterType.Value = "ft²" Then
    > Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    > DLookup("Gas_Per_Ft3", "tbl_constants")
    > Else
    > MsgBox ("Warning: No gas meter type recorded for this pitch")
    > End If
    >
    > Trouble is, when fired, this updates the GasAmount field for all
    > records...not just the current record as I expected. The form is normally
    > in data sheet mode, although I have tried continuous form, which causes
    > the same problem.
    >
    > Odd? Or am I not understanding something here?
    >
    > Thanks,
    > Simon.
    >
    > --
    > -
    > * Please reply to group for the benefit of all
    > * Found the answer to your own question? Post it!
    > * Get a useful reply to one of your posts?...post an answer to another one
    > * Search first, post later : http://www.google.co.uk/groups
    > * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
    >
     
  4. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Graham,

    Thank you very much for your comprehensive answer. Much appreciated.

    Simon.

    "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    news:%232qc1eDhGHA.4144@TK2MSFTNGP02.phx.gbl...
    > Hi Simon
    >
    > If GasAmount is an unbound textbox then it cannot display different values
    > in different records.
    >
    > You will need to make it a calculated control and write a function to
    > calculate the value:
    >
    > Private Function CalcGasAmount()
    > If Me.GasMeterType.Value = "m²" Then
    > CalcGasAmount = (Me.Gas_Reading - Me.Gas_Reading_Previous) _
    > * DLookup("Gas_Per_Mtr3", "tbl_constants")
    > ElseIf Me.GasMeterType.Value = "ft²" Then
    > Calc.GasAmount = (Me.Gas_Reading - Me.Gas_Reading_Previous) _
    > * DLookup("Gas_Per_Ft3", "tbl_constants")
    > Else
    > CalcGasAmount = "No meter type"
    > End If
    > End Function
    >
    > Then, set the ControlSource of your textbox to:
    >
    > =CalcGasAmount()
    >
    > However, there is a better way to do it...
    >
    > Instead of storing your factors in tbl_Constants, create a new table
    > tbl_GasMeterTypes, with three fields:
    > GasMeterTypeID (numeric primary key - could be byte or autonumber)
    > GasMeterUnit (text, no duplicates)
    > GasPerUnit (the factor for this unit)
    >
    > Now you can add this table to your form's recordsource query and the
    > GasPerUnit factor will be available as a field in the query.
    >
    > Furthermore, the GasAmount can be calculated as another field in the
    > query:
    >
    > GasAmount: ([Gas_Reading] - [Gas_Reading_Previous]) * [GasPerUnit]
    >
    > and this field can be bound to your textbox.
    >
    > Or, if you prefer, just enter the calculation in the controlsource of the
    > textbox.
    >
    > By the way, you should have been using the AfterUpdate event, not the
    > Change event for what you were attempting. The Change event fires every
    > time the text in the textbox changes (i.e. with every keystroke). The
    > AfterUpdate event fires only when the value of the textbox is changed -
    > when editing the contents is complete.
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    >
    > "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    > news:v_2fg.5173$O7.3183@newsfe5-win.ntli.net...
    >> Hi All,
    >>
    >> I have an on change event on a text box which does the following:
    >>
    >> If Me.GasMeterType.Value = "m²" Then
    >> Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    >> DLookup("Gas_Per_Mtr3", "tbl_constants")
    >> ElseIf Me.GasMeterType.Value = "ft²" Then
    >> Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    >> DLookup("Gas_Per_Ft3", "tbl_constants")
    >> Else
    >> MsgBox ("Warning: No gas meter type recorded for this pitch")
    >> End If
    >>
    >> Trouble is, when fired, this updates the GasAmount field for all
    >> records...not just the current record as I expected. The form is normally
    >> in data sheet mode, although I have tried continuous form, which causes
    >> the same problem.
    >>
    >> Odd? Or am I not understanding something here?
    >>
    >> Thanks,
    >> Simon.
    >>
    >> --
    >> -
    >> * Please reply to group for the benefit of all
    >> * Found the answer to your own question? Post it!
    >> * Get a useful reply to one of your posts?...post an answer to another
    >> one
    >> * Search first, post later : http://www.google.co.uk/groups
    >> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
    >>

    >
    >
     
  5. Simon Harris

    Simon Harris
    Expand Collapse
    Guest

    Graham,

    Thank you very much for your comprehensive answer. Much appreciated.

    Simon.

    "Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
    news:%232qc1eDhGHA.4144@TK2MSFTNGP02.phx.gbl...
    > Hi Simon
    >
    > If GasAmount is an unbound textbox then it cannot display different values
    > in different records.
    >
    > You will need to make it a calculated control and write a function to
    > calculate the value:
    >
    > Private Function CalcGasAmount()
    > If Me.GasMeterType.Value = "m²" Then
    > CalcGasAmount = (Me.Gas_Reading - Me.Gas_Reading_Previous) _
    > * DLookup("Gas_Per_Mtr3", "tbl_constants")
    > ElseIf Me.GasMeterType.Value = "ft²" Then
    > Calc.GasAmount = (Me.Gas_Reading - Me.Gas_Reading_Previous) _
    > * DLookup("Gas_Per_Ft3", "tbl_constants")
    > Else
    > CalcGasAmount = "No meter type"
    > End If
    > End Function
    >
    > Then, set the ControlSource of your textbox to:
    >
    > =CalcGasAmount()
    >
    > However, there is a better way to do it...
    >
    > Instead of storing your factors in tbl_Constants, create a new table
    > tbl_GasMeterTypes, with three fields:
    > GasMeterTypeID (numeric primary key - could be byte or autonumber)
    > GasMeterUnit (text, no duplicates)
    > GasPerUnit (the factor for this unit)
    >
    > Now you can add this table to your form's recordsource query and the
    > GasPerUnit factor will be available as a field in the query.
    >
    > Furthermore, the GasAmount can be calculated as another field in the
    > query:
    >
    > GasAmount: ([Gas_Reading] - [Gas_Reading_Previous]) * [GasPerUnit]
    >
    > and this field can be bound to your textbox.
    >
    > Or, if you prefer, just enter the calculation in the controlsource of the
    > textbox.
    >
    > By the way, you should have been using the AfterUpdate event, not the
    > Change event for what you were attempting. The Change event fires every
    > time the text in the textbox changes (i.e. with every keystroke). The
    > AfterUpdate event fires only when the value of the textbox is changed -
    > when editing the contents is complete.
    > --
    > Good Luck!
    >
    > Graham Mandeno [Access MVP]
    > Auckland, New Zealand
    >
    >
    > "Simon Harris" <too-much-spam@makes-you-fat.com> wrote in message
    > news:v_2fg.5173$O7.3183@newsfe5-win.ntli.net...
    >> Hi All,
    >>
    >> I have an on change event on a text box which does the following:
    >>
    >> If Me.GasMeterType.Value = "m²" Then
    >> Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    >> DLookup("Gas_Per_Mtr3", "tbl_constants")
    >> ElseIf Me.GasMeterType.Value = "ft²" Then
    >> Me.GasAmount.Value = (Me.Gas_Reading - Me.Gas_Reading_Previous) *
    >> DLookup("Gas_Per_Ft3", "tbl_constants")
    >> Else
    >> MsgBox ("Warning: No gas meter type recorded for this pitch")
    >> End If
    >>
    >> Trouble is, when fired, this updates the GasAmount field for all
    >> records...not just the current record as I expected. The form is normally
    >> in data sheet mode, although I have tried continuous form, which causes
    >> the same problem.
    >>
    >> Odd? Or am I not understanding something here?
    >>
    >> Thanks,
    >> Simon.
    >>
    >> --
    >> -
    >> * Please reply to group for the benefit of all
    >> * Found the answer to your own question? Post it!
    >> * Get a useful reply to one of your posts?...post an answer to another
    >> one
    >> * Search first, post later : http://www.google.co.uk/groups
    >> * Want my email address? Ask me in a post...Cos2MuchSpamMakesUFat!
    >>

    >
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 5263 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     

Share This Page