Using Combo Box to select & open a specific record.

  • Thread starter Thread starter kate
  • Start date Start date
K

kate

Hi
I am trying to do something really basic in access (2k) but can't do
it! Perhaps you could point me in the right direction?

1.User searches for a keyword which should be contained within a
specific field in a set of records. (e.g.: search for meadow within
location field)
2. Any records which contain this keyword in the (location) field are
returned - in a listbox. (e.g.: meadow lane footbridge; meadow fields
school etc)
3. The user double clicks the correct location in the listbox and the
standard form opens at that record.

I can seem to do bits of this but can't get it all together. I have
read various previous postings but am afriad i can't make much headway
- i need a complete beginners/idiots guide please..

Thanks
Kate
 
Step 1. Load the listbox from the text.
Use the AfterUpdate event procedure of the text box to set the RowSource of
the listbox:

Private Sub txtFindWhat_AfterUpdate
Dim strSQL As String
If Not IsNull(Me.txtFindWhat) Then
strSQL = "SELECT LocationID, Location FROM tblLocation WHERE
(Location Like ""*" & Me.txtFindWhat & "*"");"
Me.MyListBox.RowSource = strSQL
End If
End Sub

Step 2. Find the record form the list box.
This assumes the list box has two columns, and the first is the numeric key
you are searching for:

Private Sub lstWhichOne_DblClick(Cancel As Integer)
Dim strWhere As String
If Not IsNull(Me.lstWhichOne) Then
strWhere = "LocationID = " & Me.lstWhichOne
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
With Me.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
Msgbox "Not found."
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub


Personally, I would skip the listbox, and just set the Filter of the form so
that it contains only the records that have a match. The user can easily
find the desired one:

Private Sub txtFindWhat_AfterUpdate
If Not IsNull(Me.txtFindWhat) Then
If Me.Dirty Then 'Save first.
Me.Dirty = False
End If
Me.Filter = "Location Like ""*" & Me.txtFindWhat & "*"")
Me.FilterOn = True
End If
End Sub
 
Hi Allen
Thanks for the reply. My access to the newsgroup through work is a little
sporadic, so I will try this out & see what happens tomorrow.
Thanks again
 
Back
Top