f



Setting the size of column widths of a combo box through Code and Searching Combo Box

I have a combo box on a form which is based on table tblMachine.  On that
combo box I have four columns visible MachineNumber, description, location
and type.  The bound column is the MachineNumber and this is not set to 0 as
the machines have its number on which means the user should know the machine
number.  My problem is if the user doesn't know the number.  I have a button
which changes the row source of the combo box to order by description, but
when the user starts to type the description the relevant record does not
come up as machine number is still visible.  Is it possible, when I change
the row source to also make the first column width 0 through code?

I also want to enable the user to search the combo box leaving the drop down
filled with the search results.
I also have an unbound textbox for the user to enter their search terms, but
I am not sure of the next step

Thanks in advance

Will



0
1/31/2005 10:35:09 AM
comp.databases.ms-access 42670 articles. 0 followers. Post Follow

1 Replies
494 Views

Similar Articles

[PageSpeed] 13

I did a similar sort of thing with 4 options.

Code is here. mnake of it what you will.

Incidently I would strongly recomend using a MachineID (Autonumber) as the 
key to your table rather than the MachineNumber

I know its a pigs ear, but the bound column is always the last one, and the 
titles and column widths change according to the selection option 
TypeOfSearch selected

Function NewTypeOfSearch()      ' Changes the search box

    Dim SQLStg As String, SQLStg1 As String

    On Error GoTo NewSearch_Type_Err

    With CodeContextObject
        !BySearchType.RowSource = ""
        !BySearchType.ColumnCount = 5
        !SearchBox.RowSource = ""
        !SearchBox.ColumnCount = 5

    If !TypeOfSearch.Value = 1 Then         ' My reference
        !SearchText.Caption = "Select My Reference to Search For     "
        !BySearchType.ColumnWidths = "1000;2200;1600;2400;1800"         'In 
Twips
        !BySearchType.BoundColumn = 1
        !SearchBox.ColumnWidths = "1000;2200;1600;2400;1800"
        !SearchBox.BoundColumn = 1
        If .Name = "Insured" Then
            SQLStg = "SELECT Insured.InsuredID, [ILastName] & ' ' & 
[IFirstName] AS Name, "
            SQLStg = SQLStg + "RegistrationNos.RegistrationNo , 
Principals.PrincipalName, "
            SQLStg = SQLStg + "Insured.PrincipalsRef, "
            SQLStg = SQLStg + " Insured.Active "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN Insured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
Insured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
Insured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE Insured.InsuredID Like " & Chr$(34) & 
!Filter & Chr$(34)
            If !Activity < 1 Then
                SQLStg = SQLStg + " AND Insured.Active = " & !Activity & " "
                SQLStg1 = SQLStg1 + " WHERE Insured.Active = " & !Activity & 
" "
            End If
            SQLStg = SQLStg + "ORDER BY Insured.InsuredID;"
            SQLStg1 = SQLStg1 + "ORDER BY Insured.InsuredID;"
        Else                                        ' ArcInsured form
            SQLStg = "SELECT ArcInsured.InsuredID, [ILastName] & ' ' & 
[IFirstName] AS Name, "
            SQLStg = SQLStg + "RegistrationNos.RegistrationNo , 
Principals.PrincipalName, "
            SQLStg = SQLStg + "ArcInsured.PrincipalsRef "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN ArcInsured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
ArcInsured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
ArcInsured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE ArcInsured.InsuredID Like " & Chr$(34) 
& !Filter & Chr$(34)
            SQLStg = SQLStg + " ORDER BY ArcInsured.InsuredID;"
            SQLStg1 = SQLStg1 + " ORDER BY ArcInsured.InsuredID;"
        End If
    ElseIf !TypeOfSearch.Value = 2 Then              ' Principals reference
        !SearchText.Caption = "Select the Principals Reference to Search For 
"
        !BySearchType.ColumnWidths = "1800;2400;2200;1600;1000"
        !BySearchType.BoundColumn = 5
        !SearchBox.ColumnWidths = "1800;2400;2200;1600;1000"
        !SearchBox.BoundColumn = 5
        If .Name = "Insured" Then
            SQLStg = "SELECT Insured.PrincipalsRef, 
