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!
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!