You canceled the previous operation...

[branston]

Hi, I have the (really helpfull) error "You canceled the previous operation" coming up on the piece of code marked with a string of stars below:

Dim IntMaxRev As Integer
Dim strOpen As String

strOpen = "[ProjectNumber] = '" & Me.ProjNo & "' and [PNo] = " & Me.PNo
IntMaxRev = Nz(DMax("Rev", "TblPMain", strOpen), 0) *****

Any ideas why?

 

[Galaxiom]

VBA is really a mind bender with nested quotes.

The escape character for a literal double quote mark is another double quote mark. Sometimes you get up to four double quote marks in a row.

strOpen = "[ProjectNumber] = """ & Me.ProjNo & """ and [PNo] = " & Me.PNo

It is a really good reason to avoid using string variables in such expressions wherever possible. You have project number as a string and you could do yourself a favour by changing that to a numeric field or working with an ID field.

Access loves numbers and you should use them wherever possible. It is a lot more work to process strings. Index the ID field in the table properties and the performance will be spectacular.

 

[branston]

Hi. Thanks for the reply.
I changed it to your suggestion but im getting the same error.
ProjNo & PNo are actually text - they are called numbers, but they have letters in aswell... just to confuse!!

Any other ideas?

 

[KenHigg]

Is there any other code executing?

 

[branston]

Yeah, theres a lot of code in the module, but it never gets executed as the code i showed was the 1st few lines - there was nothing before.

 

[branston]

ahHA! Finally found it - knew it would be something like this - just a spelling error. Eurch!

Thankyou!

 

[Galaxiom]

I once concluded that in Access, "You canceled the previous operation" was somehow similar to drrrrrrrrrrrrrrrrrrrrrrrrr.
Perhaps you need to look at randommmmmmmmmmm.

 

[KenHigg]

So what event are you kicking this code off with?

Second, I don't think you need to quote the project number as it appears to be a numeric value:

strOpen = "[ProjectNumber] = " & Me.ProjNo & " and [PNo] = " & Me.PNo
IntMaxRev = Nz(DMax("Rev", "TblPMain", strOpen), 0) *****

Third, I always like to paren compound evals:

strOpen = "([ProjectNumber] = " & Me.ProjNo & ") and ([PNo] = " & Me.PNo & ")"
IntMaxRev = Nz(DMax("Rev", "TblPMain", strOpen), 0) *****

This may or may not help...


>> Edit << Then never mind