how can i filter my database?

[zeroneux]

can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[strive4peace]

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*

 

[Dave]

I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

 

[strive4peace]

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*

 

[Dave]

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[Dave]

this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave



:

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[Dave]

actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[strive4peace]

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

 

[strive4peace]

Hi Dave,

"When the user types in 'villa' with quotes..."

the user would NOT type the quotes, only the text to filter on ...

correction:

mFilter = "[defendant addressm] Like ""*" _
& Me.cboquicksearch & "*"""

or

mFilter = "[defendant addressm] Like '*" _
& Me.cboquicksearch & "*'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave



:

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[Dave]

Excellent and it works... I'm pretty happy about this point. This has been
pretty frustrating but you certainly made my day. Thank you so much.... Dave

Now, for one last question. Can you suggest a way to filter the combo box
to display only those records with 'villa' in the street so the user can then
select the correct one?

Hi Dave,

"When the user types in 'villa' with quotes..."

the user would NOT type the quotes, only the text to filter on ...

correction:

mFilter = "[defendant addressm] Like ""*" _
& Me.cboquicksearch & "*"""

or

mFilter = "[defendant addressm] Like '*" _
& Me.cboquicksearch & "*'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave



:

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[strive4peace]

Hi Dave,

you are welcome happy to make your day <smile>

"Can you suggest a way to filter the combo box to display only those
records with 'villa' in the street so the user can then select the
correct one?"

you can replace the RowSource property of your combo

create a textbox for the text to filter the combo on

Name --> txtFilterAddress

on its AfterUpdate event:

(Assuming that the street is in the [defendant addressm] field...)

'~~~~~~~~~~~~~~
dim strSQL as string

'this will be the SQL for your combo before
'WHERE and/or ORDER BY clause

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename]" _

if not isnull(me.txtFilterAddress) then
strSQL = strSQL _
& " WHERE [defendant addressm] Like ""*" _
& Me.txtFilterAddress& "*"""
end if

strSQL = strSQL & " ORDER BY [defendant addressm];"

me.cboquicksearch.RowSource = strSQL
me.cboquicksearch.Requery
'~~~~~~~~~~~~~

to ensure that the form filter will NOT be saved when you close the
form, make a Close button with this as its [Event Procedure]

'~~~~~~~~~~~~~
doCmd.Close acForm, me.name, acSaveNo
'~~~~~~~~~~~~~

read 'Access Basics' in my siggy

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Excellent and it works... I'm pretty happy about this point. This has been
pretty frustrating but you certainly made my day. Thank you so much.... Dave

Now, for one last question. Can you suggest a way to filter the combo box
to display only those records with 'villa' in the street so the user can then
select the correct one?

Hi Dave,

"When the user types in 'villa' with quotes..."

the user would NOT type the quotes, only the text to filter on ...

correction:

mFilter = "[defendant addressm] Like ""*" _
& Me.cboquicksearch & "*"""

or

mFilter = "[defendant addressm] Like '*" _
& Me.cboquicksearch & "*'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave



:

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[Dave]

Thanks so much Crystal. I've printed your examples and will add the textbox
in a few hours... you've been extremely helpful and I'm much appreciative.

Now, You have a good day!
Dave

Hi Dave,

you are welcome happy to make your day <smile>

"Can you suggest a way to filter the combo box to display only those
records with 'villa' in the street so the user can then select the
correct one?"

you can replace the RowSource property of your combo

create a textbox for the text to filter the combo on

Name --> txtFilterAddress

on its AfterUpdate event:

(Assuming that the street is in the [defendant addressm] field...)

'~~~~~~~~~~~~~~
dim strSQL as string

'this will be the SQL for your combo before
'WHERE and/or ORDER BY clause

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename]" _

if not isnull(me.txtFilterAddress) then
strSQL = strSQL _
& " WHERE [defendant addressm] Like ""*" _
& Me.txtFilterAddress& "*"""
end if

strSQL = strSQL & " ORDER BY [defendant addressm];"

me.cboquicksearch.RowSource = strSQL
me.cboquicksearch.Requery
'~~~~~~~~~~~~~

to ensure that the form filter will NOT be saved when you close the
form, make a Close button with this as its [Event Procedure]

