Want Field Updated Through Calculation in Other Field (Controls) 2

[JOEYB99]

I am using Access 2007 and struggling with what I think should be a simple action on a Form.

I have set up a form that reads all the records from a table. This is a result of a make table of payroll based transactions for billing purposes. I called this new table a WorkFile. My form is designed to allow the user to make changes to this data on-the-fly.

Anyway, I want my form to allow the user to change the number of hours. The corresponding rate value is locked in and can't be changed.

For example, there is originally 2.0 hours at a rate of $55.00/hour. The WorkFile will show $110.00 in an extension field, which is also displayed in my form.

The user changes the hours field/control to 3.0. I want to see a new value of $165.00 in the Extension field or control on the form, and I want this new value posted to the Extension field in the WorkFile table.

Any input/suggestions would be greatly appreciated.

 

[PHV]

In the AfterUpdate event procedure of the number of hours control:
Me![extension field] = Nz(Me![number of hours] * Me![rate], 0)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Thank you PHV but that didn't work.

I'm assuming you mean that Me! represents the current form, which I entered as Form!

Any other ideas?

 

[PHV]

which I entered as Form!
Why ?
Me is a special keyword in the form's class module.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Okay then, thanks PHV. But what are you saying? Did you provide me with VBA code, and I should enter it as that?

 

[PHV]

which I entered as Form!
So, what is your code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

JOEYB99
PH provided VBA code that would be entered into the Event Procedure

Private Sub [hours field/control]_AfterUpdate()
   [COLOR=#4E9A06]'assuming control and field names match, you may need to change[/color]
   Me![extension field] = Nz(Me![number of hours] * Me![rate], 0) 
End Sub

Don't enter the line of code provided into the After Update property since that expects either a macro name or [Event Procedure].

Duane
Hook'D on Access
MS Access MVP

 

[JOEYB99]

Thanks DHookom.

I have updated for the proper control/field names and set up the VBA code in a module. When I debug it I get an error message "Compile Error: Invalid Use of ME keyword." It then high-lights the second occurrence of ME, just after 'NZ('.

Why is that?

 

[PHV]

set up the VBA code in a module
Why ?
The code should go in the AfterUpdate event procedure of the hours control.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[JOEYB99]

Oh, okay got ya PHV. I'm fairly new to VBA and event procedures.

I have now input the Event Procedure with the VBA code as per DHookom in the AfterUpdate property of the Hours control.

Now, I'm getting an error message "Run-time error '2448': You can't assign a value to this object." When I click on Debug the following line is high-lighted in yellow, Me![Billable Regular Amount] = Nz(Me![Regular Hours] * Me![REG Rate], 0)

I have updated for the proper control names.

What is wrong? Do I need to pre-fix these with their table names?

 

[PHV]

What is the ControlSource of [Billable Regular Amount] ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886