| re: Setting the size of column widths of a combo box through Code and Searching Combo Box
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...[color=blue]
>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
>
>
>[/color]
| 
