SEARCH FROM DROP DOWN LIST

[dcorleto]

have a combo box on a form that user can drop down to search for a record. Have it locked so user cannot change the list which comes from a primary key field in a table.

Is there a way to allow the user to type in charactes that would bring the items in the list close to what the user is searching for, without the risk of having the user alter any of the records in the list?

 

[TheAceMan1]

How are ya dcorleto . . .

[blue]Is there a way to allow the user to type in charactes that would bring the items in the list close to what the user is searching for, without the risk of having the user alter any of the records in the list?[/blue]

[ol][li]Make sure the [blue]Auto Expand[/blue] & [blue]Limit To List[/blue] properties are set to [blue]yes[/blue].[/li]
[li]In the [blue]On Got Focus[/blue] event of the combobox, copy/paste the following line:

[blue]   Me![purple][b][i]ComboboxName[/i][/b][/purple].Dropdown[/blue]

[/li]
[li]In the [blue]On Not in List[/blue] event, copy/paste the following:

[blue]   Dim Msg As String, Style As Integer, Title As String
   
   Msg = "You must enter an existing value in the dropdown list! . . ."
   Style = vbInformation + vbOKOnly
   Title = "Attempt to Update List Error! . . ."
   MsgBox Msg, Style, Title
   Response = acDataErrContinue[/blue]

[/li][/ol]
The dropdown list will act like [blue]find as you type[/blue], while the [blue]On Not in List[/blue] event prevents any changes to the list.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[dcorleto]

Very cool - it works, but once I select the location, I get an error saying changes requested to the table not successful would create duplicate values in index primary key or relationship...etc.

Otherwise, this would be great. Also, any way to type in a string that doesn't necessarily have to start with the very beginning, but just some of the text anywhere in the string?

 

[MajP]

The problem is not with your combo, but where you trying to save the value. You are trying to save it into a field that must have unique values, and you are saving a value that already exists in that field.

 

[MajP]

If you want to find any text that matches you will probably need to change the rowsource. Here is an example. In this case I turn auto expand off.

Private Sub Combo8_Change()
Dim strSql As String
strSql = "SELECT ID, strLastName FROM tblNames "
strSql = strSql & "WHERE strLastName LIKE '*" & Combo8.Text & "*' "
strSql = strSql & "ORDER BY [strLastName]"
Combo8.RowSource = strSql
debug.print strSql
Me.Combo8.Dropdown
End Sub

The key is the like statement. This is how the rowsource changes as I type in the name Smith

SELECT ID, strLastName FROM tblNames WHERE strLastName LIKE '**' ORDER BY [strLastName]
SELECT ID, strLastName FROM tblNames WHERE strLastName LIKE '*S*' ORDER BY [strLastName]
SELECT ID, strLastName FROM tblNames WHERE strLastName LIKE '*Sm*' ORDER BY [strLastName]
SELECT ID, strLastName FROM tblNames WHERE strLastName LIKE '*Smi*' ORDER BY [strLastName]
SELECT ID, strLastName FROM tblNames WHERE strLastName LIKE '*Smit*' ORDER BY [strLastName]
SELECT ID, strLastName FROM tblNames WHERE strLastName LIKE '*Smith*' ORDER BY [strLastName]

 

[PHV]

Anyway, for me, a combo used as a navigation tool should NOT be bound.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

dcorleto . . .

. . . or if its bound, the underlying table has to be on the many side. Either way I agree with [blue]PHV[/blue] . . . navigation controls should never be bound.

You'll need to rethink your tables a little . . .

See Ya! . . . . . .