What is wrong with this code?

[Vic]

Dear All,

I found this code snippet on this list (taken from a nice webpage of a
courteous fellow), which I used to filter a form on a combo box. I
wanted to repeat the same code to have an additional combo control
doing a different filtering on the same form, and simply copied the
code below and changed the appropriate combo names.
__________________________________________________ ____
Private Sub ExpByType_AfterUpdate()
If IsNull(Me.ExpByType) Then
Me.FilterOn = False
Else
Me.Filter = "TypeID = """ & Me.ExpByType & """"
Me.FilterOn = True
End If
End Sub
__________________________________________________ ______

But the above code snippet gives me an error message:

"Run time error 2001: You cancelled the previous operation" and the
debug points to the line of:

" Me.Filter = "TypeID = """ & Me.ExpByType & """""
The strange thing is that I already have an other combo on that form
using the very similar code, working just fine...

Can anyone find what I am doing wrong and correct me?

Thanks in advance: Viktor

[Wayne Morgan]

By chance is the value of this new combo numeric? The code you have listed is for
searching for a text value. If the value is numeric, you'll need to remove some of the
quotes.

--
Wayne Morgan
"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Dear All,

I found this code snippet on this list (taken from a nice webpage of a
courteous fellow), which I used to filter a form on a combo box. I
wanted to repeat the same code to have an additional combo control
doing a different filtering on the same form, and simply copied the
code below and changed the appropriate combo names.
__________________________________________________ ____
Private Sub ExpByType_AfterUpdate()
If IsNull(Me.ExpByType) Then
Me.FilterOn = False
Else
Me.Filter = "TypeID = """ & Me.ExpByType & """"
Me.FilterOn = True
End If
End Sub
__________________________________________________ ______

But the above code snippet gives me an error message:

"Run time error 2001: You cancelled the previous operation" and the
debug points to the line of:

" Me.Filter = "TypeID = """ & Me.ExpByType & """""
The strange thing is that I already have an other combo on that form
using the very similar code, working just fine...

Can anyone find what I am doing wrong and correct me?

Thanks in advance: Viktor

[Allen Browne]

The message indicates that there is something wrong with the filter string
you are trying to assign.

If there is a space in the TypeID field's name, try:
Me.Filter = "[Type ID] = """ & Me.ExpByType & """"
If the TypeID is a Number type field, drop the extra quotes:
Me.Filter = "[TypeID] = " & Me.ExpByType

The other possibility is that the filter cannot be assigned, because the
record is dirty and cannot be saved. At the top of the procedure, add:
If Me.Dirty Then
Me.Dirty = False
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Dear All,

I found this code snippet on this list (taken from a nice webpage of a
courteous fellow), which I used to filter a form on a combo box. I
wanted to repeat the same code to have an additional combo control
doing a different filtering on the same form, and simply copied the
code below and changed the appropriate combo names.
__________________________________________________ ____
Private Sub ExpByType_AfterUpdate()
If IsNull(Me.ExpByType) Then
Me.FilterOn = False
Else
Me.Filter = "TypeID = """ & Me.ExpByType & """"
Me.FilterOn = True
End If
End Sub
__________________________________________________ ______

But the above code snippet gives me an error message:

"Run time error 2001: You cancelled the previous operation" and the
debug points to the line of:

" Me.Filter = "TypeID = """ & Me.ExpByType & """""
The strange thing is that I already have an other combo on that form
using the very similar code, working just fine...

Can anyone find what I am doing wrong and correct me?

Thanks in advance: Viktor

[Vic]

Thanks Allen, worked perfectly...The problem was as you predicted,
that TypeID was a number not a string, while the other combo which
worked nicely on the same form has a string primary key.

I am a novice access user, and could use another advice: how could I
have use this two combo and the code to filter for two criteria at the
same time? E.g. If TypeID is set to a certain type, and in the other
combo AuthorID is set to a specific Author, then I would like to
filter for those records which has the given author AND a certain
type...

Is this very complicated to do? I do not have a slightest idea how to
start,:-(

The other thing I wanted to understand: The filter string. What is the
"&" stand for? And what is the difference between the use of quotes
and non-qoutes?

Me.Filter = "[Type ID] = """ & Me.ExpByType & """"
Sorry for all this naive questions and thanks for your help again:

Viktor
"Allen Browne" <ab***************@bigpond.net.au> wrote in message news:<Ia********************@news-server.bigpond.net.au>...

The message indicates that there is something wrong with the filter string
you are trying to assign.

If there is a space in the TypeID field's name, try:
Me.Filter = "[Type ID] = """ & Me.ExpByType & """"
If the TypeID is a Number type field, drop the extra quotes:
Me.Filter = "[TypeID] = " & Me.ExpByType

The other possibility is that the filter cannot be assigned, because the
record is dirty and cannot be saved. At the top of the procedure, add:
If Me.Dirty Then
Me.Dirty = False
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Dear All,

I found this code snippet on this list (taken from a nice webpage of a
courteous fellow), which I used to filter a form on a combo box. I
wanted to repeat the same code to have an additional combo control
doing a different filtering on the same form, and simply copied the
code below and changed the appropriate combo names.
__________________________________________________ ____
Private Sub ExpByType_AfterUpdate()
If IsNull(Me.ExpByType) Then
Me.FilterOn = False
Else
Me.Filter = "TypeID = """ & Me.ExpByType & """"
Me.FilterOn = True
End If
End Sub
__________________________________________________ ______

But the above code snippet gives me an error message:

"Run time error 2001: You cancelled the previous operation" and the
debug points to the line of:

" Me.Filter = "TypeID = """ & Me.ExpByType & """""
The strange thing is that I already have an other combo on that form
using the very similar code, working just fine...

Can anyone find what I am doing wrong and correct me?

Thanks in advance: Viktor

[Wayne Morgan]

Strings require quotes, numbers do not. The reason for the multiple quotes is to tell
Access to place a quote in the result.

MyVariable = "My String"
result is My String
MyVariable = """My String"""
result is "My String"

Sometimes you will see people use single quotes inside the double quotes because it is
easier to read and accomplishes the same thing, to a point. The single quotes will work as
long as none of the data has an apostrophe in it, such as the last name O'Rielly.

The 3 quotes together are actually one quote and two quotes on the left and two quotes and
one quote on the right. The outside quotes are the ones you would normally use to delimit
the string. The pair of quotes inside them are what create the quotes being returned with
the string. In your example, number the quotes from 1 to 8, left to right.

#1 & 4 offset each other. 2 & 3 are the double that return the quote character.
# 5 & 8 offset each other and are around the string returned by 6 & 7 which results in a
quote character.

Another possibility here, which is sometimes easier to read, is to use the Chr() function.
Each character on the keyboard has a number called an ASCII number or ANSI number (the 2
are different). The Chr() function uses the ASCII number. The number for a " is 34. So you
could concatenate in a " using the function. That would change your line to

Me.Filter = "[Type ID] = " & Chr(34) & Me.ExpByType & Chr(34)
The ampersand (&) is a concatenation symbol. It means take the part on each side and join
them together. You will sometimes see + used instead of &. This will work if the values
are strings. If the values can be interpreted as numeric or Null, you may get some
unexpected results (although, these sometimes come in handy).
Yes, you can filter on more than one field.

Example:
Me.Filter = "[Type ID] = """ & Me.ExpByType & """ And [AuthorID] = " & Me.txtAuthorID

If AuthorID is a string then
Me.Filter = "[Type ID] = """ & Me.ExpByType & """ And [AuthorID] = """ & Me.txtAuthorID &
""""
You will find this "doubling up" in a lot of cases where the delimiter, in this case the
quote, is supposed to be returned in the final result.

--
Wayne Morgan
"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Thanks Allen, worked perfectly...The problem was as you predicted,
that TypeID was a number not a string, while the other combo which
worked nicely on the same form has a string primary key.

I am a novice access user, and could use another advice: how could I
have use this two combo and the code to filter for two criteria at the
same time? E.g. If TypeID is set to a certain type, and in the other
combo AuthorID is set to a specific Author, then I would like to
filter for those records which has the given author AND a certain
type...

Is this very complicated to do? I do not have a slightest idea how to
start,:-(

The other thing I wanted to understand: The filter string. What is the
"&" stand for? And what is the difference between the use of quotes
and non-qoutes?

Me.Filter = "[Type ID] = """ & Me.ExpByType & """"

[Allen Browne]

Text fields need quote marks around the data, e.g.:
[Surname] = "Jones"

Date fields need # around the data, e.g.:
[InvoiceDate] = #1/1/2003#

Number fields need no delimiter, e.g.:
[Amount] = 5.67

You are creating a string to use for the Filter.
The string must be in quotes. You therefore need:
"[Amount] = 5.67"

When you try to put the Surname in quotes, Access thinks it has reached the
end of the string when it reaches the quote mark before Jones. To let it
know that this is actually *part* of the string, the trick is to double them
up, i.e.:
"This ""word"" is in quotes"
so what you need is:
"[Surname] = ""Jones"""

