Accessing data from tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to look up a record in a table using one field then get the
value of another field in that same record to be used in the vba code? I am
using access 2000
 
Yes, however, to get an accurate return, the field used as the criteria
should be unique:
X=dlookup("[itm]","cisattributetable","[activity] = 'A80560005100210'")
In this case we want to see who the ITM is for Activity Number A80560005100210
?X
Nelson

If the criteria value is not unique, then the return value will be that of
the first record where the critera matches. Nelson is responsible for
multiple Activity Numbers, so if we use ITM as the criteria, we get a
different number:

y=dlookup("[activity]","cisattributetable","[itm] = 'nelson'")
?y
A80560000259020
 
Ok I have tryed to use the dlookup but keep getting an error and can't figure
it out.

Here is the chunk of my code

If counter = 0 Then
container1 = Buffer
'set counter to Employee Number of item out
counter = DLookup("NumberOut", "InventoryControlLog",
"EmployeeControlNumber = (' " & Buffer & " ')")
counter = counter + 1

It keeps coming up and telling me
Run-time error '94':
Invalid use of Null

Any suggestions?

Klatuu said:
Yes, however, to get an accurate return, the field used as the criteria
should be unique:
X=dlookup("[itm]","cisattributetable","[activity] = 'A80560005100210'")
In this case we want to see who the ITM is for Activity Number A80560005100210
?X
Nelson

If the criteria value is not unique, then the return value will be that of
the first record where the critera matches. Nelson is responsible for
multiple Activity Numbers, so if we use ITM as the criteria, we get a
different number:

y=dlookup("[activity]","cisattributetable","[itm] = 'nelson'")
?y
A80560000259020

xavier said:
Is there a way to look up a record in a table using one field then get the
value of another field in that same record to be used in the vba code? I am
using access 2000
Click to expand...
Click to expand...
 
What data type is counter?
counter = DLookup("[NumberOut]", "InventoryControlLog",
"[EmployeeControlNumber] = '" & Buffer & "'")
You don't need the extra parenthese
The above is correct if buffer is string
If it is a number:
counter = DLookup("[NumberOut]", "InventoryControlLog",
"EmployeeControlNumber = " Buffer)
Why are you adding 1 to counter after you load it with the DLookUp?
Alway enclose field names in brackets.
counter and buffer are not very descriptive names.
The problem is your DLookup is not finding what you are looking for and
returning Null. The way to cure that is using the Nz function:
counter = Nz(DLookup("[NumberOut]", "InventoryControlLog",
"EmployeeControlNumber = " Buffer),0)
Now counter will receive a 0 if the Dlookup finds nothing. You can replace
the 0 with whatever value you want

xavier said:
Ok I have tryed to use the dlookup but keep getting an error and can't figure
it out.

Here is the chunk of my code

If counter = 0 Then
container1 = Buffer
'set counter to Employee Number of item out
counter = DLookup("NumberOut", "InventoryControlLog",
"EmployeeControlNumber = (' " & Buffer & " ')")
counter = counter + 1

It keeps coming up and telling me
Run-time error '94':
Invalid use of Null

Any suggestions?

Klatuu said:
Yes, however, to get an accurate return, the field used as the criteria
should be unique:
X=dlookup("[itm]","cisattributetable","[activity] = 'A80560005100210'")
In this case we want to see who the ITM is for Activity Number A80560005100210
?X
Nelson

If the criteria value is not unique, then the return value will be that of
the first record where the critera matches. Nelson is responsible for
multiple Activity Numbers, so if we use ITM as the criteria, we get a
different number:

y=dlookup("[activity]","cisattributetable","[itm] = 'nelson'")
?y
A80560000259020

xavier said:
Is there a way to look up a record in a table using one field then get the
value of another field in that same record to be used in the vba code? I am
using access 2000
Click to expand...
Click to expand...
Click to expand...
 
Counter is an Integer.
Buffer is a string coming from the comm port thru Microsoft Communications
Control version 6.0.
I am adding 1 to the counter because it tells me what # of items are out and
is also my case selector and inorder to move to the proper case I need to add
one to the number of items out.
The record I am using to test does have a number in the NumberOut field.

Klatuu said:
What data type is counter?
counter = DLookup("[NumberOut]", "InventoryControlLog",
"[EmployeeControlNumber] = '" & Buffer & "'")
You don't need the extra parenthese
The above is correct if buffer is string
If it is a number:
counter = DLookup("[NumberOut]", "InventoryControlLog",
"EmployeeControlNumber = " Buffer)
Why are you adding 1 to counter after you load it with the DLookUp?
Alway enclose field names in brackets.
counter and buffer are not very descriptive names.
The problem is your DLookup is not finding what you are looking for and
returning Null. The way to cure that is using the Nz function:
counter = Nz(DLookup("[NumberOut]", "InventoryControlLog",
"EmployeeControlNumber = " Buffer),0)
Now counter will receive a 0 if the Dlookup finds nothing. You can replace
the 0 with whatever value you want

xavier said:
Ok I have tryed to use the dlookup but keep getting an error and can't figure
it out.

Here is the chunk of my code

If counter = 0 Then
container1 = Buffer
'set counter to Employee Number of item out
counter = DLookup("NumberOut", "InventoryControlLog",
"EmployeeControlNumber = (' " & Buffer & " ')")
counter = counter + 1

It keeps coming up and telling me
Run-time error '94':
Invalid use of Null

Any suggestions?

Klatuu said:
Yes, however, to get an accurate return, the field used as the criteria
should be unique:
X=dlookup("[itm]","cisattributetable","[activity] = 'A80560005100210'")
In this case we want to see who the ITM is for Activity Number A80560005100210
?X
Nelson

If the criteria value is not unique, then the return value will be that of
the first record where the critera matches. Nelson is responsible for
multiple Activity Numbers, so if we use ITM as the criteria, we get a
different number:

y=dlookup("[activity]","cisattributetable","[itm] = 'nelson'")
?y
A80560000259020

:

Is there a way to look up a record in a table using one field then get the
value of another field in that same record to be used in the vba code? I am
using access 2000
Click to expand...
Click to expand...
Click to expand...
 
Back
Top