filtering a listbox

[rich1838]

I have searched and not found an answer to my question so I am posting.

I have a table named FieldData with a lookup field that gets data from another table TrainerInfo that full names in a single field (John Q. Public, Sr.) and it works great.

What I would like to know is when start to type the name "John", can the dropdown list filter names only with John in the field. Also, is there a way (similiar to searching windows explorer) where as I type the field is reduced to only matches of what I have typed no matter where the letter string. As an example typing Pub would narrow the list to John Q. Public, Al's Pub, Public Morals, etc.

The list has over a thousand records so this would make data entry more efficient when typing say John Smith and scrolling through hundres of names to get to John Jones, as well as if I only have an initial say J. Brown I can type Brown and find John Brown.

I know this happens in web pages and windows so I would like to know how to implement if possible.

Thanks

[jforbes]

You might want to look at this code. It was working pretty well last time I ran it: http://bytes.com/topic/access/answer...em#post3787104 You'll also want to set the AutoExpand Property to No. That is addressed a little further down the post.

[rich1838]

I have tried to implement this and it does not appear to be working. I will attach the code from the query that the combo box uses. it has a filter built in already as trainers data is track specific therefore their name may appear multiple times in the table.

SELECT TrainerData.ID, TrainerData.Trainer, TrainerData.Track
FROM TrainerData
WHERE (((TrainerData.Track)=[Forms]![RaceEntryForm]![Track]));
 

[jforbes]

You'll have to merge the Where Clause of the TrainerData.Track with what is typed in by the User... Something like this:

Option Compare Database
 Option Explicit
 
 Private bLookupKeyPress As Boolean
 
 Private Sub cboLookup_Change()
     Dim sSQL As String
     Dim sNewLookup As String
 
     If Not bLookupKeyPress Then
         sNewLookup = Nz(Me.cboLookup.Text, "")
         sSQL = sSQL & "SELECT TrainerData.ID, TrainerData.Trainer, TrainerData.Track "
         sSQL = sSQL & " FROM TrainerData "
         sSQL = sSQL & " WHERE TrainerData.Track=[Forms]![RaceEntryForm]![Track] "
         If Len(sNewLookup) <> 0 Then
            sSQL = sSQL & " AND TrainerData.Trainer LIKE '*" & sNewLookup & "*'"
         End If
         Me.cboLookup.RowSource = sSQL
         Me.cboLookup.Dropdown
     End If
     bLookupKeyPress = False
 End Sub
 
 Private Sub cboLookup_KeyDown(KeyCode As Integer, Shift As Integer)
     Select Case KeyCode
         Case vbKeyDown, vbKeyUp
             bLookupKeyPress = True
         Case Else
             bLookupKeyPress = False
     End Select
 End Sub
 
 Private Sub cboLookup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
     bLookupKeyPress = True
 End Sub 
 

At anytime while you are getting this to work, you can post your code and we can help you out.

[rich1838]

May be simple but just to confirm. Is this going in the on change or AfterUpdate property. Reading post not sure

[jforbes]

cboLookup was the name of the Control in the example I played around with. You'll probably need to update all the occurances of cboLookup with the Name of your Control.

To answer your question, it's the OnChange Event:

Private Sub cboLookup_Change()

For a little more explanation, there are three events involved:

1. OnChange - Where the ComboBox Rowsource is updated based on what has been entered so far.
2. OnKeyDown - To flag the Input as User input and not something changed by Access, like navigating to a different row.
3. OnMouseDown - To show the Dropdown list when the user clicks in the Combobox.

Private bLookupKeyPress As Boolean

will need to be put at the top of the Form's code. It's the Flag that is set whenever a user performs some input in the ComboBox.

[rich1838]

Worked like a champ. Thanks for the help!

[rich1838]

Stuck again! The search works great, however I now have a problem where when I go to the next record, the last chosen item is in the field instead of going blank.

[jforbes]

I think I see what you are talking about. The Dropdown would still be filtered to what was used on the previous record. It would need to be reset as the User navigates through the records. I played around with it a bit and came up something like this:

Private Sub Form_Current()
    Dim sSQL As String
    sSQL = sSQL & "SELECT TrainerData.ID, TrainerData.Trainer, TrainerData.Track "
    sSQL = sSQL & " FROM TrainerData "
    sSQL = sSQL & " WHERE TrainerData.Track=[Forms]![RaceEntryForm]![Track] "
    If Me.cboLookup.RowSource <> sSQL Then Me.cboLookup.RowSource = sSQL
End Sub

This will reset the RowSource of the ComboBox whenever the User navigates to a different record. I put the Test in there so that the RowSource is only Reset if it needs to be. It should run better that way.

To make this a bit cleaner, you may want to relocate this into it's own function and call the function from the OnCurrent event.

[rich1838]

Placed it in OnCurrent and get Compile Error. Method of Data Member not found. TrainerLookup is my control box name

Option Compare Database
 
Private Sub Form_Current()
 
    Dim sSQL As String
    sSQL = sSQL & "SELECT TrainerData.ID, TrainerData.Trainer, TrainerData.Track "
    sSQL = sSQL & " FROM TrainerData "
    sSQL = sSQL & " WHERE TrainerData.Track=[Forms]![RaceEntryForm]![Track] "
    If Me.TrainerLookup.RowSource <> sSQL Then Me.TrainerLookup.RowSource = sSQL
End Sub

[jforbes]

Hmmm, looks like it's having trouble resolving something. It could be the where clause. Might want try commenting out line 8 just to rule it out. You may also want to check line 6 and 7 to make sure they match your Column names and Table name.

The idea behind this it to reset the RowSource so that it is wide open. So you would want to set it to whatever you set it to for the OnCurrent Event, minus additional filtering from what the user typed in. The code I posted is just a guess based on what you provided.

If you are still stuck after looking at it again, maybe post all the code you have and we can try to get it working.

... I'm about to go on a 5 day vacation, so if I can't help you, hopefully someone else can. I hope to check in from time to time, but It might be difficult while canoeing. =)

[rich1838]

I just realized this is on a subform and that has no OnCurrent. The code was placed in the RaceEntryForm which has the child FieldData Subform. All the tables and fields are correct