combo box based on filter

J

Jinny

Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
 
K

Ken Sheridan

Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England
 
J

Jinny

Thank you so much for your response. It is much appreciated. The code works
perfectly, but if I may, I would like to ask another question:

When there is no filter, how can I have the combo box show all
"CompanyName"s? I tried a different codes in the AfterUpdate txtsearch, but
they didn't work.

Thank you!!!

Ken Sheridan said:
Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

Jinny said:
Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
Click to expand...
Click to expand...
 
K

Ken Sheridan

Set the RowSource property conditionally to an unrestricted query. There are
a variety of things you could examine for this, but as you are already
examining the txtSearch control for Null you can do it there. Also if
Me.RecordsetClone.RecordCount = 0

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") " & _
"OR (CompanyID IN (SELECT CompanyID FROM Contacts " & _
"WHERE Contacts.LastName Like ""*" & Me.txtSearch & _
"*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"
Me.Combo80.requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery
End If
End If
End With

Ken Sheridan
Stafford, England

Jinny said:
Thank you so much for your response. It is much appreciated. The code works
perfectly, but if I may, I would like to ask another question:

When there is no filter, how can I have the combo box show all
"CompanyName"s? I tried a different codes in the AfterUpdate txtsearch, but
they didn't work.

Thank you!!!

Ken Sheridan said:
Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

Jinny said:
Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
Click to expand...
Click to expand...
Click to expand...
 
J

Jinny

Dear Ken,

Thank you for your response. I appreciate your help so much.

I am unsure of how to "set the RowSource property conditionally to an
unrestricted query." I suppose that is the root of my problem--after having
carefully changed and added what you have written in the code, the combo box
still does not show any companies at all when there is no filter. I'm sorry
to be such a bother, but can you tell me how to do this?

Thank you!

Jinny

Ken Sheridan said:
Set the RowSource property conditionally to an unrestricted query. There are
a variety of things you could examine for this, but as you are already
examining the txtSearch control for Null you can do it there. Also if
Me.RecordsetClone.RecordCount = 0

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") " & _
"OR (CompanyID IN (SELECT CompanyID FROM Contacts " & _
"WHERE Contacts.LastName Like ""*" & Me.txtSearch & _
"*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"
Me.Combo80.requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery
End If
End If
End With

Ken Sheridan
Stafford, England

Jinny said:
Thank you so much for your response. It is much appreciated. The code works
perfectly, but if I may, I would like to ask another question:

When there is no filter, how can I have the combo box show all
"CompanyName"s? I tried a different codes in the AfterUpdate txtsearch, but
they didn't work.

Thank you!!!

Ken Sheridan said:
Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

:

Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Jinny:

The code I sent you will do it. You don't have to do anything in the
control's properties sheet other than set the other properties, BoundColumn
etc as I described before (the wizard's probably already done it for you in
fact). Just amend your code to that which I sent you and it should work fine.

If you do have any problems don't hesitate to post back.

Ken Sheridan
Stafford, England

Jinny said:
Dear Ken,

Thank you for your response. I appreciate your help so much.

I am unsure of how to "set the RowSource property conditionally to an
unrestricted query." I suppose that is the root of my problem--after having
carefully changed and added what you have written in the code, the combo box
still does not show any companies at all when there is no filter. I'm sorry
to be such a bother, but can you tell me how to do this?

Thank you!

Jinny

Ken Sheridan said:
Set the RowSource property conditionally to an unrestricted query. There are
a variety of things you could examine for this, but as you are already
examining the txtSearch control for Null you can do it there. Also if
Me.RecordsetClone.RecordCount = 0

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") " & _
"OR (CompanyID IN (SELECT CompanyID FROM Contacts " & _
"WHERE Contacts.LastName Like ""*" & Me.txtSearch & _
"*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"
Me.Combo80.requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery
End If
End If
End With

Ken Sheridan
Stafford, England

Jinny said:
Thank you so much for your response. It is much appreciated. The code works
perfectly, but if I may, I would like to ask another question:

When there is no filter, how can I have the combo box show all
"CompanyName"s? I tried a different codes in the AfterUpdate txtsearch, but
they didn't work.

Thank you!!!

:

Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

:

Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
Click to expand...
Click to expand...
Click to expand...
 
J

Jinny

Ken,

I have posted the exact code you have written and the combo box is still
empty when the filter is off. Here is my code (the name of the table is
actually "Clients"), without the wrap-around method that you used. I entered
it in originally with the &_, and that did not work, too. Thank you so much!!

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients ORDER
BY CompanyName"
Me.CompanyName.Requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients
WHERE" & strWhere & "ORDER BY CompanyName"
Me.CompanyName.Requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients
ORDER BY CompanyName"
Me.CompanyName.Requery


End If
End If
End With
End Sub

Ken Sheridan said:
Jinny:

