Dlookup

I have a form to add new info. and want a field "empcode" to be auto
filled base on the Name selected.
I have the code on the After Update Event Procedure below, but it
didn't do anything.
Could someone help me what was wrong on the code or should I put the
code somewhere else?
Thanks a lot!!

Private Sub TerrCode_AfterUpdate()
Me.empCode = DLookup([code], "DTNEmp", "[ID]=' " & Me![Emp] & " '")
Me.Requery
End Sub

Orchid,

There is an error in the DLookupo expression, in that you need ""s
around the first argument. It also assumes that the ID field is Text
data type, is that correct?

Also, the Requery will not achieve anything, and can be removed.

Private Sub TerrCode_AfterUpdate()
Me.empCode = DLookup("[code]","DTNEmp","[ID]='" & Me![Emp] & "'")
End Sub

Having said that, there is a question about the validity of doing this
in the first place. One of the fundamental rules of good database
design is that each value should be stored once only on one field in one
table. It looks like you might be flouting this rule here. The Code
corresponding with the ID is already defined within your database in the
other table, so if I am understanding correctly, all you really need
here is to *display* the value on the form, not to write it to a field.
In that sense, what you are doing is very unusual.

There are a number of approaches that may be nore applicable. This
article may help:
http://accesstips.datamanagementsolutions.biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP

Orchid wrote:
I have a form to add new info. and want a field "empcode" to be auto
filled base on the Name selected.
I have the code on the After Update Event Procedure below, but it
didn't do anything.
Could someone help me what was wrong on the code or should I put the
code somewhere else?
Thanks a lot!!

Private Sub TerrCode_AfterUpdate()
Me.empCode = DLookup([code], "DTNEmp", "[ID]=' " & Me![Emp] & " '")
Me.Requery
End Sub

On Apr 16, 6:21*pm, Steve Schapel wrote:
Orchid,

There is an error in the DLookupo expression, in that you need ""s
around the first argument. *It also assumes that the ID field is Text
data type, is that correct?

Also, the Requery will not achieve anything, and can be removed.

Private Sub TerrCode_AfterUpdate()
* *Me.empCode = DLookup("[code]","DTNEmp","[ID]='" & Me![Emp] & "'")
End Sub

Having said that, there is a question about the validity of doing this
in the first place. *One of the fundamental rules of good database
design is that each value should be stored once only on one field in one
table. *It looks like you might be flouting this rule here. *The Code
corresponding with the ID is already defined within your database in the
other table, so if I am understanding correctly, all you really need
here is to *display* the value on the form, not to write it to a field.
* In that sense, what you are doing is very unusual.

There are a number of approaches that may be nore applicable. *This
article may help:http://accesstips.datamanagementsolutions.biz/lookup.htm

--
Steve Schapel, Microsoft Access MVP



Orchid wrote:
I have a form to add new info. and want a field "empcode" to be auto
filled base on the Name selected.
I have the code on the After Update Event Procedure below, but it
didn't do anything.
Could someone help me what was wrong on the code or should I put the
code somewhere else?
Thanks a lot!!

Private Sub TerrCode_AfterUpdate()
Me.empCode = DLookup([code], "DTNEmp", "[ID]=' " & Me![Emp] & " '")
Me.Requery
End Sub- Hide quoted text -

- Show quoted text -

Thanks for your reply, Steve!
I tried your code, but still nothing show for the field "empcode".

To make sure what I have is correct on the Dlookup formula:
-- a table called "DTNEmp" with columns: ID, Code,...
JD 001
JT 002
GA 003
-- a form to input new info. with fields: Emp, empCode,...

so if I have JD on Emp column, 001 should be auto filled on empCode
column.

By the logic I had, what did I do wrong?
By the way, it is possible to Dlookup form a query instead of a table?

I see what you are saying and thanks for pointing out. I am just
using the field name as example. In fact on my database, I may have
some info for an empCode without ID. However, for the ones with ID, I
don’t want to input but automatically fill in for me.


Your help is greatly appreciated!

Orchid,

Yes, you can use the DLookup function against a query.

From what you have said, your logic seems to be correct. As I
mentioned before, your original expression was missing the ""s.

The only thing I noticed is that the code you gave is running on the
After Update event of "TerrCode" and you haven't so far indicated what
TerrCode is, and how its After Update event gets triggered.

The other thing that has crossed my mind is that you may be entering the
Emp via a combobox, in which case we need to check that you have this
set up correctly.

--
Steve Schapel, Microsoft Access MVP

Orchid wrote:

I tried your code, but still nothing show for the field "empcode".

To make sure what I have is correct on the Dlookup formula:
-- a table called "DTNEmp" with columns: ID, Code,...
JD 001
JT 002
GA 003
-- a form to input new info. with fields: Emp, empCode,...

so if I have JD on Emp column, 001 should be auto filled on empCode
column.

By the logic I had, what did I do wrong?
By the way, it is possible to Dlookup form a query instead of a table?