The ampersand is for joining two string together (concatenating), e.g.:
"This is the first part" & " and this is the rest."
so if you need to concatenate the name into the string:
"[Surname] = """ & "Jones" & """"
If the name is to be read from a text box, you need:
"[Surname] = """ & [MyTextBox] & """"

Hope that makes sense now.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Thanks Allen, worked perfectly...The problem was as you predicted,
that TypeID was a number not a string, while the other combo which
worked nicely on the same form has a string primary key.

I am a novice access user, and could use another advice: how could I
have use this two combo and the code to filter for two criteria at the
same time? E.g. If TypeID is set to a certain type, and in the other
combo AuthorID is set to a specific Author, then I would like to
filter for those records which has the given author AND a certain
type...

Is this very complicated to do? I do not have a slightest idea how to
start,:-(

The other thing I wanted to understand: The filter string. What is the
"&" stand for? And what is the difference between the use of quotes
and non-qoutes?

Me.Filter = "[Type ID] = """ & Me.ExpByType & """"
Sorry for all this naive questions and thanks for your help again:

Viktor
"Allen Browne" <ab***************@bigpond.net.au> wrote in message

news:<Ia********************@news-server.bigpond.net.au>...

The message indicates that there is something wrong with the filter string you are trying to assign.

If there is a space in the TypeID field's name, try:
Me.Filter = "[Type ID] = """ & Me.ExpByType & """"
If the TypeID is a Number type field, drop the extra quotes:
Me.Filter = "[TypeID] = " & Me.ExpByType

The other possibility is that the filter cannot be assigned, because the
record is dirty and cannot be saved. At the top of the procedure, add:
If Me.Dirty Then
Me.Dirty = False
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Dear All,

I found this code snippet on this list (taken from a nice webpage of a
courteous fellow), which I used to filter a form on a combo box. I
wanted to repeat the same code to have an additional combo control
doing a different filtering on the same form, and simply copied the
code below and changed the appropriate combo names.
__________________________________________________ ____
Private Sub ExpByType_AfterUpdate()
If IsNull(Me.ExpByType) Then
Me.FilterOn = False
Else
Me.Filter = "TypeID = """ & Me.ExpByType & """"
Me.FilterOn = True
End If
End Sub
__________________________________________________ ______

But the above code snippet gives me an error message:

"Run time error 2001: You cancelled the previous operation" and the
debug points to the line of:

" Me.Filter = "TypeID = """ & Me.ExpByType & """""
The strange thing is that I already have an other combo on that form
using the very similar code, working just fine...

Can anyone find what I am doing wrong and correct me?

Thanks in advance: Viktor

[Viktor Lakics]

Thanks Allen and Wayne,

It makes more sense now. One last shot:

So if you do
Me.filter= "[TypeID]=" & Me.ExpByType

Me.ExpByType is a number. How come you can concatenate a string with a
number? Why does not this cause a type mismatch?

Appreciate the detailed answer you gave to my previous qustions

Viktor
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:KL*****************@newssvr17.news.prodigy.co m...

Strings require quotes, numbers do not. The reason for the multiple quotes is to tell Access to place a quote in the result.

MyVariable = "My String"
result is My String
MyVariable = """My String"""
result is "My String"

Sometimes you will see people use single quotes inside the double quotes because it is easier to read and accomplishes the same thing, to a point. The single quotes will work as long as none of the data has an apostrophe in it, such as the last name O'Rielly.
The 3 quotes together are actually one quote and two quotes on the left and two quotes and one quote on the right. The outside quotes are the ones you would normally use to delimit the string. The pair of quotes inside them are what create the quotes being returned with the string. In your example, number the quotes from 1 to 8, left to right.

#1 & 4 offset each other. 2 & 3 are the double that return the quote character. # 5 & 8 offset each other and are around the string returned by 6 & 7 which results in a quote character.

Another possibility here, which is sometimes easier to read, is to use the Chr() function. Each character on the keyboard has a number called an ASCII number or ANSI number (the 2 are different). The Chr() function uses the ASCII number. The number for a " is 34. So you could concatenate in a " using the function. That would change your line to
Me.Filter = "[Type ID] = " & Chr(34) & Me.ExpByType & Chr(34)
The ampersand (&) is a concatenation symbol. It means take the part on eac h side and join them together. You will sometimes see + used instead of &. This will work if the values are strings. If the values can be interpreted as numeric or Null, you may get some unexpected results (although, these sometimes come in handy).
Yes, you can filter on more than one field.

Example:
Me.Filter = "[Type ID] = """ & Me.ExpByType & """ And [AuthorID] = " & Me.txtAuthorID
If AuthorID is a string then
Me.Filter = "[Type ID] = """ & Me.ExpByType & """ And [AuthorID] = """ & Me.txtAuthorID & """"
You will find this "doubling up" in a lot of cases where the delimiter, in this case the quote, is supposed to be returned in the final result.

--
Wayne Morgan
"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Thanks Allen, worked perfectly...The problem was as you predicted,
that TypeID was a number not a string, while the other combo which
worked nicely on the same form has a string primary key.

I am a novice access user, and could use another advice: how could I
have use this two combo and the code to filter for two criteria at the
same time? E.g. If TypeID is set to a certain type, and in the other
combo AuthorID is set to a specific Author, then I would like to
filter for those records which has the given author AND a certain
type...

Is this very complicated to do? I do not have a slightest idea how to
start,:-(

The other thing I wanted to understand: The filter string. What is the
"&" stand for? And what is the difference between the use of quotes
and non-qoutes?

Me.Filter = "[Type ID] = """ & Me.ExpByType & """"

[Allen Browne]

As an example, let's say the number is 23.
The filter has to read:
"[TypeID] = 23"
so you have to concatenate the number onto the end of your string to get
this result.

There is a problem if the number is Null. In this case, concatenating the
number onto the string results in:
"[TypeID] = "
which clearly is not going to work.
To prevent this problem, you might want to use:
Me.filter= "[TypeID]=" & Nz(Me.ExpByType, 0)

Glad it makes sense. That's what this group is about.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Viktor Lakics" <la*****@ntlworld.com> wrote in message
news:jd********************@newsfep2-win.server.ntli.net...

Thanks Allen and Wayne,

It makes more sense now. One last shot:

So if you do
Me.filter= "[TypeID]=" & Me.ExpByType

Me.ExpByType is a number. How come you can concatenate a string with a
number? Why does not this cause a type mismatch?

Appreciate the detailed answer you gave to my previous qustions

Viktor
"Wayne Morgan" <co***************************@hotmail.com> wrote in message news:KL*****************@newssvr17.news.prodigy.co m...

Strings require quotes, numbers do not. The reason for the multiple quotes

is to tell

Access to place a quote in the result.

MyVariable = "My String"
result is My String
MyVariable = """My String"""
result is "My String"

Sometimes you will see people use single quotes inside the double quotes because it is

easier to read and accomplishes the same thing, to a point. The single

quotes will work as

long as none of the data has an apostrophe in it, such as the last name

O'Rielly.

The 3 quotes together are actually one quote and two quotes on the left

and two quotes and

one quote on the right. The outside quotes are the ones you would

normally use to delimit

the string. The pair of quotes inside them are what create the quotes being returned with

the string. In your example, number the quotes from 1 to 8, left to

right.
#1 & 4 offset each other. 2 & 3 are the double that return the quote

character.

# 5 & 8 offset each other and are around the string returned by 6 & 7

which results in a

quote character.

Another possibility here, which is sometimes easier to read, is to use

the Chr() function.

Each character on the keyboard has a number called an ASCII number or

ANSI number (the 2

are different). The Chr() function uses the ASCII number. The number for

a " is 34. So you

could concatenate in a " using the function. That would change your line to

Me.Filter = "[Type ID] = " & Chr(34) & Me.ExpByType & Chr(34)
The ampersand (&) is a concatenation symbol. It means take the part on

eac h side and join

them together. You will sometimes see + used instead of &. This will

work if the values

are strings. If the values can be interpreted as numeric or Null, you

may get some

unexpected results (although, these sometimes come in handy).
Yes, you can filter on more than one field.

Example:
Me.Filter = "[Type ID] = """ & Me.ExpByType & """ And [AuthorID] = " & Me.txtAuthorID

If AuthorID is a string then
Me.Filter = "[Type ID] = """ & Me.ExpByType & """ And [AuthorID] = """ &

Me.txtAuthorID &

""""
You will find this "doubling up" in a lot of cases where the delimiter,

in this case the

quote, is supposed to be returned in the final result.

--
Wayne Morgan
"Vic" <la*****@ntlworld.com> wrote in message
news:52**************************@posting.google.c om...

Thanks Allen, worked perfectly...The problem was as you predicted,
that TypeID was a number not a string, while the other combo which
worked nicely on the same form has a string primary key.

I am a novice access user, and could use another advice: how could I
have use this two combo and the code to filter for two criteria at the
same time? E.g. If TypeID is set to a certain type, and in the other
combo AuthorID is set to a specific Author, then I would like to
filter for those records which has the given author AND a certain
type...

Is this very complicated to do? I do not have a slightest idea how to
start,:-(

The other thing I wanted to understand: The filter string. What is the
"&" stand for? And what is the difference between the use of quotes
and non-qoutes?

Me.Filter = "[Type ID] = """ & Me.ExpByType & """"