'~~~~~~~~~~~~~
doCmd.Close acForm, me.name, acSaveNo
'~~~~~~~~~~~~~

read 'Access Basics' in my siggy

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Excellent and it works... I'm pretty happy about this point. This has been
pretty frustrating but you certainly made my day. Thank you so much.... Dave

Now, for one last question. Can you suggest a way to filter the combo box
to display only those records with 'villa' in the street so the user can then
select the correct one?

Hi Dave,

"When the user types in 'villa' with quotes..."

the user would NOT type the quotes, only the text to filter on ...

correction:

mFilter = "[defendant addressm] Like ""*" _
& Me.cboquicksearch & "*"""

or

mFilter = "[defendant addressm] Like '*" _
& Me.cboquicksearch & "*'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave



:

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[strive4peace]

you're welcome, Dave

one more thing ... I often use a combo to FIND records instead of
filtering a form. Consider showing all the records in the form and just
filtering the combo that finds them...

Make one or more unbound combos on your form (like in the header). Let
the first column be invisible and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).

If you are searching the recordset on another form, change the
FindRecord name to be specific (like FindRecord_Order) and, substitute

Me --> forms!formname

If on a subform:
Me --> Me.subform_controlname.form

~~~

if you want to add other ways to limit the Where clause of the SQL for
your combo(s) to find records, here is some food for thought...

'~~~~~~~~~~~~~~~
dim mWhere as variant _
, strSQL as string

mWhere = Null

If not IsNull(me.text_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[TextFieldname]= '" _
& me.text_controlname & "'"
end if

If not IsNull(me.date_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

If not IsNull(me.numeric_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[NumericFieldname]= " _
& me.numeric_controlname
end if

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename] " _
& (" WHERE " + mWhere) _
& " ORDER BY [fieldname];"

me.combo_controlname.RowSource = strSQL
me.combo_controlname.Requery

'~~~~~~~~~~~~~~~~~~~~~
When I have controls to Find and to Filter, I often color-code the
background ... for instance, the background color of Find is pale yellow
and the background color of Filter is pale green or pale blue.
'~~~~~~~~~~~~~~~~~~~~~

from "Access Basics", by Crystal:

Difference between + and &
--------------------------

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

~~~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thanks so much Crystal. I've printed your examples and will add the textbox
in a few hours... you've been extremely helpful and I'm much appreciative.

Now, You have a good day!
Dave

Hi Dave,

you are welcome happy to make your day <smile>

"Can you suggest a way to filter the combo box to display only those
records with 'villa' in the street so the user can then select the
correct one?"

you can replace the RowSource property of your combo

create a textbox for the text to filter the combo on

Name --> txtFilterAddress

on its AfterUpdate event:

(Assuming that the street is in the [defendant addressm] field...)

'~~~~~~~~~~~~~~
dim strSQL as string

'this will be the SQL for your combo before
'WHERE and/or ORDER BY clause

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename]" _

if not isnull(me.txtFilterAddress) then
strSQL = strSQL _
& " WHERE [defendant addressm] Like ""*" _
& Me.txtFilterAddress& "*"""
end if

strSQL = strSQL & " ORDER BY [defendant addressm];"

me.cboquicksearch.RowSource = strSQL
me.cboquicksearch.Requery
'~~~~~~~~~~~~~

to ensure that the form filter will NOT be saved when you close the
form, make a Close button with this as its [Event Procedure]

'~~~~~~~~~~~~~
doCmd.Close acForm, me.name, acSaveNo
'~~~~~~~~~~~~~

read 'Access Basics' in my siggy

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Excellent and it works... I'm pretty happy about this point. This has been
pretty frustrating but you certainly made my day. Thank you so much.... Dave

Now, for one last question. Can you suggest a way to filter the combo box
to display only those records with 'villa' in the street so the user can then
select the correct one?


:

Hi Dave,

"When the user types in 'villa' with quotes..."

the user would NOT type the quotes, only the text to filter on ...

correction:

mFilter = "[defendant addressm] Like ""*" _
& Me.cboquicksearch & "*"""

or

mFilter = "[defendant addressm] Like '*" _
& Me.cboquicksearch & "*'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave



:

hi zeroneux (what is your name?)

"how to filter my database?"

do you have unbound comboboxes/textboxes in, for instance, the header of
a form? What are the control names? what are the fieldnames in the
form RecordSource that they correlate to?

.... or is this an unbound form you are using for reporting?

please provide more details, thank you

Warm Regards,
Crystal

*
have an awesome day
*


zeroneux wrote:
can someone please help me how to filter my database? i need to filter it by
placing a combo box "company names" (contains name of clients, under the
names are status report for each) and placing date range of status you want
to view. is this possible? hope someone can help me.. thanks.

 

[Dave]

WOW... you're too much and so helpful. Thank you for all of your help and
suggestions. One last question. I learn and do so much better by 'seeing'
it and the code you've given will certainly help me out. But.. can you
recommend any books that you've found with little tricks / tips as you've
pointed out? Do you have a practice db with this little example?
Suggestions are appreciated.
Thanks again.
Dave

you're welcome, Dave

one more thing ... I often use a combo to FIND records instead of
filtering a form. Consider showing all the records in the form and just
filtering the combo that finds them...

Make one or more unbound combos on your form (like in the header). Let
the first column be invisible and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).

