DLookup Criteria

[Steve]

Hi
I am trying to add a unique Record Id ( primary key ) into a record by first
creating it then checking if its been used if not use it.
This is what I have done but I get a run-time error
Error 2001 You canceled the previous operation.
If I change the critera for the LOOKUP the error goes away but I get no result
The primary key is [CCardID]
The table is tblCustomers
The value I want to check is DumVal
I think I have the syntax wrong in the DLookup but I have been looking
through help files for hrs and cant find what Im doing wrong.
any help most apreciated.

Dim MyVal As Double
Dim DumVal As String
Dim upperbound As Long
Dim lowerbound As Integer
Dim strlength As Integer
Dim checkit As Variant
upperbound = 999999999
lowerbound = 1
Randomize
MyVal = Abs(Int((upperbound - lowerbound + 1) * Rnd + lowerbound))
strlength = Len(Str(MyVal))
' strip blank from number and add a prefix
DumVal = "CUST" & Right(Str(MyVal), (strlength - 1))
' placed here to check as this ID is in use and will be remove if can get
working
DumVal = "CUST123456"
' check if this number has been used
checkit = DLookup("[CCardID]", "tblCustomers", DumVal)

I have tried changing DumVal for "'DumVal'" in the critera and the result I
get from the Variable checkit is a CCardID in the middle of the record set
that has no meaning as it is nothing like criteria DumVal.

I have also tried also "[CCardID]= " & "'DumVal'" as the critera for the
lookup
and just get checkit = NULL but I know there is a record ID its just not
finding it.

Steve - from a land down under

 

[Allan Murphy]

Steve
After
DumVal = "CUST" & Right(Str(MyVal), (strlength - 1))

add

DumVal = "'" & DumVal & "'"

"'" is double quotes then a single quote then double quotes

change

checkit = DCount("[CCardID]", "tblCustomers", "CCardID=" & DumVal)

DCount can be used to count the number occurence the criteria occurs in the
table

Allan
Also from the land down under.

 

[Steve]

Thanks Allan it works perfectly now
the quotes thing I must get my head around somehow
Im in Perth by the way and happy to meet you on here.
I have just returned to using Access after a few years lay off doing other
stuff and so I have alot to learn again so I thank you for your help.

Steve - from a land down under

Steve
After
DumVal = "CUST" & Right(Str(MyVal), (strlength - 1))

add

DumVal = "'" & DumVal & "'"

"'" is double quotes then a single quote then double quotes

change

checkit = DCount("[CCardID]", "tblCustomers", "CCardID=" & DumVal)

DCount can be used to count the number occurence the criteria occurs in the
table

Allan
Also from the land down under.

Hi
I am trying to add a unique Record Id ( primary key ) into a record by
first
creating it then checking if its been used if not use it.
This is what I have done but I get a run-time error
Error 2001 You canceled the previous operation.
If I change the critera for the LOOKUP the error goes away but I get no
result
The primary key is [CCardID]
The table is tblCustomers
The value I want to check is DumVal
I think I have the syntax wrong in the DLookup but I have been looking
through help files for hrs and cant find what Im doing wrong.
any help most apreciated.

Dim MyVal As Double
Dim DumVal As String
Dim upperbound As Long
Dim lowerbound As Integer
Dim strlength As Integer
Dim checkit As Variant
upperbound = 999999999
lowerbound = 1
Randomize
MyVal = Abs(Int((upperbound - lowerbound + 1) * Rnd + lowerbound))
strlength = Len(Str(MyVal))
' strip blank from number and add a prefix
DumVal = "CUST" & Right(Str(MyVal), (strlength - 1))
' placed here to check as this ID is in use and will be remove if can get
working
DumVal = "CUST123456"
' check if this number has been used
checkit = DLookup("[CCardID]", "tblCustomers", DumVal)

I have tried changing DumVal for "'DumVal'" in the critera and the result
I
get from the Variable checkit is a CCardID in the middle of the record set
that has no meaning as it is nothing like criteria DumVal.

I have also tried also "[CCardID]= " & "'DumVal'" as the critera for the
lookup
and just get checkit = NULL but I know there is a record ID its just not
finding it.

Steve - from a land down under