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

[Will]

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

[Phil Stanton]

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" <Wi********@hotmail.com> wrote in message
news:36*************@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