473,320 Members | 1,957 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Dlookup question

I am trying to create a procedure that has values are entered into a
table that based on the value entered will programatically decide
weather or not the Yes/No should be checked. I am trying to use the
Dlookup command so that it looks to another table to get the values to
compare to - I want to use the Dlookup command so that if the
acceptable ranges change that a user wouldn't have to get into the VB
code to make the change happen.

Here is the code....

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("TPtest", DB_OPEN_TABLE)
MyTable.AddNew
' add a new record - the new record becomes the current record
MyTable("Serial Number") = MyData1
' write our data to a field named "SerialData"
MyTable("Pressure") = MyData2
'preform data check
LR = DLookup("[LowRange]", "TestValues", "[TestName]=Pressure")
**get error 2001 cancel previous operation here and quits**
HR = DLookup("[HighRange]", "TestValues", "[TestName]=Pressure")
If MyData2 > "LR" And MyData2 < "HR" Then
MyTable("PressureCK") = -1
End If

If I hard code values into the IF statements it works as expected - I
just would really like to have the testing ranges kept on another
table to be accessed accordingly.

TIA

Nov 12 '05 #1
2 4196
Try adding single quotes around the string, as in:
"[TestName]='Pressure'")

instead of
"[TestName]=Pressure")

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

_____________________________________________

"sherah" <sh*****@fleetwoodgroup.com> wrote in message
news:3f***************@news.egl.net...
I am trying to create a procedure that has values are entered into a
table that based on the value entered will programatically decide
weather or not the Yes/No should be checked. I am trying to use the
Dlookup command so that it looks to another table to get the values to
compare to - I want to use the Dlookup command so that if the
acceptable ranges change that a user wouldn't have to get into the VB
code to make the change happen.

Here is the code....

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("TPtest", DB_OPEN_TABLE)
MyTable.AddNew
' add a new record - the new record becomes the current record
MyTable("Serial Number") = MyData1
' write our data to a field named "SerialData"
MyTable("Pressure") = MyData2
'preform data check
LR = DLookup("[LowRange]", "TestValues", "[TestName]=Pressure")
**get error 2001 cancel previous operation here and quits**
HR = DLookup("[HighRange]", "TestValues", "[TestName]=Pressure")
If MyData2 > "LR" And MyData2 < "HR" Then
MyTable("PressureCK") = -1
End If

If I hard code values into the IF statements it works as expected - I
just would really like to have the testing ranges kept on another
table to be accessed accordingly.

TIA
Nov 12 '05 #2
Oops....for some reason, my first thought was that the values you were accessing were
string values--I think because of the fieldname of TestName. If this field contains
numeric values, then you should disregard what I indicated below. I may or may not have
mislead you, depending on the data type.

_____________________________________________

"Tom Wickerath" <AO***********************@comcast.net> wrote in message
news:N6********************@comcast.com...

Try adding single quotes around the string, as in: "[TestName]=Pressure")

DLookup Usage Samples
http://www.mvps.org/access/general/gen0018.htm

_____________________________________________

"sherah" <sh*****@fleetwoodgroup.com> wrote in message
news:3f***************@news.egl.net...
I am trying to create a procedure that has values are entered into a
table that based on the value entered will programatically decide
weather or not the Yes/No should be checked. I am trying to use the
Dlookup command so that it looks to another table to get the values to
compare to - I want to use the Dlookup command so that if the
acceptable ranges change that a user wouldn't have to get into the VB
code to make the change happen.

Here is the code....

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyTable = MyDB.OpenRecordset("TPtest", DB_OPEN_TABLE)
MyTable.AddNew
' add a new record - the new record becomes the current record
MyTable("Serial Number") = MyData1
' write our data to a field named "SerialData"
MyTable("Pressure") = MyData2
'preform data check
LR = DLookup("[LowRange]", "TestValues", "[TestName]=Pressure")
**get error 2001 cancel previous operation here and quits**
HR = DLookup("[HighRange]", "TestValues", "[TestName]=Pressure")
If MyData2 > "LR" And MyData2 < "HR" Then
MyTable("PressureCK") = -1
"[TestName]='Pressure'")

instead of

End If

If I hard code values into the IF statements it works as expected - I
just would really like to have the testing ranges kept on another
table to be accessed accordingly.

TIA

Nov 12 '05 #3

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

Similar topics

1
by: John Hargrove | last post by:
I am building a database to manage test samples in an environmental laboratory. I am learning Access as I go and don't know much about the programming aspects. I hope to make the application...
2
by: Ronny Sigo | last post by:
Hello all, I already put the same question, only now I have more to tell ... Although I used this code before in the same routine (only the fieldname of the table differs) ___ at this point in the...
2
by: ctyrrell | last post by:
I have read with interest the many discussions of the 3048 Error: Cannot open any more databases. I understand how the number of open Table ID's causes the problem. My question has to do with the...
11
by: MLH | last post by:
DLookup("", "tblPreliminaryVINs", "=Forms!frmVINODO!SerialNum") is giving me a Type Mismatch error. That's confusing to me and I don't know how to circumvent it. The field in...
4
by: Karen Bielska | last post by:
I have a table "Customer" with two fields "CusID" (autonumber, PK) and "CusName" (text). If I know the customer's ID, say 215, how can I find the name using DLookup? PS: I'm not really looking...
6
by: Ron | last post by:
Hi All, Was just wondering which is faster, a subreport or dlookup. I've got a report with either 5 subreports or lots of dlookups. The subreports generally have anywhere from 5 to 12...
9
by: | last post by:
In my database I have a 'control table' in which basic info is stored about the application, for instance the application's path and the name of the company that is using it. In all of the...
7
Breezwell
by: Breezwell | last post by:
This is probably a simple question for someone out there. I understand that the DLookup function takes has the following syntax: DLookup(expression,domain,) From what I have read, domain can...
15
by: rleepac | last post by:
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue This is a table used to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.