472,374 Members | 1,367 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,374 software developers and data experts.

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
May 5 '06 #1
2 2189
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

May 5 '06 #2

"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
May 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Kalvin Schroder | last post by:
I am fairly new to Access, and am trying to put together an invoice form. The main form in called InvoiceDetailFm. Source is the table InvoiceDetail and has invoice number, saleman, and CustID as...
3
by: Tempy | last post by:
Hi all, i am running into problems with a DLookUp below: =DLookUp("","tblBuyerTotals"," =' " & me!LastName) This is in the Control Source of a text box on the same form. "LastName" is a text...
4
by: basstwo | last post by:
I have a field with a serial number in it. I want to use Mid to extract the 4th and 5th characters, use them to lookup a value on a small lookup table, and use the info from that table to fill in...
5
by: kevinjouco | last post by:
Hello Have searched the group for a solution to the following problem without success: Table 1 has Ref No (No Duplicates) & Min Max Value Fields ie Ref No 1 Min 1 Max 10 Ref No 2 Min 11 Max...
6
by: Don Sealer | last post by:
I've written this expression for a DLookup function. It works almost alright. What I'm trying to do is type in a description and the ID field (number) populates automatically. It works almost as...
6
by: deejayquai | last post by:
Hi I'm attempting to append multiple values into a new record, using multiple criteria from a listbox. I've got the basics for the code below but I get an "Error 3085 Undefined Function" for...
21
by: Thelma Lubkin | last post by:
I would like my DLookup criteria to say this: Trim(fieldX) = strVar: myVar = _ DLookup("someField", "someTable", "Trim(fieldX) = '" & strVar & '") I don't believe that this will work, and I...
2
by: Simon | last post by:
Hi, I need some help form someone. I use my database for my online shop, so once I have entered a order onto the database I have a button that creates the Email to the customer to let them know...
1
by: aharding | last post by:
Hello! I am using Access 2003 I have been playing with DLookup all morning and have not been successful yet. I have never used this function...and have some limited experience with code. I...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: Johno34 | last post by:
I have this click event on my form. It speaks to a Datasheet Subform Private Sub Command260_Click() Dim r As DAO.Recordset Set r = Form_frmABCD.Form.RecordsetClone r.MoveFirst Do If...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...
0
by: F22F35 | last post by:
I am a newbie to Access (most programming for that matter). I need help in creating an Access database that keeps the history of each user in a database. For example, a user might have lesson 1 sent...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.