The code I sent you will do it. You don't have to do anything in the
control's properties sheet other than set the other properties, BoundColumn
etc as I described before (the wizard's probably already done it for you in
fact). Just amend your code to that which I sent you and it should work fine.

If you do have any problems don't hesitate to post back.

Ken Sheridan
Stafford, England

Jinny said:
Dear Ken,

Thank you for your response. I appreciate your help so much.

I am unsure of how to "set the RowSource property conditionally to an
unrestricted query." I suppose that is the root of my problem--after having
carefully changed and added what you have written in the code, the combo box
still does not show any companies at all when there is no filter. I'm sorry
to be such a bother, but can you tell me how to do this?

Thank you!

Jinny

Ken Sheridan said:
Set the RowSource property conditionally to an unrestricted query. There are
a variety of things you could examine for this, but as you are already
examining the txtSearch control for Null you can do it there. Also if
Me.RecordsetClone.RecordCount = 0

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") " & _
"OR (CompanyID IN (SELECT CompanyID FROM Contacts " & _
"WHERE Contacts.LastName Like ""*" & Me.txtSearch & _
"*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"
Me.Combo80.requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery
End If
End If
End With

Ken Sheridan
Stafford, England

:

Thank you so much for your response. It is much appreciated. The code works
perfectly, but if I may, I would like to ask another question:

When there is no filter, how can I have the combo box show all
"CompanyName"s? I tried a different codes in the AfterUpdate txtsearch, but
they didn't work.

Thank you!!!

:

Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

:

Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Sheridan

Jinny:

If you are turning the filter off in some other way than by setting the
txtSearch control to Null, e.g. from the built in button on the toolbar, then
the code won't execute, so lets do it another way which should work whatever
way the filter is turned off. Put the following code in the GotFocus evenbt
procedure of the combo box:

Dim ctrl As Control
Dim strSQL as string

Set ctrl = Me.ActiveControl

strSQL = ""SELECT CompanyID, CompanyName " & _
"FROM Clients ORDER BY CompanyName"


If Not Me.FilterOn Then
ctrl.RowSource = strSQL
ctrl.Requery
End If

As soon as you move focus to the combo box the code will execute and if the
FilterOn property is False will set the RowSource property of the control to
the unrestricted SQL statement.

Let me know how you get on.

Ken Sheridan
Stafford, England

Jinny said:
Ken,

I have posted the exact code you have written and the combo box is still
empty when the filter is off. Here is my code (the name of the table is
actually "Clients"), without the wrap-around method that you used. I entered
it in originally with the &_, and that did not work, too. Thank you so much!!

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients ORDER
BY CompanyName"
Me.CompanyName.Requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients
WHERE" & strWhere & "ORDER BY CompanyName"
Me.CompanyName.Requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients
ORDER BY CompanyName"
Me.CompanyName.Requery


End If
End If
End With
End Sub

Ken Sheridan said:
Jinny:

The code I sent you will do it. You don't have to do anything in the
control's properties sheet other than set the other properties, BoundColumn
etc as I described before (the wizard's probably already done it for you in
fact). Just amend your code to that which I sent you and it should work fine.

If you do have any problems don't hesitate to post back.

Ken Sheridan
Stafford, England

Jinny said:
Dear Ken,

Thank you for your response. I appreciate your help so much.

I am unsure of how to "set the RowSource property conditionally to an
unrestricted query." I suppose that is the root of my problem--after having
carefully changed and added what you have written in the code, the combo box
still does not show any companies at all when there is no filter. I'm sorry
to be such a bother, but can you tell me how to do this?

Thank you!

Jinny

:

Set the RowSource property conditionally to an unrestricted query. There are
a variety of things you could examine for this, but as you are already
examining the txtSearch control for Null you can do it there. Also if
Me.RecordsetClone.RecordCount = 0

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") " & _
"OR (CompanyID IN (SELECT CompanyID FROM Contacts " & _
"WHERE Contacts.LastName Like ""*" & Me.txtSearch & _
"*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"
Me.Combo80.requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery
End If
End If
End With

Ken Sheridan
Stafford, England

:

Thank you so much for your response. It is much appreciated. The code works
perfectly, but if I may, I would like to ask another question:

When there is no filter, how can I have the combo box show all
"CompanyName"s? I tried a different codes in the AfterUpdate txtsearch, but
they didn't work.

Thank you!!!

:

Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

:

Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
Click to expand...
Click to expand...
Click to expand...
 
J

Jinny

Ken,

It all works!!! I am so grateful, thank you so much. To confirm, I added
the code to the combo box's gotfocus event, and kept the code that you gave
me in the txtsearch's afterupdate event.

Thank you, thank you, thank you!

Jinny

Ken Sheridan said:
Jinny:

If you are turning the filter off in some other way than by setting the
txtSearch control to Null, e.g. from the built in button on the toolbar, then
the code won't execute, so lets do it another way which should work whatever
way the filter is turned off. Put the following code in the GotFocus evenbt
procedure of the combo box:

