Help with DLookup/ I'm stumped!

[Don]

Can someone help me fix my DLookup problem. I'm far from proficiency with
Access. I've been creating databases for several years for work with the
help of many of you and trial and error.
I have used DLookup several times and I've actually re-created the same
lookup just in different forms.
Here's my problem.
I'm using dlookup when I enter a part description it adds the part number
for me. I choose the part description from a combo box. This is how I've
done it several times in other tables.
In all the other instances when I enter the part description and move to the
next control (?term), or hit enter, the part number appears. In my present
situation the part number doesn't appear until after I've moved to the next
record. I'm using an Event Procedure in the AfterUpdate control (?term) as
I've done in all the other instances. It's just that this one works
differently.
Can anyone tell me why? I realize that there's probably more I should tell
you but I don't know what that is. If you ask me for more info I'll do my
best to provide it. Here's the code I'm using:
Thanks,
Don............

Private Sub Casting_AfterUpdate()
On Error GoTo Err_Casting_AfterUpdate

Dim strFiter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "Casting = " & Me!Casting

'Look up castings partID and assign it to part# control.
Me!PartID = DLookup("[PartID]", "Casting Table", "[Casting] = '" &
Me!Casting & "'")

Exit_Casting_AfterUpdate:
Exit Sub

Err_Casting_AfterUpdate:
MsgBox Err.Description
Resume Exit_Casting_AfterUpdate

End Sub

[John Welch]

It looks like you might do better with a 2 column combo box. The first
column will hold the partID and the second column will be the description.
When you set up the combo box, in the properties dialog, under format, set
column count to 2, then for the rowsource of the combo box use something
like "select partID, Description from [Casting Table]. In the column widths
for the combo box (also under format), you can say 0;4 (these are inches),
and the first column will be invisible. (or if you don't want it invisible,
give the first column a nonzero width, so something like 1;4) Then set the
bound column (under data) property to 1, and the combo box will actually
store the partID even though all you see is the description. Then set the
control source of the combobox to be PartID.
hope this helps
-John

"Don" <vz******@verizon.net> wrote in message
news:wHw6g.922$0v6.1@trndny05...

Can someone help me fix my DLookup problem. I'm far from proficiency with
Access. I've been creating databases for several years for work with the
help of many of you and trial and error.
I have used DLookup several times and I've actually re-created the same
lookup just in different forms.
Here's my problem.
I'm using dlookup when I enter a part description it adds the part number
for me. I choose the part description from a combo box. This is how I've
done it several times in other tables.
In all the other instances when I enter the part description and move to
the
next control (?term), or hit enter, the part number appears. In my
present
situation the part number doesn't appear until after I've moved to the
next
record. I'm using an Event Procedure in the AfterUpdate control (?term)
as
I've done in all the other instances. It's just that this one works
differently.
Can anyone tell me why? I realize that there's probably more I should
tell
you but I don't know what that is. If you ask me for more info I'll do my
best to provide it. Here's the code I'm using:
Thanks,
Don............

Private Sub Casting_AfterUpdate()
On Error GoTo Err_Casting_AfterUpdate

Dim strFiter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "Casting = " & Me!Casting

'Look up castings partID and assign it to part# control.
Me!PartID = DLookup("[PartID]", "Casting Table", "[Casting] = '" &
Me!Casting & "'")

Exit_Casting_AfterUpdate:
Exit Sub

Err_Casting_AfterUpdate:
MsgBox Err.Description
Resume Exit_Casting_AfterUpdate

End Sub

[Arno R]

"Don" <vz******@verizon.net> schreef in bericht news:wHw6g.922$0v6.1@trndny05...

Can someone help me fix my DLookup problem. I'm far from proficiency with
Access. I've been creating databases for several years for work with the
help of many of you and trial and error.
I have used DLookup several times and I've actually re-created the same
lookup just in different forms.
Here's my problem.
I'm using dlookup when I enter a part description it adds the part number
for me. I choose the part description from a combo box. This is how I've
done it several times in other tables.
In all the other instances when I enter the part description and move to the
next control (?term), or hit enter, the part number appears. In my present
situation the part number doesn't appear until after I've moved to the next
record. I'm using an Event Procedure in the AfterUpdate control (?term) as
I've done in all the other instances. It's just that this one works
differently.
Can anyone tell me why? I realize that there's probably more I should tell
you but I don't know what that is. If you ask me for more info I'll do my
best to provide it. Here's the code I'm using:
Thanks,
Don............

Private Sub Casting_AfterUpdate()
On Error GoTo Err_Casting_AfterUpdate

Dim strFiter As String

'Evaluate filter before it's passed to DLookup function.
strFilter = "Casting = " & Me!Casting

'Look up castings partID and assign it to part# control.
Me!PartID = DLookup("[PartID]", "Casting Table", "[Casting] = '" &
Me!Casting & "'")

Exit_Casting_AfterUpdate:
Exit Sub

Err_Casting_AfterUpdate:
MsgBox Err.Description
Resume Exit_Casting_AfterUpdate

End Sub

The code should work IMO, but please check a few things:
-- You create a var like strFilter but you are not actually using strFilter.
Also strFilter is wrong (Casting seems to be a string, so needs extra quotes)
-- If you were to use strFilter indeed you could use code like:
Me!PartID = DLookup("[PartID]", "Casting Table", strFilter)
-- In that case strFilter needs to be changed to:
strFilter = "[Casting] = '" & Me!Casting & "'" (like you do in the where clause of DLookup)
In that case for clarity I would also use another name like strWhere instead of strFilter

So maybe I would use code like:

Private Sub Casting_AfterUpdate()
On Error GoTo Err_Casting_AfterUpdate

Dim strWhere As String
'Evaluate before it's passed to DLookup function.
strWhere = "[Casting] = '" & Me!Casting & "'"

'Look up castings partID and assign it to part# control.
Me!PartID = DLookup("[PartID]", "Casting Table", strWhere)
Exit_Casting_AfterUpdate:
Exit Sub

Err_Casting_AfterUpdate:
MsgBox Err.Description
Resume Exit_Casting_AfterUpdate

End Sub
BUT your code is not working....
-Is Me!PartID the name of your control or ?? (try changing the name)
Is this control (PartID) bound or unbound ??
-If you test the DLookup in the Immediate window what does it say ??

Arno R