Using 3 textboxes to filter a subform either together/independentl

[Scitea]

Hi, I've been given the job of creating a database for a colleague using a
current excel speadsheet with 3500 records, so filtering is definately needed!

I have created a form which contains a subreport detailing information such
as Company Name, Country, Trademark, Expiry Year, Agent Name, etc. I have put
textboxes in the form header for Company Name, Country and Trademark which
works well when you know all three pieces of information. However, I have 2
questions;

1. Can I set up the textboxes in the Header so that they will filter if only
1 or 2 of them are filled in (the others being blank)?

2. Can I create a 'wildcard filter' so that when you enter the first few
letters of, say, the trademark, it automatically filters for those characters
as it changes? I have created wildcard filters before, but with set
characters (e.g. Star*) in queries and am unsure if it can be done without
defining characters to filter for, other than what is entered into the text
box each time (I'm ok with VB but I've only dealt with the basics before!).

Any help with this would be great,

Many thanks,

Sci x

 

[Douglas J. Steele]

You can set the filter in code, which means that you can check whether or
not a particular field has something in it, and only include it if is does.

You can also add a wildcard character (and use Like).

Dim strFilter As String

If IsNull(Me.txtCompanyName) = False Then
strFilter = strFilter & "[CompanyName] LIKE """ & _
Me.txtCompanyName & "*"" AND "
End If

If IsNull(Me.txtCountry) = False Then
strFilter = strFilter & "[Country] LIKE """ & _
Me.txtCountry & "*"" AND "
End If

If IsNull(Me.txtTrademark) = False Then
strFilter = strFilter & "[Trademark] LIKE """ & _
Me.txtTrademark & "*"" AND "
End If

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = vbNullString
Me.FilterOn = False
End If

How you call this is up to you. You could add a button that the user clicks
on once they've filled in as much as they want, and call that code in the
button's On Click event, or you could set it up as a function that's called
in the AfterUpdate event of the three text boxes.

 

[Scitea]

Douglas,

I am having trouble with getting the code to run properly. I have created a
command button to run the code after data is entered into the text boxes, but
the following error message keeps appearing;

"Compile Error: Method or Data Member not found" and in VB the section that
is highlighted is the first line of the If function, and only the
'txtCompanyName' is highlighted. The same happens with the other three codes
when I try to seperate them.

I have tried all I can think of to get this to work but I can't seem to get
it sorted. Any clues as to why this is happening (I'll bet it's something
really simple!)

Thanks for helping with this, I really appreciate it,

Sci x

You can set the filter in code, which means that you can check whether or
not a particular field has something in it, and only include it if is does.

You can also add a wildcard character (and use Like).

Dim strFilter As String

If IsNull(Me.txtCompanyName) = False Then
strFilter = strFilter & "[CompanyName] LIKE """ & _
Me.txtCompanyName & "*"" AND "
End If

If IsNull(Me.txtCountry) = False Then
strFilter = strFilter & "[Country] LIKE """ & _
Me.txtCountry & "*"" AND "
End If

If IsNull(Me.txtTrademark) = False Then
strFilter = strFilter & "[Trademark] LIKE """ & _
Me.txtTrademark & "*"" AND "
End If

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = vbNullString
Me.FilterOn = False
End If

How you call this is up to you. You could add a button that the user clicks
on once they've filled in as much as they want, and call that code in the
button's On Click event, or you could set it up as a function that's called
in the AfterUpdate event of the three text boxes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I've been given the job of creating a database for a colleague using a
current excel speadsheet with 3500 records, so filtering is definately
needed!

I have created a form which contains a subreport detailing information
such
as Company Name, Country, Trademark, Expiry Year, Agent Name, etc. I have
put
textboxes in the form header for Company Name, Country and Trademark which
works well when you know all three pieces of information. However, I have
2
questions;

1. Can I set up the textboxes in the Header so that they will filter if
only
1 or 2 of them are filled in (the others being blank)?

2. Can I create a 'wildcard filter' so that when you enter the first few
letters of, say, the trademark, it automatically filters for those
characters
as it changes? I have created wildcard filters before, but with set
characters (e.g. Star*) in queries and am unsure if it can be done without
defining characters to filter for, other than what is entered into the
text
box each time (I'm ok with VB but I've only dealt with the basics
before!).

Any help with this would be great,

Many thanks,

Sci x

 

[Douglas J. Steele]

