Dlookup (AGAIN!!)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andi C - 17 Jul 2007 08:26 GMT

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

Reply to this Message

Keith Wilby - 17 Jul 2007 08:49 GMT

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

Where exactly are you putting it?

Keith.

Reply to this Message

Andi C - 17 Jul 2007 09:10 GMT

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

And none of these work

> > And this works fine until I try and put it on the form.
>
> Where exactly are you putting it?
>
> Keith.

Reply to this Message

Keith Wilby - 17 Jul 2007 09:46 GMT

> 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

Reply to this Message

Andi C - 17 Jul 2007 10:10 GMT

I will give that a go

Thanks

> > Good question.... I have tried it in
> > Control Source
[quoted text clipped - 7 lines]
> Keith.
> www.keithwilby.com

Reply to this Message

Rick Brandt - 17 Jul 2007 13:16 GMT

> I have a form (TIME SHEET ENTRY) that I can enter employee name,
> where he worked, the hours he worked and the week.
[quoted text clipped - 18 lines]
>
> 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)

Signature

Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Reply to this Message

Tom Lake - 17 Jul 2007 14:06 GMT

>I have a form (TIME SHEET ENTRY) that I can enter employee name, where he
> worked, the hours he worked and the week.
[quoted text clipped - 9 lines]
> =DLookUp("[HOURLY]","EMPLOYEE","[EmpNo] = '" & Forms![TIME SHEET
> ENTRY]![NAME]& "'")

Does the NAME field actually hold an EmpNo?

Tom Lake

Reply to this Message