M
magmike
I have a Combo Box that pulls data from a table, uses the 2nd Column
as display (CompanyName) and the first Column (CompanyID) is not
visible, but is the bound column.
Of course, this box operates like a "Starts With" type search and
onUpdate, finds the record chosen based on CompanyID. However, I've
created a form that searches based on Like, so that you can search for
"Bar" and get "Randy's Bar Supply".
What I have tried to do, is using a Command button next to the
ComboBox, is when "Bar" is typed, and no records show up in the
ComboBox (because nothing 'Starts With' "Bar"), is press the button
and have the search form look for records where CompanyName is Like
Bar.
However, I can't set the Limit To List property to "No" because of the
way the columns are, and if I could, the value that my button code
uses is the CompanyID anyhow, so it will just look for an ID code
anyhow.
Of course, the button could just lead to the search form, and the user
could just type it in if they couldn't find results in the ComboBox,
but I'd still like to make the steps as few as possible.
Does anyone have a work around that would allow a user to type a word
(like "Cats") and when nothing is present in the database that starts
with "cats" can press the button to have the search form automatically
search for any record where the company name is like "Cats" ?
Perhaps, is there a way to (by pressing the button) turn off the
warning that says the text doesn't match anything in the list, and
then copy the text typed into the Combo Box and then use it for the
search?
In case it is helpful, here is the button's code (OnClick of course):
Private Sub SearchButton_Click()
On Error GoTo Err_SearchButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ProspectSearch"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!ProspectSearch!findco.Value = Combo137
Forms!ProspectSearch!findquery.Form.Filter = "CompanyName Like " &
"'*" & Forms!ProspectSearch!findco & "*'"
Forms!ProspectSearch!findquery.Form.FilterOn = True
Forms!ProspectSearch!findquery.Form.Visible = True
Forms!ProspectSearch!findco.SetFocus
Exit_SearchButton_Click:
Exit Sub
Err_SearchButton_Click:
MsgBox Err.Description
Resume Exit_SearchButton_Click
End Sub
Thanks in advance!
magmike
as display (CompanyName) and the first Column (CompanyID) is not
visible, but is the bound column.
Of course, this box operates like a "Starts With" type search and
onUpdate, finds the record chosen based on CompanyID. However, I've
created a form that searches based on Like, so that you can search for
"Bar" and get "Randy's Bar Supply".
What I have tried to do, is using a Command button next to the
ComboBox, is when "Bar" is typed, and no records show up in the
ComboBox (because nothing 'Starts With' "Bar"), is press the button
and have the search form look for records where CompanyName is Like
Bar.
However, I can't set the Limit To List property to "No" because of the
way the columns are, and if I could, the value that my button code
uses is the CompanyID anyhow, so it will just look for an ID code
anyhow.
Of course, the button could just lead to the search form, and the user
could just type it in if they couldn't find results in the ComboBox,
but I'd still like to make the steps as few as possible.
Does anyone have a work around that would allow a user to type a word
(like "Cats") and when nothing is present in the database that starts
with "cats" can press the button to have the search form automatically
search for any record where the company name is like "Cats" ?
Perhaps, is there a way to (by pressing the button) turn off the
warning that says the text doesn't match anything in the list, and
then copy the text typed into the Combo Box and then use it for the
search?
In case it is helpful, here is the button's code (OnClick of course):
Private Sub SearchButton_Click()
On Error GoTo Err_SearchButton_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ProspectSearch"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!ProspectSearch!findco.Value = Combo137
Forms!ProspectSearch!findquery.Form.Filter = "CompanyName Like " &
"'*" & Forms!ProspectSearch!findco & "*'"
Forms!ProspectSearch!findquery.Form.FilterOn = True
Forms!ProspectSearch!findquery.Form.Visible = True
Forms!ProspectSearch!findco.SetFocus
Exit_SearchButton_Click:
Exit Sub
Err_SearchButton_Click:
MsgBox Err.Description
Resume Exit_SearchButton_Click
End Sub
Thanks in advance!
magmike