I assumed that the text boxes are named txtCompanyName, txtCountry and
txtTrademark (and that they'll be used to filter fields CompanyName,
Country and Trademark in your form's Recordsource). If your names are
different, change the names from the sample code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I am having trouble with getting the code to run properly. I have created
a
command button to run the code after data is entered into the text boxes,
but
the following error message keeps appearing;

"Compile Error: Method or Data Member not found" and in VB the section
that
is highlighted is the first line of the If function, and only the
'txtCompanyName' is highlighted. The same happens with the other three
codes
when I try to seperate them.

I have tried all I can think of to get this to work but I can't seem to
get
it sorted. Any clues as to why this is happening (I'll bet it's something
really simple!)

Thanks for helping with this, I really appreciate it,

Sci x

You can set the filter in code, which means that you can check whether or
not a particular field has something in it, and only include it if is
does.

You can also add a wildcard character (and use Like).

Dim strFilter As String

If IsNull(Me.txtCompanyName) = False Then
strFilter = strFilter & "[CompanyName] LIKE """ & _
Me.txtCompanyName & "*"" AND "
End If

If IsNull(Me.txtCountry) = False Then
strFilter = strFilter & "[Country] LIKE """ & _
Me.txtCountry & "*"" AND "
End If

If IsNull(Me.txtTrademark) = False Then
strFilter = strFilter & "[Trademark] LIKE """ & _
Me.txtTrademark & "*"" AND "
End If

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = vbNullString
Me.FilterOn = False
End If

How you call this is up to you. You could add a button that the user
clicks
on once they've filled in as much as they want, and call that code in the
button's On Click event, or you could set it up as a function that's
called
in the AfterUpdate event of the three text boxes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, I've been given the job of creating a database for a colleague
using a
current excel speadsheet with 3500 records, so filtering is definately
needed!

I have created a form which contains a subreport detailing information
such
as Company Name, Country, Trademark, Expiry Year, Agent Name, etc. I
have
put
textboxes in the form header for Company Name, Country and Trademark
which
works well when you know all three pieces of information. However, I
have
2
questions;

1. Can I set up the textboxes in the Header so that they will filter if
only
1 or 2 of them are filled in (the others being blank)?

2. Can I create a 'wildcard filter' so that when you enter the first
few
letters of, say, the trademark, it automatically filters for those
characters
as it changes? I have created wildcard filters before, but with set
characters (e.g. Star*) in queries and am unsure if it can be done
without
defining characters to filter for, other than what is entered into the
text
box each time (I'm ok with VB but I've only dealt with the basics
before!).

Any help with this would be great,

Many thanks,

Sci x

 

[Scitea]

Ah ha! Sorted! I knew it would be something simple like that!

Thanks so much for your help, you're a star!!!!!

Sci x

I assumed that the text boxes are named txtCompanyName, txtCountry and
txtTrademark (and that they'll be used to filter fields CompanyName,
Country and Trademark in your form's Recordsource). If your names are
different, change the names from the sample code.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

I am having trouble with getting the code to run properly. I have created
a
command button to run the code after data is entered into the text boxes,
but
the following error message keeps appearing;

"Compile Error: Method or Data Member not found" and in VB the section
that
is highlighted is the first line of the If function, and only the
'txtCompanyName' is highlighted. The same happens with the other three
codes
when I try to seperate them.

I have tried all I can think of to get this to work but I can't seem to
get
it sorted. Any clues as to why this is happening (I'll bet it's something
really simple!)

Thanks for helping with this, I really appreciate it,

Sci x

You can set the filter in code, which means that you can check whether or
not a particular field has something in it, and only include it if is
does.

You can also add a wildcard character (and use Like).

Dim strFilter As String

If IsNull(Me.txtCompanyName) = False Then
strFilter = strFilter & "[CompanyName] LIKE """ & _
Me.txtCompanyName & "*"" AND "
End If

If IsNull(Me.txtCountry) = False Then
strFilter = strFilter & "[Country] LIKE """ & _
Me.txtCountry & "*"" AND "
End If

If IsNull(Me.txtTrademark) = False Then
strFilter = strFilter & "[Trademark] LIKE """ & _
Me.txtTrademark & "*"" AND "
End If

If Len(strFilter) > 0 Then
strFilter = Left(strFilter, Len(strFilter) - 5)
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.Filter = vbNullString
Me.FilterOn = False
End If

How you call this is up to you. You could add a button that the user
clicks
on once they've filled in as much as they want, and call that code in the
button's On Click event, or you could set it up as a function that's
called
in the AfterUpdate event of the three text boxes.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi, I've been given the job of creating a database for a colleague
using a
current excel speadsheet with 3500 records, so filtering is definately
needed!

I have created a form which contains a subreport detailing information
such
as Company Name, Country, Trademark, Expiry Year, Agent Name, etc. I
have
put
textboxes in the form header for Company Name, Country and Trademark
which
works well when you know all three pieces of information. However, I
have
2
questions;

1. Can I set up the textboxes in the Header so that they will filter if
only
1 or 2 of them are filled in (the others being blank)?

2. Can I create a 'wildcard filter' so that when you enter the first
few
letters of, say, the trademark, it automatically filters for those
characters
as it changes? I have created wildcard filters before, but with set
characters (e.g. Star*) in queries and am unsure if it can be done
without
defining characters to filter for, other than what is entered into the
text
box each time (I'm ok with VB but I've only dealt with the basics
before!).

Any help with this would be great,

Many thanks,

Sci x