Newbie Question: Calculated Field on Form

[darrelhawes]

Hi,

I'm trying to populate a field with wage data. I have a field linked to an "employee" table; the particular employee can be selected from the drop-down box. I then enter the hours worked in the next field. Then I want Access to look back in the employee table, find the wage rate for that employee, multiply it by the hours worked value that I just entered, and enter the result into the "wages" field (linked back to a transaction table).

In my research thus far, I haven't been able to figure out how to accomplish this. What is the best way to make this happen?

Thanks,

Darrel

 

[scriverb]

Add an additional column to the RowSource of your Combobox. Add 1 to the column count, set the Width property of that column to 0. Put the following VBA code in After Update event procedure of the Hours worked control:

Me![Wages] = Me![HoursWorked] * Me![cboEmployeeSelect].Column(1)
The column number in red should relate to the column of theRowSource SQL which relates to the WageRate. Columns start counting at zero(0). This is why I entered a 1 for the column in this example. Change it if this field is farther down the list of selected fields.

Post back with questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

Bob,

I don't understand how to add an additional column to the RowSource of the Combobox. Thanks for your patience and help.

Darrel

 

[scriverb]

No problem. Open up the Properties window of the combobox.(Right click and select Properties). There is a RowSource property near the top of the list, about 5-6 items down. You either have a table, query, or sql indicated here. Post back with what you have. If a query or SQL post the SQL and I will modify the code for you.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

Thanks for your answer. I was away for several days and not able to work on this database until now. Problem: when I right click on the combo box on my form, I don't see a RowSource field. Am I supposed to be on the form or on a table?

 

[darrelhawes]

** Disregard last message **. I see what I was doing wrong. I DO have the RowSource field but after I add one to my column count, I don't see how to set the property of that column to zero.

The code that is in the RowSource field of the Employee combo box is:

SELECT [Employee].[Employee Initials] FROM Employee;

I hope I've given you enough information. Thanks so much.

Darrel

 

[scriverb]

Change your SQL in the RowSource to the following updating the Wage_Rate field for the correct spelling of the fieldname:

SELECT [Employee].[Employee Initials], [Employee].[Wage_Rate] FROM Employee;

Now in the Properties find the Column Count. Change this to 2. Now find the Bound Column. Set to 1. Now find the ColumnWidths. Set to 1;0 .

This should setup the combo so that the rest of the code that I provided will work.

Post back with additional questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

I entered the code as directed. Now when I click on the employee box, I received the prompt to "enter parameter value" for employee hours. I just entered a number and pressed enter, and then the list of employees appears. I chose an employee and entered a value in the employee hours box, and received the error message:

"Can't find macro 'Me![Wages]=Me![Emp Hrs]*Me!cboEmployee.Select'"

Perhaps I was supposed to insert something to replace "Me"?

Darrel

 

[scriverb]

Post the code behind the AfterUpdate of the Combobox.



Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

Bob,

Thanks for your patience. I'm not sure what I'm doing incorrectly. I posted:

Me![Wages]=Me![Emp Hrs]*Me![cboEmployeeSelect].Column(1)

In the AfterUpdate field in the Hours Worked control.

Is there something I missed?

Darrel

 

[scriverb]

Is Me![Emp Hrs] the hours worked control that you have put the VBA code in its After Update event procedure? If so, what is the value showing up in the Me![Wages] control? If nothing then try running the SQL for the Row Source of the Combobox all by itself. Copy and paste it into a new query and just run it. There should be two columns. First Initials then the [Wage Rate]. If this is so then I would put a STOP command in the AfterUpdate of the [Wages] control just in front of the expression. Then using the immediate or debugger window find out just what is in each of the controls and the combobox column(1).

This can be done by either just holding your cursor over each of the control names like Me![Emp Hrs] or Me![cboEmployeeSelect].Column(1). A mouse over value will show right there in your code. There should not be any null values. This is what i am suspecting is happening.

Post back with the results of your findings.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

Before I do that, would you explain what the "Me" is supposed to represent? Was I supposed to replace "Me" with the name of a table?

 

[scriverb]

VBA code within a form or a report can refer to a control within that collection as Me![ControlName]. The Me! is the same as using the long form [FORMS]![frmFormName]! . It is just a shorthand version of the long version.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

I copied the query and ran it. I received the same result; i.e. Dialog box asking me to Enter Parameter Value" Employee.Emp Hrs.

I opened the query in design mode and found the following:

Column 1 Field: Employee Initials
Column 1 Table: Employee

Column 2 Field: Expr1: [Employee].[Emp Hrs]

Does that help explain what's wrong?

 

[scriverb]

Yes, your second column is trying to display a field called Emp Hrs. Here is the SQL that I posted for you to use. The second column should be Wage Rate. Just select from the dropdown for the field the Wage Rate field. This will match the SQl that I posted. Now run your form.

SELECT [Employee].[Employee Initials], [Employee].[Wage_Rate] FROM Employee;

Post back with questions.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

OK, I made that correction on the SQL statement. Now I am able to select the employee from the combo box, and enter the number of hours. When I tab out of the hours field, I receive the following error message:

"The expression AfterUpdate you entered as the event property setting produced the following error: The object doesn't contain the Automation object 'Me.'."

The statement in the AfterUpdate field of the Hours control is:

=Me!Wages=Me![Emp Hrs]*Me!cboEmployeeSelect.Column(1)

I also tried changing the value after Column from 1 to 2 and 3 and received the same result.

I don't see what I'm doing wrong; any more ideas?

Thanks,

Darrel

 

[scriverb]

The statement should read like this in the AfterUpdate event procedure:

Me![Wages] = Me![Emp Hrs] * Me![cboEmployeeSelect].Column(1)

You had an equals sign(=) in front of the statement. When you are creating a calculated control an equal sign precedes the expression when entered into the control Source but not in VBA in an event procedure. Also the proper syntax is indicated above when referencing controls on a form. Use it as I have stated and you will never have any problems with syntax.

Post back with questions or concerns.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darrelhawes]

Bob,

For some reason, my version of Access (2000) did not know what to do with the "Me" shorthand. I must have done something else wrong. It kept adding the equal sign in the beginning of the statement. However, by studying one of Microsoft's sample databases, I was able to finally make it work. Here's what I did:

(Note that I took the spaces out of the control names):

1. The Employee Combo box contains the following statement:

SELECT [Employee].[EmployeeInitials], [Employee].[EmployeeName], [Employee].[WageRate] FROM Employee;

I set up the properties so that the employee name and wage rate are visible in the drop-down box, along with the initials of the employee.


2. The Wages control now contains the following statement in the ControlSource:

=[Forms]![ProjectExpenses]![hours]*[Forms]![ProjectExpenses]![EmployeeInitials].column(2)

This has the effect of looking at the employee selected from the combo box and multiplying their wage rate times the hours worked field.

Thanks again for your patience with one who is trying to get up to speed. I'm sure I'll have more questions in the near future.

Darrel

 

[scriverb]

Looks like you got it right. Fantastic.

When you were getting that equals sign in the from of the expression the problem was that the code you were pasting in was the code you would use in a VBA event procedure. It included the form control to make the assignment of the expression value. You were trying to use it in the Control Source. What you should have been pasting into the control source was the Red Code only:
=Me!Wages=Me![Emp Hrs]*Me!cboEmployeeSelect.Column(1)

ACCESS would have put the equals sign in front of it and it would have worked.

Good luck and it was a pleasure working with you to solve this problem.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???