Runtime Error 2001

[Ken Ivins]

I am getting a "Runtime error 2001: You canceled the previous Operation"

The code I am trying to run is:

varX = "(biGroupID = " & Forms![frmSubscribers-MainActive].giGroupID & _
") AND (biCoverageDateStart = " & Format(Me.txbCoverageDate,
"\#mm\/dd\/yyyy\#") & ")"

If IsNull(DLookup("biGroupID", "[tblBilling]", varX)) Then

Basically I want to look at the table billing and if there is a group ID
with a given date then do a command.

Any idea what is causing this and how to fix it?

Thanks,
Ken Ivins

 

[TC]

If you don't get any other answers, debug.print the content of varX & post
back here.

Also, if the biGroupID field is not a required field, your code will not be
able to distinguish these two cases:

1. There are no records matching the criteria, or

2. There >are< one or more such records, but one or more of those have a
Null value for biGroupID, and the DLookup() returned one of those (by
chance).

Better practice would be to replace biGroupID in the DLookup call, with a
constant, eg. 1. I would do that even if biGroupID is a required field & can
never be null. Why name a field, if no one field is specifically relevant to
what you are trying to achieve?

HTH,
TC

 

[Ken Ivins]

Dear TC,

Sorry I took so long to you gracious help. Life just got crazy.

The result of VarX is "(biGroupID = NCBA12345) AND (biCoverageDateStart =
#06/01/2004#)"

Which is what I want. I want to look and see if a posting of data for this
billing Group number for a given date is already in the table. If so then I
cancel the process. If not then it can be added.

The field biGroup is required but I like the idea of retuning a 1 instead.

Although I have never done that with Dlookup. I assumed it had to be a field
value. Your help got me thinking of a few other solutions as well So, let me
try a few them as well and will post again if I am still having problems.

Thanks for you time and energy.

Ken Ivins

If you don't get any other answers, debug.print the content of varX & post
back here.

Also, if the biGroupID field is not a required field, your code will not be
able to distinguish these two cases:

1. There are no records matching the criteria, or

2. There >are< one or more such records, but one or more of those have a
Null value for biGroupID, and the DLookup() returned one of those (by
chance).

Better practice would be to replace biGroupID in the DLookup call, with a
constant, eg. 1. I would do that even if biGroupID is a required field & can
never be null. Why name a field, if no one field is specifically relevant to
what you are trying to achieve?

HTH,
TC

I am getting a "Runtime error 2001: You canceled the previous Operation"

The code I am trying to run is:

varX = "(biGroupID = " & Forms![frmSubscribers-MainActive].giGroupID & _
") AND (biCoverageDateStart = " & Format(Me.txbCoverageDate,
"\#mm\/dd\/yyyy\#") & ")"

If IsNull(DLookup("biGroupID", "[tblBilling]", varX)) Then

Basically I want to look at the table billing and if there is a group ID
with a given date then do a command.

Any idea what is causing this and how to fix it?

Thanks,
Ken Ivins

 

[Graham Mandeno]

Hi Ken

It looks like biGroupID is a text field, so your filter value must be
enclosed in either single or double quotes:
(biGroupID = 'NCBA12345') AND ...

So add these to your code:
varX = "(biGroupID = '" & Forms![frmSubscribers-MainActive].giGroupID _
& "') AND ( ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Dear TC,

Sorry I took so long to you gracious help. Life just got crazy.

The result of VarX is "(biGroupID = NCBA12345) AND (biCoverageDateStart =
#06/01/2004#)"

Which is what I want. I want to look and see if a posting of data for this
billing Group number for a given date is already in the table. If so then I
cancel the process. If not then it can be added.

The field biGroup is required but I like the idea of retuning a 1 instead.

Although I have never done that with Dlookup. I assumed it had to be a field
value. Your help got me thinking of a few other solutions as well So, let me
try a few them as well and will post again if I am still having problems.

Thanks for you time and energy.

Ken Ivins

If you don't get any other answers, debug.print the content of varX & post
back here.

Also, if the biGroupID field is not a required field, your code will not be
able to distinguish these two cases:

1. There are no records matching the criteria, or

2. There >are< one or more such records, but one or more of those have a
Null value for biGroupID, and the DLookup() returned one of those (by
chance).

Better practice would be to replace biGroupID in the DLookup call, with a
constant, eg. 1. I would do that even if biGroupID is a required field & can
never be null. Why name a field, if no one field is specifically

relevant

to
what you are trying to achieve?

HTH,
TC

I am getting a "Runtime error 2001: You canceled the previous Operation"

The code I am trying to run is:

varX = "(biGroupID = " & Forms![frmSubscribers-MainActive].giGroupID & _
") AND (biCoverageDateStart = " & Format(Me.txbCoverageDate,
"\#mm\/dd\/yyyy\#") & ")"

If IsNull(DLookup("biGroupID", "[tblBilling]", varX)) Then

Basically I want to look at the table billing and if there is a group ID
with a given date then do a command.

Any idea what is causing this and how to fix it?

Thanks,
Ken Ivins