Dlookup (AGAIN!!)

[Guest]

I have a form (TIME SHEET ENTRY) that I can enter employee name, where he
worked, the hours he worked and the week.

The employee name is a combo box. The name is looked up but the employee
number (which is text) is stored in the table.

I am trying to get Dlookup to automatically put in the hourly rate from the
EMPLOYEE table.

I have used CTRL G to check the following
=DLookUp("[HOURLY]","EMPLOYEE","[EmpNo] = '" & Forms![TIME SHEET
ENTRY]![NAME]& "'")

And this works fine until I try and put it on the form. I get a blank entry
in Hourly rate.

I am probably doing something very stupid!!! It's taken me so long to get
the formula to work and now I want to scream!!!

Please help me

Thanks

 

[Keith Wilby]

And this works fine until I try and put it on the form.

Where exactly are you putting it?

Keith.

 

[Guest]

Good question.... I have tried it in
Control Source
Default Value
On Enter
After Update

And none of these work

 

[Keith Wilby]

Good question.... I have tried it in
Control Source
Default Value
On Enter
After Update

Try making it a calculated field in the form's query and then bind your
control to that field.

Keith.
www.keithwilby.com

 

[Guest]

I will give that a go

Thanks

Try making it a calculated field in the form's query and then bind your
control to that field.

Keith.
www.keithwilby.com

 

[Rick Brandt]

I have a form (TIME SHEET ENTRY) that I can enter employee name,
where he worked, the hours he worked and the week.

The employee name is a combo box. The name is looked up but the
employee number (which is text) is stored in the table.

I am trying to get Dlookup to automatically put in the hourly rate
from the EMPLOYEE table.

I have used CTRL G to check the following
=DLookUp("[HOURLY]","EMPLOYEE","[EmpNo] = '" & Forms![TIME SHEET
ENTRY]![NAME]& "'")

And this works fine until I try and put it on the form. I get a
blank entry in Hourly rate.

I am probably doing something very stupid!!! It's taken me so long
to get the formula to work and now I want to scream!!!

Please help me

Thanks

Forget DLookup(). Instead add that field as a hidden column to your
ComboBox. Then you can refer to it with...

Me.ComboBoxName.Column(n)

....where 'n' is the zero-based column position that you want the value for.
In your case I assume this would be the third column or .column(2).

Now you have a decision. If the hourly rate might change and you need
records to reflect the hourly rate at the time the record was created, then
you need to copy that value into your record. For that use the AfterUpdate
event of the ComboBox...

Me.HourlyRate = Me.ComboBoxName.Column(2)

However; if you want this to be a lookup so that you always see the CURRENT
hourly rate (even on older records) then you don't need any code. Just use
a TextBox with a ControlSource property expression of...

=ComboBoxName.Column(2)

 

[Tom Lake]

I have a form (TIME SHEET ENTRY) that I can enter employee name, where he
worked, the hours he worked and the week.

The employee name is a combo box. The name is looked up but the employee
number (which is text) is stored in the table.

I am trying to get Dlookup to automatically put in the hourly rate from
the
EMPLOYEE table.

I have used CTRL G to check the following
=DLookUp("[HOURLY]","EMPLOYEE","[EmpNo] = '" & Forms![TIME SHEET
ENTRY]![NAME]& "'")

Does the NAME field actually hold an EmpNo?

Tom Lake