Pasting text into a combo-box for browsing

  • Thread starter Thread starter Gary Schuldt
  • Start date Start date
G

Gary Schuldt

Here's the situation:

I have a garden plant database. I'm trying to improve the searching
functionality on it.

I have a form based on a query that displays botanical name (txtBotName) and
common name.

The Botanical Name is the concatenation of Genus, Species, and Variety
names. The query does this concatenation and is sorted ascending on
Botanical Name.

I want to find and display a record that contains a specified search
word/phrase (e.g., coral) in any of those fields (essentially Botanical Name
and Common Name).

So far, so good: I can clunk through this with Edit->Find and locate the
record I want, and do FindNext to look at another one.

However, in many cases the user will want to directly browse the Botanical
Names surrounding the one found by FindRecord. To do this, I added to the
form an unbound combo box cbxBrowseBotName whose control source is the same
as that of the form, and the only column in it is the Botanical Name.

To browse, I do all this with the mouse after Finding a record: I highlight
the txtBotName value, Ctrl-C, highlight cbxBrowseBotName value, Ctrl-V and
then hit the drop-down arrow and . . . Voila! I see the nearby Botanical
Names in the drop-down. I have an AfterUpdate event on the cbx to find the
any newly selected record, and that works fine.

It seems, though, that I should be able to produce the cbx dropdown either
using a macro or VBA code without all that highlighting and cutting and
pasting, but I can't figure out how to do it.

I need some help with that. Or, if you see a better way to achieve the same
functionality, I'd sure be open to that as well!

TIA

Gary
 
Hi Gary,

Here is some code I use to do text searching in a bible database I have.
The procedure searches for any occurrence of the search value, whether or
not it has leading or trailing characters. So, if you were searching for
"Maria", it would also return "TecoMariaCapensus". I don't know if it's
what you need but maybe you'll be able to modify to do what you want.

This code is used in the Click event of the seacrh button. The user enters
a search value into a text box then clicks the search button.

Private Sub cmdSearch_Click()
On Error GoTo Err_Handler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSearch As String
Dim strMsg As String
Dim strSQL As String
Dim intLen As Integer
Dim lngCounter As Long
Dim strVerses()

Set db = CurrentDb
Me.ocxList.Clear
lngCounter = 0

'Feedback for the user
With Me
.lblCounterDisplay.Caption = "Found " & lngCounter & " item(s)."
.txtFullVerse = ""
End With
'Set up some variables and check that the
'search text has been entered
strSearch = Nz(Me.txtSearch, "")
strMsg = "Text value required."
If IsNull(Me.txtSearch) Then
MsgBox strMsg, vbInformation, "New Living Translation"
Me.txtSearch.SetFocus
Else
DoCmd.Hourglass (True)
Me.Caption = "Searching..."
'Define the SQL that will do the search in the table(s)
strSQL = "SELECT tblBooks.BookTitle, tblVerses.Chapter,
tblVerses.Verse, tblVerses.TextData "
strSQL = strSQL & "FROM tblBooks INNER JOIN tblVerses ON
tblBooks.BookID = tblVerses.BookID "
strSQL = strSQL & "WHERE (((tblVerses.TextData) Like " & """" & "*"
& Trim(Me.txtSearch) & "*" & """" & ")) "
strSQL = strSQL & "ORDER BY 1, 2, 3, 4;"

Set rs = db.OpenRecordset(strSQL)
'The first bit here gets the full recordcount to enabling the
'setting of the MAX value on a progress meter
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
With Me.ocxProgress
.Max = rs.RecordCount
.Visible = True
End With
'Loop through the results of the recordset and concatenate some
fields
'together, then add them to a list box on the form
Do
With rs
Me.ocxList.AddItem .Fields(0) _
& " " & .Fields(1) & " v" & .Fields(2) & ": " &
..Fields(3)
lngCounter = lngCounter + 1
Me.lblCounterDisplay.Caption = "Found " & lngCounter
& " item(s)."
Me.ocxProgress = lngCounter
DoEvents
rs.MoveNext
End With
Loop Until rs.EOF
rs.Close
Set rs = Nothing
Set db = Nothing
'Clean up the user advice controls
With Me
.ocxProgress = 0
.ocxProgress.Visible = False
.Caption = "Search"
End With
Else
MsgBox "No records match criteria.", vbInformation, "Search"
End If
End If
Me.Caption = "Search"

Exit_cmdSearch_Click:
DoCmd.Hourglass (False)
Exit Sub

Err_Handler:
If Err = 3075 Then
MsgBox "You cannot use quotes or other special marks in the search
string.", vbExclamation, "Criterion Error"
Else
MsgBox Err.Number & ": " & Err.Description
End If
Resume Exit_cmdSearch_Click

End Sub

Jamie

All Unsolicited email is deleted.
 
Hey, Jamie,

Somehow I overlooked your reply to my post until just now while doing fall
housecleaning.

Thanks very much for the code; I'm sure I will find it useful in several
ways!

Gary
 
Back
Top