check for text in a memo field 4

[rene1000]

On a form i have created a list box and a memo field. The list box contain peoples names. If i double click on a name, the name is added to the memo field. I do however want to check if the name is allready in the memo field and warn the user if it is allready added.

Someone know how to achieve this ?
thanks,
Rene

 

[orna]

Private Sub MemoField_DblClick(Cancel As Integer)
If InStr(1, Me.MemoField, Me!ListBox) Then
MsgBox "The name was picked up"
End If
End Sub

 

[willir]

Rene

For the most part, Memo fields are treats like blobs and can not be searched. The one caveat is, and I only read this and do not have the tools to test, is that with Access 2002 / XP, you can search the first 255 characters.

(You did not want to hear this -- sorry)

This link is to an FAQ by one of the brighter Access stars...

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=4188

Richard

 

[BillPower]

Hi Rene,

This is a re-working of the code I suggested yesterday in thread702-660254 and works for Access Versions 95+

Place this in the After Update event of your Combo:

Dim ctlTextToSearch As Control, intFind As Integer, strFind As String
On Error GoTo Err_CarryOn
Set ctlTextToSearch = Me!MemoFieldName
ctlTextToSearch.SetFocus
With Me!MemoFieldName
strFind = Me!ComboBoxName
intFind = InStr(.Value, strFind)
.SetFocus
.SelStart = intFind - 1
.SelLength = Len(strFind)
End With
MsgBox strFind & " has already been entered!"
Screen.PreviousControl.SetFocus
Exit Sub
Err_CarryOn:
Screen.PreviousControl.SetFocus
MsgBox "Name hasn’t been entered, alright to continue!"'place your code to add selection to memeo field here

Change the names in red to your own control names.

If the name already exists, let the user know and do nothing. If the name isn't found an error is raised, replace the message box after Err_CarryOn: with your own procedure.

Bill

 

[willir]

Bill
You are amazing! No wonder you and a few seelct others are a million light years ahead of the pack.
Richard

 

[cuok]

Hi!
In billpower code ther is this part of code:

.....
With Me!MemoFieldName
strFind = Me!ComboBoxName
intFind = InStr(.Value, strFind)
....


How can i search text that have been selected in a memo (the highlited text).
I guess i have to replace the !ComboBoxName with somthing like '.SelStart'.

i'm using this code to search in memo field :

If IsNull(txtgetmed) Or txtgetmed = "" Then Exit Sub

Dim strSearch As String, intWhere As Integer
Dim ctlTextToSearch As Variant
Dim strMsg As String

strMsg = " Not Found"

With Me!txtDetail

strSearch = txtgetmed
intWhere = InStr(intStart, txtDetail, strSearch)
If intWhere Then
.SetFocus
.SelStart = intWhere - 1
.SelLength = Len(strSearch)
intStart = intWhere + Len(strSearch)

Else

strMsg = " The specified region: " & " has been searched!"
If intStart < 2 Then strMsg = &quot;Word(s) searched: &quot; & &quot;'&quot; & txtgetmed & &quot;'&quot; & vbNewLine & &quot; Not Found&quot; Else strMsg = &quot;Word(s) searched: &quot; & &quot;'&quot; & txtgetmed & &quot;'&quot; & vbNewLine & &quot; The specified region has been searched &quot;
MsgBox strMsg, vbInformation, &quot;CF.OK&quot;
End If

End With

thanks for any help
CUOK

 

[BillPower]

Hi cuok,

I'm not sure about what you want. Have assumed that &quot;txtgetmed&quot; is the control with the highlighted text that you want to search for in the memo field &quot;txtDetail&quot;.

Place this in the Declarations section of your form:
Dim strSelection As String

This in the On MouseUp event of the Control &quot;txtgetmed&quot;:
strSelection = Me!txtgetmed.SelText

Place this in the On Click event of a Form:
Dim ctlTextToSearch As Control, strFind As String, intFind As Integer
If IsNothing(strSelection) Then Exit Sub
Set ctlTextToSearch = Me!txtDetail
ctlTextToSearch.SetFocus
With ctlTextToSearch
strFind = strSelection
intFind = InStr(.Value, strFind)
.SetFocus
.SelStart = intFind - 1
.SelLength = Len(strFind)
End With

This is in my opinion a better Function than IsNull, palce this in a Module:
IsNothing = False
Select Case varType(varV)
Case vbEmpty
IsNothing = True
Case vbNull
IsNothing = True
Case vbString
If Len(varV) = 0 Then
IsNothing = True
End If
Case Else
IsNothing = False
End Select

This should find the 1st instance of the text selected in &quot;txtDetail&quot; in the Memo Field &quot;txtDetail&quot;. If you want to find all instances, if any, please let me know. As I said, I am a little unsure. Also, if I've got it wrong also let me know, but please give a bit more info on what you want.

I'm not going to be able to do much more tipping from now on as I've actually got a job, so could if there is anything else I can help on, let me know by the weekend.

Bill

 

[cuok]

Thank u billpower!

Here the time is 01:05
so i must sleep now and as u said i tell u what happend when we come back .
Thanks again
CUOK

 

[cuok]

Thank u again billpower!

ok i coukd not sleep till i checked it, here what i did:

On MouseUp event of the Control &quot;txtDetail&quot; (the memo):

txtgetmed = Me!txtDetail.SelText

where txtgetmed is the ctlTextToSearch in my code
it works fine!
thank u and have a star for your help and let me sleep earlier!
CUOK