autopopulate and update in form

[gscma1]

I have a value stored in one table that i wish to transfer to another form

I have tblEmployees this stores the employee name, wage rate etc.

I have tblHours this is used each week to input employee wages - the empployee id is entered, the hours worked, the week number etc.

i want tblHours to pick up the wage rate from tblEmployees and display in a field in tblHours as per the employee id entered.

Although, it is possible the rate could change therefore i would like this field to be changed if necessary.

therefore my report for the weeks wages would work from the wage rate in tblHours not the wage rate in tblEmployees.

the only reason i want a wage rate to be in tblEmployees is so that each week my end-user doesn't have to input a wage rate there will be a default from tblEmployees. this ensures less mistakes as all employees are on varying wage rates.

hope its clear what i want to do, and if anyone knows how i can do please can they help me because i'm baffled.

many thanks

 

[randy700]

Not sure I understand your question entirely, but I think the DLOOKUP function would do what you want.


Randy

 

[djwatts]

Yeah I think DLOOKUP is what you need, something like.

tblHours.wage = DLOOKUP("Wage", "tblEmployee", "[EmployeeID]=" & CurrentID)

Should be able to put that as a control source property for a txt box etc. Where CurrentID is whatever your using to specify which employee its for. If CurrentID is a string value not numerical you will need -

& Chr(34) & CurrentID & Chr(34)

 

[gscma1]

thanks, in my query i now have

Wage Rate: DLookUp("Wage Rate","tblEmployees","[EmpId]=" & [EmpId])

i can't spot whats wrong with this but it is returning #Error in the field of the form.

any ideas?

thanks

 

[randy700]

How about...

Wage Rate: DLookUp("[COLOR=red][[/color]Wage Rate[COLOR=red]][/color]", "tblEmployees", "EmpID = " & [COLOR=red]YourFormName.[/color]EmpID)

Randy

 

[gscma1]

WageRate: DLookUp([Wage Rate],"tblEmployees","[EmpId]=" & HoursForm.EmpId)

This now seems to bring up an input box which says, when i try to open the query.

hoursform.empid:

then it asks for my week no. - this is set up to do so anyway.

i have tried different variations including brackets, square brackets etc, but nothing seems to work.

 

[gscma1]

i have managed to remove that problem, and my code now stands like this:

Wage Rate: DLookUp("[Wage Rate]","tblEmployees","[EmpId] =" &[HoursForm].[EmpId])

the only problem now is that within the wage rate field on the form, it now says : #Name?

any ideas?

 

[djwatts]

Are the data types in both fields the same? The data type for the wage fields in the Employee & Hours tables need to be the same...

Try running:

MSGBOX DLookUp("[Wage Rate]","tblEmployees","[EmpId] =" &[HoursForm].[EmpId])

And see if it returns the value that you're expecting. This should verify the lookup is working correctly.

 

[gscma1]

data types are both currency.

field is called Wage Rate in tblEmployees and tblHours (tblHours is the table the HoursForm is based on)

the HoursForm also opens using a parameter query asking for the week no.

do you think this is why i'm having a problem should the dlookup be in the query stage not the form?? if so where?

 

[djwatts]

Did the DLOOKUP return the correct value?

Have you tried editing the data field in the query manually through dataview? Some queries won't let you edit related records.

It shouldn't really matter whether you lookup in the table of in the query.

 

[gscma1]

it is not returning any value just #Name?

i will try editing it through dataview and see what results that returns

thanks

 

[djwatts]

I assume that both the EmpID fields are the same data type also?

Try changing the DLOOKUP to:-

DLookUp("[Wage Rate]","tblEmployees","[EmpId] =" & Forms![HoursForm]![EmpId])

 

[randy700]

Try this...

Wage Rate: DLookUp("[Wage Rate]", "tblEmployees", "EmpID = " & [COLOR=red]Forms![/color]HoursForm.EmpID)

If EmpID is NOT numeric...

Wage Rate: DLookUp("[Wage Rate]", "tblEmployees", "EmpID = [COLOR=red]'[/color]" & Forms!HoursForm.EmpID [COLOR=red] & " ' "[/color])

Randy