Principals.PrincipalName, "
            SQLStg = SQLStg + "[ILastName] & ' ' & Insured.IFirstName AS 
Name, "
            SQLStg = SQLStg + "RegistrationNos.RegistrationNo, 
Insured.InsuredID, "
            SQLStg = SQLStg + " Insured.Active "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN Insured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
Insured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
Insured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE Insured.PrincipalsRef Like " & Chr$(34) 
& !Filter & Chr$(34)
            If !Activity < 1 Then
                SQLStg = SQLStg + " AND Insured.Active = " & !Activity & " "
                SQLStg1 = SQLStg1 + " WHERE Insured.Active = " & !Activity & 
" "
            End If
            SQLStg = SQLStg + "ORDER BY Principals.PrincipalName, 
Insured.PrincipalsRef;"
            SQLStg1 = SQLStg1 + "ORDER BY Principals.PrincipalName, 
Insured.PrincipalsRef;"
        Else                                        ' ArcInsured form
            SQLStg = "SELECT ArcInsured.PrincipalsRef, 
Principals.PrincipalName, "
            SQLStg = SQLStg + "[ILastName] & ' ' & ArcInsured.IFirstName AS 
Name, "
            SQLStg = SQLStg + "RegistrationNos.RegistrationNo, 
ArcInsured.InsuredID "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN ArcInsured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
ArcInsured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
ArcInsured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE ArcInsured.PrincipalsRef Like " & 
Chr$(34) & !Filter & Chr$(34)
            SQLStg = SQLStg + " ORDER BY Principals.PrincipalName, 
ArcInsured.PrincipalsRef;"
            SQLStg1 = SQLStg1 + " ORDER BY Principals.PrincipalName, 
ArcInsured.PrincipalsRef;"
        End If
     ElseIf !TypeOfSearch.Value = 3 Then         ' Insured name
        !SearchText.Caption = "Select the Insured Name to Search For"
        !BySearchType.ColumnWidths = "2200;1600;2400;1800;1000"
        !BySearchType.BoundColumn = 5
        !SearchBox.ColumnWidths = "2200;1600;2400;1800;1000"
        !SearchBox.BoundColumn = 5
        If .Name = "Insured" Then
            SQLStg = "SELECT Insured.ILastName & ' ' & Insured.IFirstName AS 
Name, "
            SQLStg = SQLStg + "RegistrationNos.RegistrationNo, "
            SQLStg = SQLStg + "Principals.PrincipalName, 
Insured.PrincipalsRef, "
            SQLStg = SQLStg + "Insured.InsuredID, Insured.Active "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN Insured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
Insured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
Insured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE Insured.ILastName Like " & Chr$(34) & 
!Filter & Chr$(34)
            If !Activity < 1 Then
                SQLStg = SQLStg + " AND Insured.Active = " & !Activity & " "
                SQLStg1 = SQLStg1 + " WHERE Insured.Active = " & !Activity & 
" "
            End If
            SQLStg = SQLStg + "ORDER BY Insured.ILastName, 
Insured.IFirstName;"
            SQLStg1 = SQLStg1 + "ORDER BY Insured.ILastName, 
Insured.IFirstName;"
        Else
            SQLStg = "SELECT ArcInsured.ILastName & ' ' & 
ArcInsured.IFirstName AS Name, "
            SQLStg = SQLStg + "RegistrationNos.RegistrationNo, "
            SQLStg = SQLStg + "Principals.PrincipalName, 
ArcInsured.PrincipalsRef, "
            SQLStg = SQLStg + "ArcInsured.InsuredID "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN ArcInsured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
ArcInsured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
ArcInsured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE ArcInsured.ILastName Like " & Chr$(34) 
& !Filter & Chr$(34)
            SQLStg = SQLStg + " ORDER BY ArcInsured.ILastName, 
