Combo Box Columns Issue

[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

 

[Ken Sheridan]

Rather than having the separate button why not pop up a message box if no
company name starting with the value entered is found. First set the combo
box's RowSource property to return just one column of distinct values:

SELECT DISTINCT CompanyName FROM Companies ORDER BY CompanyName;

This allows for two or more companies to have the same name, which might be
unlikely, but is theoretically possible.

Set its LimitToList property to False ('No') and leave its ColumnWidths
property blank. In its AfterUpdate event procedure put:

Dim ctrl As Control
Dim frm as Form
Dim strFilter As String
Dim strMessage As String

Set ctrl = Me.ActiveControl
Set frm = Forms("ProspectSearch")

strMessage = _
"No company names starting with '" & ctrl & _
" ' " & "found." & vbNewLine & vbNewLine & _
"Do you wish to search for company names " & _
"containing '" & ctrl & "'?"

If Not IsNull(ctrl) Then
strFilter = "CompanyName Like """ & ctrl & "*"""
If IsNull(DLookup("CompanyID", "Companies", strFilter)) Then
If MsgBox(strMessage, vbYesNo + vbQuestion, "Find Company") =
vbYes Then
strFilter = " CompanyName Like ""*" & ctrl & "*"""
frm.Filter = strFilter
frm.FilterOn = True
frm.Visible = True
frm.SetFocus
End If
Else
frm.Filter = strFilter
frm.FilterOn = True
frm.Visible = True
frm.SetFocus
End If
End If

Ken Sheridan
Stafford, England

 

[magmike]

Rather than having the separate button why not pop up a message box if no
company name starting with the value entered is found.  First set the combo
box's RowSource property to return just one column of distinct values:

SELECT DISTINCT CompanyName FROM Companies ORDER BY CompanyName;

This allows for two or more companies to have the same name, which might be
unlikely, but is theoretically possible.

Set its LimitToList property to False ('No') and leave its ColumnWidths
property blank.  In its AfterUpdate event procedure put:

    Dim ctrl As Control
    Dim frm as Form
    Dim strFilter As String
    Dim strMessage As String

    Set ctrl = Me.ActiveControl
    Set frm = Forms("ProspectSearch")

    strMessage = _
        "No company names starting with '" & ctrl & _
        " ' " & "found." & vbNewLine & vbNewLine & _
        "Do you wish to search for company names " & _
        "containing '" & ctrl & "'?"

    If Not IsNull(ctrl) Then
        strFilter = "CompanyName Like """ & ctrl & "*"""
        If IsNull(DLookup("CompanyID", "Companies", strFilter)) Then
            If MsgBox(strMessage, vbYesNo + vbQuestion, "FindCompany") =
vbYes Then
                strFilter = " CompanyName Like ""*" & ctrl & "*"""
                frm.Filter = strFilter
                frm.FilterOn = True
                frm.Visible = True
                frm.SetFocus
            End If
        Else
            frm.Filter = strFilter
            frm.FilterOn = True
            frm.Visible = True
            frm.SetFocus
        End If
    End If

Ken Sheridan
Stafford, England




















- Show quoted text -

In your code, what is "Companies"? Is that my company table?

 

[Ken Sheridan]

Yes.

In your code, what is "Companies"? Is that my company table?