Form based on using Like query to direct to a specified record

[Ted111]

am having a problem with designing a form that will enable a client to view a record based on text from a long descriptive Memo field, and then to edit that record.

I believe that I have have mastered the input query, which I call from a form using a combo box

SELECT [Contacts].[Description]
FROM Contacts
WHERE ((([Contacts].[Description]) Like "*" & [enter any word] & "*&quot);

However, I can not figure out how to direct the form to proceed to that record. The standard code that I have been using with other combo boxes that draw upon queries using dropdown lists is:

Private Sub Combo25_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ContactID] = " & Str(Me![Combo25])
Me.Bookmark = rs.Bookmark
End Sub

What I would like is to scroll through the list of descriptions in the memo field that the search has found, and have the info from the selected record appear in the form. I can not imagine that this is a unique problem, and hope that someone could suggest how they have resolved it.

Thx for any assistance

 

[papparuff]

There are two ways you can deal with this problem. You can continue to use the memo field to store the description, or change the memo field to a text field. I will explain how to code for both but I recommend using the Text Field method.

MEMO Field Method

There are two limitations with using the memo field

1. You cannot use memo fields in either comboboxes or listboxes

2. The Find or FindFirst method does not work with Like or "*". So you must use the forms' Filter properties.

I would suggest placing a text control in the form's Form Header where the user can enter the word they want to search for and call it txtSearch. For the label of the text control you can enter something like "Enter word to search in Description Field:" so the user knows what the text control is for. In the text control's After Update event you can use the following code to search for the word the user enters.

Private Sub txtSearch_AfterUpdate()

Me.FilterOn = True
Me.Filter = "[Description] like '*" & txtSearch & "*'"

End Sub

You can then add a command button cmdShowAll and in it's click event add:

Private Sub cmdShowAll_Click()

DoCmd.ShowAllRecords

End Sub

This will clear the filtered records.

TEXT Field Method

There is one limitation and that is a text field can have no more than 255 characters in it.

Keep the txtSearch text box as described in the MEMO Field Method. In the txtSearch text box's After Update event you can have code first check to see if there is at least one record in the table that has the word the user is looking for in the Description field. If there is, then the program can populate your combobox. Once the user selects the appropriate record in the combobox, then the program will display the record. Here is the code:

(Note - you will have to change the names of the fields I use to the names you use as appropriate)

Private Sub cboSearch_AfterUpdate()
Dim rst As DAO.Recordset

If Not IsNull(cboSearch) Then
Set rst = Me.RecordsetClone

'Find the record that matches the control
With rst
.FindFirst "[SalesPersonID] = " & cboSearch

If .NoMatch Then
MsgBox "Record not found"
txtSearch.SetFocus
Else
Me.Bookmark = .Bookmark
FName.SetFocus
End If

.Close
End With
Set rst = Nothing
End If

End Sub

Private Sub cboSearch_GotFocus()

' Automatically drop down the
' cboSearch combobox
cboSearch.Dropdown

End Sub

Private Sub txtSearch_AfterUpdate()

Dim rst As DAO.Recordset
Dim strSQL As String

' Create a grouped SQL statement and count the
' number of records that match the data the user
' entered in the txtSearch text box
strSQL = "SELECT Count(SalesPersonID) AS CountOfSalesPersonID " & _
"FROM tblSalesman " & _
"WHERE Description Like '*" & txtSearch & "*'"
Set rst = CurrentDb.OpenRecordset(strSQL)

' Check to insure there are records in the table
If Not rst.BOF And Not rst.EOF Then
' If there are records in the table that match
' what the user entered in the txtSearch text box
' then set the cboSearch combobox's RowSource
' with the SQL statement
If rst!CountOfSalesPersonID > 0 Then
strSQL = "SELECT SalesPersonID, Description " & _
"FROM tblSalesman " & _
"WHERE Description Like '*" & txtSearch & "*' " & _
"ORDER BY Description"
cboSearch.RowSource = strSQL
' Requery the cboSearch combobox so that it has
' the latest records in it
cboSearch.Requery
Else
' Let the user know that the data the entered
' in the txtSearch text box could not be found
' in the table
MsgBox "The word " & Chr(34) & txtSearch & Chr(34) & _
" could not be found"
cboSearch.SetFocus
txtSearch.SetFocus
End If
Else
' Let the user know if there are no
' records in the table
MsgBox "There are no records in the table"
txtSearch.SetFocus
End If

rst.Close
Set rst = Nothing

End Sub

I've tested this and it works beautifully. I would suggest you add error checking though.

John Ruff - The Eternal Optimist