This bombs on the StPL= line. I want it to look up a string value from a table with a criterion of matching another string. In immediate mode the form evaluates correctly with ? Form![wo complete]![4Item] as TEST but it returns a null in when executed. I have tinkered with syntax for way too long and would like to get past this part of a project. The lines with """"TEST"" all return a value stored under TEST in the file. -
Private Sub WO_AfterUpdate()
-
Dim stDate As Date
-
Dim stCust As String
-
Dim stOrd As String
-
Dim stItem As String
-
Dim stQty As Integer
-
Dim stPL As String
-
Dim ststdum As String
-
Dim ststdprice As String
-
Dim ststdcost As String
-
-
stDate = DLookup("[WODate]", "[WO table]", "[WO]=[woid]")
-
stCust = DLookup("[Customer]", "[WO table]", "[WO]=[woid]") & " "
-
stOrd = DLookup("[salesord]", "[WO table]", "[WO]=[woid]") & " "
-
stItem = DLookup("[itemno]", "[WO table]", "[WO]=[woid]")
-
stQty = DLookup("[qty]-nz([scrapqty])", "[WO table]", "[WO]=[woid]")
-
-
-
[Forms]![wo complete]![1Date] = stDate
-
[Forms]![wo complete]![2Cust] = stCust
-
[Forms]![wo complete]![3Ord] = stOrd
-
[Forms]![wo complete]![4Item] = stItem
-
[Forms]![wo complete]![5Qty] = stQty
-
stPL = DLookup("[ProductLine]", "[IM1_InventoryMasterfile]", "[ItemNumber] = 'Forms![wo complete]![4Item]'")
-
ststdum = DLookup("[StdUM]", "[IM1_InventoryMasterfile]", "[ItemNumber] = ""TEST""")
-
ststdprice = DLookup("[StdPrice]", "[IM1_InventoryMasterfile]", "[ItemNumber] = ""TEST""")
-
ststdcost = DLookup("[StdCost]", "[IM1_InventoryMasterfile]", "[ItemNumber] = ""TEST""")
-
[Forms]![wo complete]![ProductLine] = stPL
-
[Forms]![wo complete]![StdUM] = ststdum
-
[Forms]![wo complete]![StdPrice] = ststdprice
-
[Forms]![wo complete]![StdCost] = ststdcost
-
-
-
End Sub
-
Thank you for your consideration and help.
8 2375 Rabbit 12,516
Recognized Expert Moderator MVP
'Forms![wo complete]1[4Item]'
You have a 1 in there instead of a !.
I changed it in the code and it still doesn't work, and edited the post to reflect the change.
Thank you for looking through the code and taking the time to help. I have been beating my head against a wall and retyping this with different syntaxes for some time.
Rabbit 12,516
Recognized Expert Moderator MVP
Remove the single quotes.
Removed the single quotes. Still gives a null error.
Thanks again.
I wounder if IM1_InventoryMa sterfile being a linked file which is read only is affecting this? It evaluates correctly when I substitute in the ""TEST"" and reads the test data but doesn't seem to work with any of the standard string methods even though it evaluates in Ctrl-g as ? [Forms]![wo complete]![4Item] being equal to TEST.
Rabbit 12,516
Recognized Expert Moderator MVP
What's the full text of the error message? If you're getting a message on null, Try the Nz function.
Run time error '94':
Invalid use of null
And that error message was after I changes the [Forms]![wo complete]![4Item] to Nz([fORMS]![wo complete]![4Item])
In immediate mode ? Nz[4Item]
evaluates to test as it should but doesn't in the expression.
Rabbit 12,516
Recognized Expert Moderator MVP
Well, you need to specify what to use instead of Null. Use, Nz([fORMS]![wo complete]![4Item], "")
I tries somethink that worked. I still don't understand why it worked but I changed the line to
stPL = DLookup("[ProductLine]", "[IM1_InventoryMa sterfile]", "[ItemNumber] = [4Item]") & Mid(stItem, 1, 0)
It may be kludgy, and I haven't checked to see what kind of performance hit it gives but for the time being I can move along. Thank you for all the help. I was stuck in a rut and really needed some new ideas on how to approach the problem. It seems to be an odd way to force the criterion into a string mode where all the text book answers didn't work. I really appreciate the feed back and your time. This is one of the wierdest problems I have seen in a while.
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Norman Fritag |
last post by:
Hi there,
I am wonder why this dlookups return null, despite having a record in the
table with a matching date?
Am I missing something here???
Am i running in some limitations, that I am not aware of??
Any hint is much appreciated.
regards
|
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
|
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...
|
by: Don |
last post by:
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...
|
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
| |
by: Loismustdie129 |
last post by:
I don't really know if this is right to ask this here or not (and if it isn't please correct me) but I was wondering if you guys could help me learn about databases and MySQL.
I have googled this a thousand time and still I don't have a concrete answer but, what do you do with a database like MySQL? and, how do you connect and interact with it?
That is all the questions I have, if anyone could give me any help at all that would be great,...
|
by: db2 |
last post by:
Hi,
I have heard that stored procedure on Z/OS can be written on
assembler language only. Can any body could help me out on DB2 stored
procedure on Z/OS. If you recommend me any materials, that would be
great helpful for me.
Thanks,
Joseph
http://db2examples.googlepages.com
|
by: moon22 |
last post by:
Hi every one...
I have a project that takes data from a ZigBee wireless microcontroller (the code is written in C) and safe the data into a file through a ConnectPort X Gateway that support python...
I don't know how to start...
if anyone could help please...
|
by: QC |
last post by:
Hi Friends,
I have one Application running on Client PC, i coded to store all
Debug, Info, and Trace related information in Log file. This log file
helps me to analyze the exception if any generated in my Application.
Related to exception, i only stores information passed Exception (or
related exception type class) class (ToString(), Message property).
After analyzed and corrected so many exceptions using Logs in last 3
year, I am...
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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...
|
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();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |