I have the following tables
1. tblActivities
2. tblEmpName with the fields IDNum and EmpName
3. tblTradeWorked with the fields WorkedFor and TradeWithIDNum

I have a main form frmEmpForm with a subform frmActivities. The main
form has a control button that is used to open frmTradeWorked.

When I enter a name in the control WorkedFor on the frmTradeWorked
which gets EmpName from tblEmpName, I want the control on the
frmTradeWorked entitled TradeWithIDNum to autofill with the correct
IDNum from the tblEmpName.

This is the code I am trying to use but it will not work.

Private Sub WorkedFor_AfterUpdate()
Me.TradeWithIDNum = DLookup("[IDNum]", "tblEmpName", "[EmpName]='" &
Me.[WorkedFor] & "'")
End Sub

Help is appreciated
Thanks
Ron

"A_Classic_Man" <rmerkel2 (AT) cox (DOT) net> wrote

It's a syntax problem. If you're going to use square brackets:

[WorkedFor]

it must be with the bang, not the dot, ie:

Me![WorkedFor]

However, since WorkedFor doesn't contain any spaces, you don't need the
brackets anyway (the same goes for IDNum and EmpName), so:

Me.TradeWithIDNum = DLookup("IDNum", "tblEmpName", "EmpName='" &
Me.WorkedFor & "'")

should do nicely.

A_Classic_Man wrote:

What do these 2 forms have to do with anything regarding your issue?
They add confusion to your problem since they are superfluous to the
rest of the description.


You could open a code module, and from the Immediate window do something
like

wf = "Joe Blow" 'put in valid name
? DLookup("[IDNum]", "tblEmpName", "[EmpName]='" & _
wf & "'")
Did you get a value? Or did you get a Null?

If you got a value then put the word
STOP
prior to your dlookup line and step thru the code with your form open.

On 19/01/2011 05:50:21, A_Classic_Man wrote:
Think it all depends on what TradeWithIDNum is. Is it an autonumber or just a
long number? Are there any relationships established between tblTradeWorked
and any other table? When you say you want to autofill what do you mean - are
you trying to add a new record or are you trying to find an existing record?
If you type DLookup("IDNum", "tblEmpName", "EmpName='" & Me!WorkedFor & "'")
in the immediate window, do you get an result? Note the removal of
unneccessary square brackets which you only need round fields with spaces in
them or if you are using reserved words as a field name (like [Date]) Phil

On Jan 19, 12:15*am, "Stuart McCall" <smcc... (AT) myunrealbox (DOT) com> wrote:
Thanks to all for the responses
Stuart, I tried your suggestion and still get nothing.
The IDNum fields are text fields and are size is set to 5 for both
fields.
TradeWithIDNum in the tblTradeWorked and IDNum in the tblEmpName are
the linked fields.

I have checked spelling of table and field names and all appear to be
correct.
Any other ideas what to look for. I have a feeling there is something
simple that I am missing

Ron

On Jan 19, 8:37*am, A_Classic_Man <rmerk... (AT) cox (DOT) net> wrote:
It's working now. The following code resolved the problem
Me.TradeWithIDNum = DLookup("IDNum", "tblEmpName", "IDNum='" &
Me.WorkedFor & "'")
Thanks to all for the help

Ron

The working code is exactly what I posted. Oh well, blame the gremlins.

On Jan 19, 11:58*am, "Stuart McCall" <smcc... (AT) myunrealbox (DOT) com> wrote:

Me.TradeWithIDNum = DLookup("IDNum", "tblEmpName", "EmpName='" &
Me.WorkedFor & "'") What you suggested
Me.TradeWithIDNum = DLookup("IDNum", "tblEmpName", "IDNum='" &
Me.WorkedFor & "'") What works

I'm not sure what is going on with this. The way I think this through
is:

"TradeWithIDNum" on the current form is the "IDNum" in "tblEmpName"
where "EmpName" in "tblEmpName" is equal to "WorkedFor" on the current
form.

I guess that thought process is incorrect. When I Changed "EmpName" to
"IDNum" the code works perfectly

Thanks

Ron