By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 1,871 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Dlookup question

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.