If you are searching the recordset on another form, change the
FindRecord name to be specific (like FindRecord_Order) and, substitute

Me --> forms!formname

If on a subform:
Me --> Me.subform_controlname.form

~~~

if you want to add other ways to limit the Where clause of the SQL for
your combo(s) to find records, here is some food for thought...

'~~~~~~~~~~~~~~~
dim mWhere as variant _
, strSQL as string

mWhere = Null

If not IsNull(me.text_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[TextFieldname]= '" _
& me.text_controlname & "'"
end if

If not IsNull(me.date_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

If not IsNull(me.numeric_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[NumericFieldname]= " _
& me.numeric_controlname
end if

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename] " _
& (" WHERE " + mWhere) _
& " ORDER BY [fieldname];"

me.combo_controlname.RowSource = strSQL
me.combo_controlname.Requery

'~~~~~~~~~~~~~~~~~~~~~
When I have controls to Find and to Filter, I often color-code the
background ... for instance, the background color of Find is pale yellow
and the background color of Filter is pale green or pale blue.
'~~~~~~~~~~~~~~~~~~~~~

from "Access Basics", by Crystal:

Difference between + and &
--------------------------

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

~~~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thanks so much Crystal. I've printed your examples and will add the textbox
in a few hours... you've been extremely helpful and I'm much appreciative.

Now, You have a good day!
Dave

Hi Dave,

you are welcome happy to make your day <smile>

"Can you suggest a way to filter the combo box to display only those
records with 'villa' in the street so the user can then select the
correct one?"

you can replace the RowSource property of your combo

create a textbox for the text to filter the combo on

Name --> txtFilterAddress

on its AfterUpdate event:

(Assuming that the street is in the [defendant addressm] field...)

'~~~~~~~~~~~~~~
dim strSQL as string

'this will be the SQL for your combo before
'WHERE and/or ORDER BY clause

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename]" _

if not isnull(me.txtFilterAddress) then
strSQL = strSQL _
& " WHERE [defendant addressm] Like ""*" _
& Me.txtFilterAddress& "*"""
end if

strSQL = strSQL & " ORDER BY [defendant addressm];"

me.cboquicksearch.RowSource = strSQL
me.cboquicksearch.Requery
'~~~~~~~~~~~~~

to ensure that the form filter will NOT be saved when you close the
form, make a Close button with this as its [Event Procedure]

'~~~~~~~~~~~~~
doCmd.Close acForm, me.name, acSaveNo
'~~~~~~~~~~~~~

read 'Access Basics' in my siggy

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
Excellent and it works... I'm pretty happy about this point. This has been
pretty frustrating but you certainly made my day. Thank you so much.... Dave

Now, for one last question. Can you suggest a way to filter the combo box
to display only those records with 'villa' in the street so the user can then
select the correct one?


:

Hi Dave,

"When the user types in 'villa' with quotes..."

the user would NOT type the quotes, only the text to filter on ...

correction:

mFilter = "[defendant addressm] Like ""*" _
& Me.cboquicksearch & "*"""

or

mFilter = "[defendant addressm] Like '*" _
& Me.cboquicksearch & "*'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks


:

 

[strive4peace]

Hi Dave,

thank you -- and you are most welcome!

As for a good 'practice' database ... most of what I develop are custom
databases that cannot be shared because they are proprietary. There is
a link to a contact database in 'Access Basics' that you can download.
Chapter 8 of Access Basics lists several links that also have sample
databases and lots of good information -- and there are links for more
essentials throughout the document as well.

The Internet has a wealth of information, Google is your fiend <smile>

~~

aah, books ... there are several good books. Go to your favorite
bookstore and plan to spend at least a few hours picking one or more
books that suit you

1. go to the shelves with your basket(s!) and pull off a copy of every
book on Access.

At each of these subsequent steps, eliminate books that don't meet your
criteria and put the books back on the shelves

2. open each book and see if you like the font it is written in -- make
sure it is big enough and clear enoug. Also ensure that the pages
aren't so thin that text from the backside shows up as darkened areas as
this will distract you while you are trying to learn.

3. check for a comprehensive index

4. now, open each book and read arbitrary sections from different places
in the book. Every author has a different writing style and you will
find some styles more condusive to the way that you learn

5. make sure diagrams are clear enough to see

6. check the pages for "white space" ... just as important as what is
written is what is not ... your brain will need to assimilate a lot of
information and good use of white space is important

You will now have a much smaller stack to evaluate! Books are different
types, some are step-by-step, some are quick-reference, and others are
more comprehensive and designed for reference.

Once you have narrowed your choices, you may want to use price to help
make the final decisions.

pick:

1. a step-by-step book that uses examples you can relate to.
Here is where you will need to sit in the bookstore and read for a few
hours to really find one with good examples you can learn from.
Personally, I do not like step-by-step books; but for you, I think it
would be a good idea. A good Table Of Contents is something to look for
too.

2. quick-reference book (if they have one, it should be a smaller and
less-expensive book -- showing things like shortcut keys)

3. a good reference book
pick one that you could comfortably read cover-cover as that is exactly
what you should do. This book should have a thorough index.

The secret is to buy books that YOU can relate to and read! The number
of pages and what they cover is not nearly as important to how well you
feel you can relate to the author(s) -- it will only be a good book if
you read it <smile>.

~~

You will find these newsgroups to be a great resource, some of the best
Access minds on the world are here. Build a database you have a need
for, and let us help guide you along the way. Read books, do examples,
then change the examples to apply to your practice database. It is ok
to do things you wouldn't really care about, like sum and average
numeric ID fields, or pretend your SaleDate needs to be verified for a
product (like a booking date for a hotel room) <smile>... practice
skills as you read about them and feel they would be useful to know.
There is no substitute for trying it yourself.

As you use Access, print out help screens you visit and put them in a
notebook

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

WOW... you're too much and so helpful. Thank you for all of your help and
suggestions. One last question. I learn and do so much better by 'seeing'
it and the code you've given will certainly help me out. But.. can you
recommend any books that you've found with little tricks / tips as you've
pointed out? Do you have a practice db with this little example?
Suggestions are appreciated.
Thanks again.
Dave

you're welcome, Dave

one more thing ... I often use a combo to FIND records instead of
filtering a form. Consider showing all the records in the form and just
filtering the combo that finds them...

Make one or more unbound combos on your form (like in the header). Let
the first column be invisible and be the primary key ID of the
RecordSource of your form and then, on its AfterUpdate event...

=FindRecord()

this code goes behind the form:

'~~~~~~~~~~~~~~~~~~~~
Private Function FindRecord()

'if nothing is picked in the active control, exit
If IsNull(Me.ActiveControl) Then Exit Function

'save current record if changes were made
If me.dirty then me.dirty = false

'declare a variable to hold the primary key value to look up
Dim mRecordID As Long

'set value to look up by what is selected
mRecordID = Me.ActiveControl

'clear the choice to find
Me.ActiveControl = Null

'find the first value that matches
Me.RecordsetClone.FindFirst "SomeID = " & mRecordID

'if a matching record was found, then move to it
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
End If

End Function

'~~~~~~~~~~~~~~~~~~~~
where
- SomeID is the Name of the primary key field, which is in the
RecordSource of the form -- assuming your primary key is a Long Integer
data type (autonumbers are long integers)

Remember that the Rowsource for a combo can come from anywhere -- it can
pull from multiple tables or only use one ... just make sure that the
first column is the primary key ID of the table you want to search (and
that field is part of the RecordSource for the form you are searching).

If you are searching the recordset on another form, change the
FindRecord name to be specific (like FindRecord_Order) and, substitute

Me --> forms!formname

If on a subform:
Me --> Me.subform_controlname.form

~~~

if you want to add other ways to limit the Where clause of the SQL for
your combo(s) to find records, here is some food for thought...

'~~~~~~~~~~~~~~~
dim mWhere as variant _
, strSQL as string

mWhere = Null

If not IsNull(me.text_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[TextFieldname]= '" _
& me.text_controlname & "'"
end if

If not IsNull(me.date_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[DateFieldname]= #" _
& me.date_controlname & "#"
end if

If not IsNull(me.numeric_controlname ) Then
mWhere = (mWhere + " AND ") _
& "[NumericFieldname]= " _
& me.numeric_controlname
end if

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename] " _
& (" WHERE " + mWhere) _
& " ORDER BY [fieldname];"

me.combo_controlname.RowSource = strSQL
me.combo_controlname.Requery

'~~~~~~~~~~~~~~~~~~~~~
When I have controls to Find and to Filter, I often color-code the
background ... for instance, the background color of Find is pale yellow
and the background color of Filter is pale green or pale blue.
'~~~~~~~~~~~~~~~~~~~~~

from "Access Basics", by Crystal:

Difference between + and &
--------------------------

& and + are both Operators

The standard Concatenation Operator is ampersand (&). If a term that is
concatenated is Null (has no data; unknown), all terms will display if
you use ampersand.

The Addition Operator is the plus sign (+) … but, even if one of the
terms has a value, the result will be Null if any term is Null (kind of
like multiplying by 0). As in math, what is enclosed in parentheses will
be evaluated first.

Null + "anything" = Null
Null & "anything = "anything"

"something " + "anything" = "something anything"
"something " & "anything" = "something anything"
no difference because both of the terms have a value

Null + "" = Null
Null & "" = ""

(Null + " ") & "Lastname" = "Lastname"
(Null & " ") & "Lastname" = " Lastname"
in the second case, the parentheses do not make a difference, each term
is concatenated -- and note the space in the result before Lastname

Do you see the difference between using + and using & ? For instance, if
you want to add a space between first and last name but you are not sure
that first name will be filled out, you can do this:

(Firstname + " ") & Lastname

What is in the parentheses is evaluated first -- then it is concatenated
to what comes next

You might also want to do this:

(Firstname + " ") & (Middlename + " ") & Lastname

Combining + and & in an expression gives you a way to make the result
look right without having to test if something is not filled out.

What if firstname is filled but nothing else? There will be a space at
the end. Usually, this is not a problem but if you want to chop it off,
you can wrap the whole expression in the Trim function, which truncates
leading and trailing spaces.

Trim((Firstname + " ") & (Middlename + " ") & Lastname)

~~~~~~~~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thanks so much Crystal. I've printed your examples and will add the textbox
in a few hours... you've been extremely helpful and I'm much appreciative.

Now, You have a good day!
Dave

:

Hi Dave,

you are welcome happy to make your day <smile>

"Can you suggest a way to filter the combo box to display only those
records with 'villa' in the street so the user can then select the
correct one?"

you can replace the RowSource property of your combo

create a textbox for the text to filter the combo on

Name --> txtFilterAddress

on its AfterUpdate event:

(Assuming that the street is in the [defendant addressm] field...)

'~~~~~~~~~~~~~~
dim strSQL as string

'this will be the SQL for your combo before
'WHERE and/or ORDER BY clause

strSQL = "SELECT field1, field2 " _
& " FROM [Tablename]" _

if not isnull(me.txtFilterAddress) then
strSQL = strSQL _
& " WHERE [defendant addressm] Like ""*" _
& Me.txtFilterAddress& "*"""
end if

strSQL = strSQL & " ORDER BY [defendant addressm];"

me.cboquicksearch.RowSource = strSQL
me.cboquicksearch.Requery
'~~~~~~~~~~~~~

to ensure that the form filter will NOT be saved when you close the
form, make a Close button with this as its [Event Procedure]

'~~~~~~~~~~~~~
doCmd.Close acForm, me.name, acSaveNo
'~~~~~~~~~~~~~

read 'Access Basics' in my siggy

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
Excellent and it works... I'm pretty happy about this point. This has been
pretty frustrating but you certainly made my day. Thank you so much.... Dave

Now, for one last question. Can you suggest a way to filter the combo box
to display only those records with 'villa' in the street so the user can then
select the correct one?


:

Hi Dave,

"When the user types in 'villa' with quotes..."

the user would NOT type the quotes, only the text to filter on ...

correction:

mFilter = "[defendant addressm] Like ""*" _
& Me.cboquicksearch & "*"""

or

mFilter = "[defendant addressm] Like '*" _
& Me.cboquicksearch & "*'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
actually, my combo box produces a huge list of 5000 plus records. When the
user types in 'villa' with quotes, I'd like the combo box to only display
those records with 'villa' in the address line. From there, the user could
select the record they want to make current. Hope this clarifies what I'm
trying to do. I sure appreciate your help. Thanks


:

 

[NukeEng85]

Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

 

[strive4peace]

Hi (what is your name?)

just wanted to let you know that I plan to look at this ... will have
some time this weekend and possibly before...

meanwhile, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal


*
have an awesome day
*

Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?

 

[NukeEng85]

oh, and my name is Jessica

Hi (what is your name?)

just wanted to let you know that I plan to look at this ... will have
some time this weekend and possibly before...

meanwhile, read this:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal


*
have an awesome day
*

Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?

 

[strive4peace]

Hi Jessica,

your code only sets a filter on the main form ... it does nothing with
the subform.

1. Do you have the subform synchronized with LinkMasterFields and
LinkChildFields?

2. Are you using a query for the RecordSource of subform that gets
criteria from the main form? (if so, this is not the best way -- #1 is)

Can you explain what type of information is in your main form and your
subform?

~~~

instead of this:

If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."

you might want to use no criteria to show all the records

~~~

Command44 :

before you write code for a control, you should give it a logical Name
like --> cmdApplyFilter

for more information, read about Properties and Methods here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*

Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

:

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave

 

[NukeEng85]

Crystal,

Thank you so much for looking at my code! I am afraid I'm not sure how to
know if I have the subform synchronized with LinkMasterFields and
LinkChildFields. Could you explain that process/what those are? I think I
am using a query to get the info from the table onto the form. my combo
boxes have row source type set to table/query and the row sources have this
in them: (the other combo box has the same but with a different field)

SELECT DISTINCT [SNM Type]FROM [SNM Data]ORDER BY [SNM Type];


So my main form has two combo boxes, combo22 and combo24. It pulls up the
SNM TYPE and ICA (both fields in my table) from my "SNM Type" table. My main
form also have "Go" and "Reset" buttons which are the Command44 and
Command43.

My subform is just a subform I made in the wizard, and it took the field
names from my table and made them text boxes with labels. When I am in "form
view" it shows up as a table, not individual text boxes (This is what I want).

As far as using "no criteria" to show all the records, I'm not sure how to
do that. What would that change when I used my form?

Is there anything else I can tell you to help you? Thank you so much!

Jessica

Hi Jessica,

your code only sets a filter on the main form ... it does nothing with
the subform.

1. Do you have the subform synchronized with LinkMasterFields and
LinkChildFields?

2. Are you using a query for the RecordSource of subform that gets
criteria from the main form? (if so, this is not the best way -- #1 is)

Can you explain what type of information is in your main form and your
subform?

~~~

instead of this:

If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."

you might want to use no criteria to show all the records

~~~

Command44 :

before you write code for a control, you should give it a logical Name
like --> cmdApplyFilter

for more information, read about Properties and Methods here:

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace


Warm Regards,
Crystal

remote programming and training

*
have an awesome day
*

Crystal,

You seem to really know your stuff with regards to filtering and forms, so
I'm hoping you can help me out.

I have a form with two combo boxes that pull information from their
respective fields in a table. I have set them up with a "go" command button
to filter the info in the table based on what I select in the combo box, and
it shows up in a subform. The problem I'm having is that if I exit form view
and go to design view, or close the file and re-open it, the command button
stops working unless I right click the subform (in form veiw) select "filter
by form" and then "apply filter/sort". After doing this the "go" Command
button works perfectly unless I exit out of the form view. I also have a
"reset" command button that, when I have the "go" button working but the
above steps, only clears the combo boxes, not the results in the subform.

Before I figured out that I had to select "filter by form" and "apply
filter/sort" my form worked only if I selected what I wanted in form view,
clicked into design view, and then when I clicked back into form view the
results had pulled up into the subform. My reset button also cleared the
whole subform. The code is below where command44 s the go button and
command43 is the reset button. Combo22 and Combo24 are my two combo boxes.

Option Compare Database
Option Explicit

Private Sub Combo22_AfterUpdate()

End Sub

Private Sub Command44_Click()
Dim strWhere As String 'The criteria string.
Dim lngLen As Long

If Not IsNull(Me.Combo22) Then
strWhere = strWhere & "([SNM TYPE] = """ & Me.Combo22 & """) AND "
End If


If Not IsNull(Me.Combo24) Then
strWhere = strWhere & "([ICA] = """ & Me.Combo24 & """) AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Debug.Print strWhere


Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub





Private Sub Command43_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acDetail).Controls
Select Case ctl.ControlType
Case acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

The Immediate line after my Debug.print shows like it should be working:

([SNM TYPE] = "IRM-C") AND ([ICA] = "Reactor Core")

Can you help me?

Hi Dave,

oops, sorry I put an extra quote mark in (was distracted doing something
else at the same time)...

take out the extra space on either side of *
remove extra quote on end -- you should only have ONE double quote on
the end because you will be concatenating your search string
(Me.cboquicksearch4)

this is not right:
....Like """ * "" & ...

do this:
....Like ""*" & ...

"" -- embed one quote mark
* -- wildcard
" -- end string

what you will end up with, if your combo value is '123 Main street' is this:

mFilter = "[defendant addressm] Like ""*123 Main street*"""

Filter: [defendant addressm] Like "*123 Main street*"

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code or references, your should always compile
before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
this is the portion of code that is not working...
mFilter = "[defendant addressm] Like """ * "" _
& Me.cboquicksearch4 & "*"""

:

Thank you so much.... it makes sense and I appreciate your help. However,
when I execute I get a 'run-time error 13, type mismatch. Any idea why?


:

Hi Dave,

no extra spaces in the mask
use the word 'Like' instead of equal sign if you have a mask

if you use double quotes to surround the mask, you must use TWO of them
since you are using double quotes to delimit your filter

put this in the [Event Procedure] of the AfterUpdate event of
cboquicksearch

'~~~~~~~~~~~~~
dim mFilter as string
if isnull(me.cboquicksearch) then
'show all records
me.FilterOn = false
else
mFilter = "[defendant addressm] Like """*" _
& Me.cboquicksearch & "*"""
me.filter = mFilter
me.FilterOn = true
end if
me.requery
'~~~~~~~~~~~~~

if your data will not contain a single quote, you can also do this:

mFilter = "[defendant addressm] Like "'*" _
& Me.cboquicksearch & "*'"

WHERE
'defendant addressm' is the name of your field and its data type is text

BUT ... if you are using a combobox, I assume you are giving the user a
list of addresses to pick from. If they choose a complete address, you
do not need to use LIKE

mFilter = "[defendant addressm] = "'" _
& Me.cboquicksearch & "'"


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*




Dave wrote:
I didn't start the string but my question is the same. I haven't found the
answer and can't locate a source. Hopefully someone here can help. I'm
using an unbound combo box on a form. I want the user to type in a partial
address and have the combo box filter only those records that have what the
user typed in.
My code is wrong.
Me.Filter = "[defendant addressm]= "" * " & Me.cboquicksearch & " * """
Me.FilterOn = True
I can't get the filter to work. "the text you entered isn't an item on the
list" is the error message I get.
Can someone help me out....?
Thanks in advance.
Dave