"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