ArcInsured.IFirstName;"
            SQLStg1 = SQLStg1 + " ORDER BY ArcInsured.ILastName, 
ArcInsured.IFirstName;"
        End If
    ElseIf !TypeOfSearch.Value = 4 Then         ' Vehicle Number
        !SearchText.Caption = "Select the Insured Name to Search For 
"
        !BySearchType.ColumnWidths = "1600;2200;1800;2400;1000"
        !BySearchType.BoundColumn = 5
        !SearchBox.ColumnWidths = "1600;2200;1800;2400;1000"
        !SearchBox.BoundColumn = 5
        If .Name = "Insured" Then
            SQLStg = "SELECT RegistrationNos.RegistrationNo, "
            SQLStg = SQLStg + "Insured.ILastName & ' ' & Insured.IFirstName 
AS Name, "
            SQLStg = SQLStg + "Insured.PrincipalsRef, 
Principals.PrincipalName, "
            SQLStg = SQLStg + "Insured.InsuredID, Insured.Active "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN Insured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
Insured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
Insured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE RegistrationNos.RegistrationNO Like " & 
Chr$(34) & !Filter & Chr$(34)
            If !Activity < 1 Then
                SQLStg = SQLStg + " AND Insured.Active = " & !Activity & " "
                SQLStg1 = SQLStg1 + " WHERE Insured.Active = " & !Activity & 
" "
            End If
            SQLStg = SQLStg + "ORDER BY RegistrationNos.RegistrationNo;"
            SQLStg1 = SQLStg1 + "ORDER BY RegistrationNos.RegistrationNo;"
        Else                                ' ArcInsured
            SQLStg = "SELECT RegistrationNos.RegistrationNo, "
            SQLStg = SQLStg + "ArcInsured.ILastName & ' ' & 
ArcInsured.IFirstName AS Name, "
            SQLStg = SQLStg + "ArcInsured.PrincipalsRef, 
Principals.PrincipalName, "
            SQLStg = SQLStg + "ArcInsured.InsuredID "
            SQLStg = SQLStg + "FROM RegistrationNos INNER JOIN "
            SQLStg = SQLStg + "(Principals INNER JOIN ArcInsured "
            SQLStg = SQLStg + "ON Principals.PrincipalID = 
ArcInsured.PrincipalID) "
            SQLStg = SQLStg + "ON RegistrationNos.RegistrationNoID = 
ArcInsured.RegistrationNoID "
            SQLStg1 = SQLStg                        ' Save the SQL
            SQLStg = SQLStg + "WHERE RegistrationNos.RegistrationNo Like " & 
Chr$(34) & !Filter & Chr$(34)
            SQLStg = SQLStg + " ORDER BY RegistrationNos.RegistrationNo;"
            SQLStg1 = SQLStg1 + " ORDER BY RegistrationNos.RegistrationNo;"
        End If
    End If
    !BySearchType.RowSource = SQLStg
    !SearchBox.RowSource = SQLStg1
   End With
   Exit Function

NewSearch_Type_Err:
    MsgBox "Error is " & Error$, 16
    Exit Function

End Function


"Will" <Will_57212@hotmail.com> wrote in message 
news:366fqaF4sod5tU1@individual.net...
>I have a combo box on a form which is based on table tblMachine.  On that
> combo box I have four columns visible MachineNumber, description, location
> and type.  The bound column is the MachineNumber and this is not set to 0 
> as
> the machines have its number on which means the user should know the 
> machine
> number.  My problem is if the user doesn't know the number.  I have a 
> button
> which changes the row source of the combo box to order by description, but
> when the user starts to type the description the relevant record does not
> come up as machine number is still visible.  Is it possible, when I change
> the row source to also make the first column width 0 through code?
>
> I also want to enable the user to search the combo box leaving the drop 
> down
> filled with the search results.
> I also have an unbound textbox for the user to enter their search terms, 
> but
> I am not sure of the next step
>
> Thanks in advance
>
> Will
>
>
> 


0
philnoxx (60)
1/31/2005 5:43:56 PM
Reply: