Error from DMAX in VBA

[Guest]

I’m using Access 2003 with Access 2000 file format.
I’m getting an error message “Run-time Error ‘2001’: You canceled the
previous operation’ when using a Where Clause with DMAX in VBA.

The code is:

Me.txtOrder = DMax("[OOrder]", "tblOCode", "SCode = " & Me.Parent.SCode) + 1

This code works without errors when I don’t include the Where Clause
portion. I’ve looked for the error in the Microsoft Knowledge Base but do
not find any reference. I probably have some problem with the quotes.

txtOrder is a control in the form that holds the sequential numbers, the
numbers should be sequential for each unique SCode.

OOrder is the field in the table tblOCode that holds the sequential numbers.
SCode is a foreign key field in table tblOCode and a primary key field in
tblSCode. SCode is unique in tblSCode. tblSCode and tblOCode have a one to
many relationship.

I’ve read many posts regarding sequential numbers in this group and others
like it and found similar code, but continue to get error message.

TIA

 

[Guest]

Maui,
Your assumption about the quotes is probably right on the money. I do not
know about the help file in 2003, but in Access 2000, there's a help search
that will actually find an entry on quotation marks. The entry will make
suggestions on how to use the 'single quote' or even the chr$(34) for the
double quote.

But, based on what it appears you are trying to do, you want to have your
where clause as such:

"SCode = Me.Parent.SCode"

You should remember that the ENTIRE where clause needs to be in double
quotes. That's the part that I still get stuck on, especially when I am
dealing with something like "SCode = 'Trees'". Remembering the singles
inside the doubles... grrrrr...

I hope this helps!

Derek

 

[Guest]

Thanks Derek;

I found the Quotation Marks topic in VBA Help.
As you said, it was the single quotation marks inside the double quotation
marks.

Me.txtOrder = DMax("[OOrder]", "tblOCode", "SCode = ' " & Me.Parent.SCode &
" ' ") + 1

I was trying variations in this but using the single quotes on the wrong
side of the equals sign.

Thanks again;
All works well now.

Maui on my Mind

Maui,
Your assumption about the quotes is probably right on the money. I do not
know about the help file in 2003, but in Access 2000, there's a help search
that will actually find an entry on quotation marks. The entry will make
suggestions on how to use the 'single quote' or even the chr$(34) for the
double quote.

But, based on what it appears you are trying to do, you want to have your
where clause as such:

"SCode = Me.Parent.SCode"

You should remember that the ENTIRE where clause needs to be in double
quotes. That's the part that I still get stuck on, especially when I am
dealing with something like "SCode = 'Trees'". Remembering the singles
inside the doubles... grrrrr...

I hope this helps!

Derek

I’m using Access 2003 with Access 2000 file format.
I’m getting an error message “Run-time Error ‘2001’: You canceled the
previous operation’ when using a Where Clause with DMAX in VBA.

The code is:

Me.txtOrder = DMax("[OOrder]", "tblOCode", "SCode = " & Me.Parent.SCode) + 1

This code works without errors when I don’t include the Where Clause
portion. I’ve looked for the error in the Microsoft Knowledge Base but do
not find any reference. I probably have some problem with the quotes.

txtOrder is a control in the form that holds the sequential numbers, the
numbers should be sequential for each unique SCode.

OOrder is the field in the table tblOCode that holds the sequential numbers.
SCode is a foreign key field in table tblOCode and a primary key field in
tblSCode. SCode is unique in tblSCode. tblSCode and tblOCode have a one to
many relationship.

I’ve read many posts regarding sequential numbers in this group and others
like it and found similar code, but continue to get error message.

TIA