473,903 Members | 4,300 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_AfterUp date()
On Error GoTo Err_Casting_Aft erUpdate

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_Af terUpdate:
Exit Sub

Err_Casting_Aft erUpdate:
MsgBox Err.Description
Resume Exit_Casting_Af terUpdate

End Sub
May 5 '06 #1
2 2279
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******@veriz on.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_AfterUp date()
On Error GoTo Err_Casting_Aft erUpdate

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_Af terUpdate:
Exit Sub

Err_Casting_Aft erUpdate:
MsgBox Err.Description
Resume Exit_Casting_Af terUpdate

End Sub

May 5 '06 #2

"Don" <vz******@veriz on.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_AfterUp date()
On Error GoTo Err_Casting_Aft erUpdate

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_Af terUpdate:
Exit Sub

Err_Casting_Aft erUpdate:
MsgBox Err.Description
Resume Exit_Casting_Af terUpdate

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_AfterUp date()
On Error GoTo Err_Casting_Aft erUpdate

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_Af terUpdate:
Exit Sub

Err_Casting_Aft erUpdate:
MsgBox Err.Description
Resume Exit_Casting_Af terUpdate

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
3208
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 some of its fields. I have put together a combobox, named it CustID, and am trying to use DLookup() to bring the complete Customer Name etc onto the form using DLookup() function for the fields I need. The combobox will bring up the CustID in...
3
4223
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 box on the form, but i get an error message ("The object doesn't contain the Automation object 'me.') and then ("You tried to run a visual basic procedure to set a property or method
4
2510
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 the value for my lens field. I tried doing this: =DLookUp(!,,Mid(!,4,2)=!) Where LensLookup is my lookup table, LensName is the value I want to have appear
5
2343
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 20 etc Table 2 is linked by Ref No (Allows Duplicates) & Recorded Value
6
508
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 I've said. It doesn't populate until I go to a new record. Then if I go back to the record the number is in the field. Instead of populating immediately it waits until I've created a new record. Am I making sense with this description. Here's...
6
5798
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 the Dlookup part of it. Dim db As Database Dim rec As DAO.Recordset
21
3389
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 won't be at a machine with access to Access for a while, so can someone please tell me how to write this? thanks, --thelma
2
1543
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 the order has been placed. I use to use as a auto number but I have just changed it to Which is not a auto number as I type them in. But now my VB code for the email button does not work.
1
1810
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 will post what I have tried so far and hopefully someone can help me problem solve. I have tried three different methods... (I know Calculation is spelled wrong..I have to go through my database and fix it everywhere just haven't had the time...
0
10003
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
10988
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10504
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9692
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
8055
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7213
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
6099
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4732
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3327
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.