Dim ctrl As Control
Dim strSQL as string

Set ctrl = Me.ActiveControl

strSQL = ""SELECT CompanyID, CompanyName " & _
"FROM Clients ORDER BY CompanyName"


If Not Me.FilterOn Then
ctrl.RowSource = strSQL
ctrl.Requery
End If

As soon as you move focus to the combo box the code will execute and if the
FilterOn property is False will set the RowSource property of the control to
the unrestricted SQL statement.

Let me know how you get on.

Ken Sheridan
Stafford, England

Jinny said:
Ken,

I have posted the exact code you have written and the combo box is still
empty when the filter is off. Here is my code (the name of the table is
actually "Clients"), without the wrap-around method that you used. I entered
it in originally with the &_, and that did not work, too. Thank you so much!!

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients ORDER
BY CompanyName"
Me.CompanyName.Requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients
WHERE" & strWhere & "ORDER BY CompanyName"
Me.CompanyName.Requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.CompanyName.RowSource = "SELECT CompanyID, CompanyName FROM Clients
ORDER BY CompanyName"
Me.CompanyName.Requery


End If
End If
End With
End Sub

Ken Sheridan said:
Jinny:

The code I sent you will do it. You don't have to do anything in the
control's properties sheet other than set the other properties, BoundColumn
etc as I described before (the wizard's probably already done it for you in
fact). Just amend your code to that which I sent you and it should work fine.

If you do have any problems don't hesitate to post back.

Ken Sheridan
Stafford, England

:

Dear Ken,

Thank you for your response. I appreciate your help so much.

I am unsure of how to "set the RowSource property conditionally to an
unrestricted query." I suppose that is the root of my problem--after having
carefully changed and added what you have written in the code, the combo box
still does not show any companies at all when there is no filter. I'm sorry
to be such a bother, but can you tell me how to do this?

Thank you!

Jinny

:

Set the RowSource property conditionally to an unrestricted query. There are
a variety of things you could examine for this, but as you are already
examining the txtSearch control for Null you can do it there. Also if
Me.RecordsetClone.RecordCount = 0

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery

Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") " & _
"OR (CompanyID IN (SELECT CompanyID FROM Contacts " & _
"WHERE Contacts.LastName Like ""*" & Me.txtSearch & _
"*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"

Me.Filter = strWhere
Me.FilterOn = True

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"
Me.Combo80.requery

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies ORDER BY CompanyName"
Me.Combo80.requery
End If
End If
End With

Ken Sheridan
Stafford, England

:

Thank you so much for your response. It is much appreciated. The code works
perfectly, but if I may, I would like to ask another question:

When there is no filter, how can I have the combo box show all
"CompanyName"s? I tried a different codes in the AfterUpdate txtsearch, but
they didn't work.

Thank you!!!

:

Try adding the following code to the txtSearch control's AfterUpdate event
procedure, after the string expression has been assigned to the strWhere
variable of course:

Me.Combo80.RowSource = "SELECT CompanyID, CompanyName " & _
"FROM Companies WHERE " & strWhere & " ORDER BY CompanyName"

Me.Combo80.Requery

I've assumed that the table's name is Companies.

Combo80's other properties should be as follows:

RowSourceType: Table/Query

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Ken Sheridan
Stafford, England

:

Hello all,

I am having significant trouble finding a resolution to this problem. I
have a single form based on a table of companies. I have a textbox used to
create a filter, which searches company name or contact name (the latter is
linked by companyID to a unique table, Contacts [thanks Allen Browne!]).

Where the form show the company name, I would like the combo box to only
show the results of the filter, and allow me to show a record based on my
selection in the combo box. I am unclear if this need to happen in the
events of the search box, the events of the combo box, or the row source of
the combo box. Can someone please tell me what to code into VBA or the combo
box?

I have tried altering cascading combo box code, but with no results. Here
is the code for my search box:

Private Sub txtSearch_AfterUpdate()

Dim strWhere As String

If Me.Dirty Then
Me.Dirty = False
End If

With Me.txtSearch
If IsNull(.Value) Then 'Show all records
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strWhere = "(CompanyName Like ""*" & Me.txtSearch & "*"") OR (CompanyID
IN (SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" &
Me.txtSearch & "*"" OR Contacts.FirstName Like ""*" & Me.txtSearch & "*""))"
Me.Filter = strWhere
Me.FilterOn = True

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "No records found."
Me.FilterOn = False

End If
End If
End With
End Sub

And code for the combo box is the one created by the wizard:
Private Sub Combo80_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CompanyID] = " & Str(Nz(Me![Combo80], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Thanks much!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Searching 1
Combo Box & Filter Error 3
searching using a combo box 1
MultiSelect List Box to Filter Form 4
combo box filter 1
filter by multiple combo boxes 2
Filtering and Removing Filter based on a combo box 4
Access Search on Access Table 1

Top