Updating a form from another form 1

[lsgls]

I have a database set up which calculates weekly wages of approx. 40 employees.

As it stands it works well, however, i am having an issue with a wage rate field.

Each week my end-user inputs the hours each employee has worked against the week no., period, employee id etc.

This data combined with wagerate field stored in tblemployees, calculates the wages in a report.

However, i wish to put the wage rate on my hours form. this is because, although the wage rate for each employee usually stays constant, it is liable to small changes throughout the year.

i want a wage rate to appear on my hours form as an employee id is selected (from a combo box).

i would like this wage rate to have the ability to be changed if there has been a pay rise, i would also prefer this second rate to be stored so that future reports will default to this rate, but previous reports will stay at the previous rate.

if anyone understands my problem please help! i'm wlling to give more information if anyone requires it.

 

[Remou]

Include the wage rate as an extra column in the combo then, when the employee is selected, write the data to the relevant control, for example:

[tt]'After update event for employee combo
Me.HoursWageRate=Me.cboEmployee.Column(2) 'Numbering from 0[/tt]

 

[lsgls]

i understand that i think, however, my main problem, which i dont think i explanined properly is that, currently my data is normalised and i have wage rate within tblemployees. therefore when you change this wage rate all reports change.

this is bad because should you require a report from a previous month of week and the wage rate has since been updated, it means the incorrect wage rate will be picked up.

i realise it would be best if my wage rate were in a seperate table with a week effective date, however, once set-up like this i am very unsure how to set up my report to check for the most current wage rate.

the reason i suggested having the wage rate field on my hours input form was a way of trying to get around the creation of what i just mentioned.

if you could guide me through setting up a report to look at the most recent wage rate from a seperate table, that would be great. many thanks

 

[Remou]

I think you will need to redesign the reports to use the wage rate from the hours table. The wage rate in the employees table can only be used as the default rate written to the hours table.

 

[lsgls]

if i set up a default within my tables it wants me to put a constant value, how can i set up a default based on the employee id selected? do i do this within my form or from the table

 

[Remou]

Try the suggestion in my first post with a form. You should also update the employees table if the rate is changed.

 

[lsgls]

ok, ive just attempted that, it all made sense however i think i may have done something wrong, i have input the code as per your comment and when i select an employee from the combo box it says the following warning :

Wages Database can't find the macro "Me."

any ideas?

 

[Remou]

You need to use the After Update event.

 

[lsgls]

i am using the after update event that is why i;m confused by the message

 

[Remou]

Please post the whole procedure. You do have a procedure, don't you? For example:

Sub cboEmployee_AfterUpdate()
  Me.HoursWageRate=Me.cboEmployee.Column(2) 'Numbering from 0
End Sub

You can get the Sub ... End Sub part by clicking the three dots to the right of After Update on the property sheet.

 

[lsgls]

Thanks Remou! its sorted, i'd actually done it right but i'd accidently typed one of the field names wrong! its all sorted now and works how i wanted it to.

many thanks