Move focus inside a drop down list

[Brad]

Thanks for taking the time to read my quetion.

I am importing information into a table. Not all fields
are filled. Filling the empty fields is done on the
form. Me.LocationID (text) has a name in it but it may
not be exactly the same as Me.Producer (a combo box, also
text. items in list come from a shared table on the
server). When Me.Producer receives the focus, I would
like the list to drop down (got that part), but then I'd
like it to go to the item that matches the first 2
letters of the LocationID.

For Example: LocationID could = "Bens Co."
When me.Producer receives the focus I'd like the first
item in the list that has the first two letters = "Be" to
receive the foucs. So it would scroll past all the A's
etc.

Here is what I've tried so far.

Private Sub Producer_GotFocus()
MsgBox Me.Producer
If Not IsNull(Me.Producer) Then
Me.Producer.Dropdown
Me.Producer = Left(Me.LocationID, 2)
End If
End Sub

Thanks again,

Brad

 

[Marshall Barton]

Thanks for taking the time to read my quetion.

I am importing information into a table. Not all fields
are filled. Filling the empty fields is done on the
form. Me.LocationID (text) has a name in it but it may
not be exactly the same as Me.Producer (a combo box, also
text. items in list come from a shared table on the
server). When Me.Producer receives the focus, I would
like the list to drop down (got that part), but then I'd
like it to go to the item that matches the first 2
letters of the LocationID.

For Example: LocationID could = "Bens Co."
When me.Producer receives the focus I'd like the first
item in the list that has the first two letters = "Be" to
receive the foucs. So it would scroll past all the A's
etc.

This is a very tricky thing to do. I played around with it
for a while and the follow code is the best I could come up
with:

Private Sub Producer_GotFocus()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(Producer.RowSource)
rs.FindFirst "LocationID >= '" & Left(Me.LocationID, 2)
& "'"
If rs.NoMatch Then
Me.Producer.ListIndex = Me.Producer.ListCount - 1
Else
Me.Producer.ListIndex = rs.AbsolutePosition
End If
Me.Producer.Dropdown

rs.Close: Set rs = Nothing
Set db = Nothing
End Sub

 

[Guest]

Hey Marsh,

Thanks so much for giving it a try.

Sorry I didn't respond sooner, been home with a cold, and
no access to computer.

Brad

 

[Guest]

Marsh,

I had to change the code a bit to get it to work.
Me.Producercmb is the combo box

for those of you that may find this code useful:

p_name is the field name that populates the combo box as
the row source is a query
Me.LocationIDtxt is a text box that has the name to be
found in the combo box


Private Sub Producercmb_GotFocus()
Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset(Me.Producercmb.RowSource)
rs.FindFirst "p_name >= '" & Left(Me.LocationIDtxt,
2) & "'"
If rs.NoMatch Then
Me.Producercmb.ListIndex =
Me.Producercmb.ListCount - 1
Else
Me.Producercmb.ListIndex = rs.AbsolutePosition
End If
Me.Producercmb.Dropdown

rs.Close: Set rs = Nothing
Set db = Nothing
End Sub