message user if he types in certain words in a sentence

[rainbows]

i have a table called stocklist and within that table i have a field called Materialpur. if possbile i would like a message to appear if the user uses certain words within a sentence
for example he could and is doing tying words in like
1. FILM to be in the "w room"
2 CAN to be made using "drawing" 111111
3 LID to "Drawing" "TBC"

so it any of the word in "" are used the message would appear

thanks steve

 

[arnelgp]

use the Change Event of the textbox and catch if the two -"double-quote" character is present in the Text property.

 

[rainbows]

sorry i have mislead you, the words that i want to highlight is the words that i have put in quotes these are the words he could type into the sentence . not that he is modifying the sentence

 

[The_Doc_Man]

i have a table called stocklist and within that table i have a field called Materialpur. if possbile i would like a message to appear if the user uses certain words within a sentence
for example he could and is doing tying words in like
1. FILM to be in the "w room"
2 CAN to be made using "drawing" 111111
3 LID to "Drawing" "TBC"

so it any of the word in "" are used the message would appear

thanks steve

Please clarify the timing of this message. For instance, do you want it to pop up the moment that word is typed, or do want it to pop up when the user has finished entering something and is about to move on to something else? (Different events are involved in those two cases.)

 

[rainbows]

when he has finished entering any of the words in that field is ok

 

[arnelgp]

the words that i want to highlight

is you field a Long Text (memo) so you can highlight it?

can you use BeforeUpdate event, but will not highlight, only show the words inside the double quote:

Private Sub yourTextbox_BeforeUpdate(Cancel As Integer)
    Dim s As String, i As Integer
    Dim n1 As Integer, n2 As Integer, ln As Integer
    s = [yourTextbox].Text
    ln = Len(s)
    If ln <> 0 Then
        For i = 1 To ln
            If Mid$(s, i, 1) = """" Then
                If n1 = 0 Then
                    n1 = i
                Else
                    If n2 = 0 Then
                        n2 = i
                    Else
                        Exit For
                    End If
                End If
            End If
        Next
        If n1 <> 0 And n2 <> 0 Then
            MsgBox Mid$(s, n1 + 1, n2 - n1 - 1)
        End If
    End If
End Sub

if you want it while it is being typed, use the Change Event (with same code).

 

[rainbows]

the text is short text , can we show the words that i mentioned if they are not in quotes ?

steve

 

[The_Doc_Man]

While you are typing, the control is in focus so that means you can do an "INSTR( 1, control-name.Text, "special-word" )" to see if the special word is in the control in question. You have to search the control's .Text as opposed to .Value because until you change focus or take some other definitive action, the incoming text is in the .Text property. It is not in the .Value property yet. If you trigger an action that would leave the control, you will also trigger the control.BeforeUpdate event, which would be where you can test once for the content you seek. Once the control actually loses focus, it WILL update the contents of .Text to become the new .Value for that control.

Using the Change event has a secondary issue that once you find the string you wanted to find, you will KEEP on finding it with each new character, so you have to "throttle" what you do with some sort of flag that tells you "don't do this again, we've already notified the user." You can find the words you want but formatting will depend on the control in question.

You asked about "showing the words" in post #3 which includes some type of highlighting? It is possible to specify limited HTML formatting for text boxes so I guess you could highlight something if you needed to. Access isn't a web-page tool so has limited graphics abilities along those lines. I've included a couple of links that might be helpful.

Access Rich-Text: Which subset of HTML is supported?

Microsoft Access text boxes can be configured as "Rich Text", allowing the user to apply formatting such as bold text or different font sizes. Internally, this "rich text" is stored as HTML. Since...

stackoverflow.com

TextBox.BeforeUpdate event (Access)

Office VBA reference topic

learn.microsoft.com

 

[CJ_London]

You show 3 ‘special words’ - how many are there?

assuming they are in a table then in the after update event of the text box something like


dim rs as dao.recordset
Dim sqlstr as string

sqlstr=“select word from tblwords where ‘“ & txtbox & “‘ like ‘*’ & word & ‘*’”

set rs = currentdb.openrecordset sqlstr
if Not rs.eof then
Msg box rs!word & “ used”
end if

on my phone so apologies for typos and presentation

 

[rainbows]

MaterialPUR
Insulation board for EC4272M2 with recess for terminal head(Dwg TBC)

Film mPP 11.7 µm x 62.5 mm DEM 2.5 - High Isotactic - dwg 9504
Film pPP 9µm x 35mm PPDC (W Room )



just to put you in the picture this company uses excel and word for all there systems and i have been asked to try and put it all in access

these are 3 sentences they use of which are wrong , as i cannot send a purchase order out with the words TBC nor w room in it
we have a drawing number column to put drawing numbers in so i dont want it in the text

i know the code is not good as i always try and learn how to make things work and i am certain there is but i dont know how to do that
one of the members told me i should try and keep away from the word " like " when i was doing a query

thanks
steve

Private Sub Purchasing_BeforeUpdate(Cancel As Integer)
Dim val As String:

val = Me.MaterialPUR
If val Like "*dwg*" Then
val = "dwg"
MsgBox "DONT USE THE WORD " + val
Else
If val Like "*TBC*" Then
val = "tbc"
MsgBox "DONT USE THE WORD " + val
Else
If val Like "*w room*" Then
val = "w room"
MsgBox "DONT USE THE WORD " + val

End If
End If
End If

End Sub

 

[arnelgp]

you are in a problem alright.
the problem of lack of Standards.

i have been a Material Controller for Refinery/OIl and Gas constructions for more than 20 years.
and yes each project, we have ISO drawings.
and each ISO, there are Bill of materials (BOM).

the Standard is:
2 (or more) data entry personel, enter each drawing number and each materials in the bom to an Excel/Access.
everytime there is Revision/new drawing, the list is updated.

Material request coming from construction group.
they are Required to submit, together with their requisition a Copy of
the latest revision of the ISO, where the material are being requested.
therefore, we can also update our Excel/Access which drawing this
material belongs.

whatever narrative they write on the requisition, as long as they write
the Ident_number (material number in the drawing), we can still Pick
the correct "material description" from our Excel/Access list by matching
the ident_number (sometimes they just encircle it in the drawing).

if the material being requested has correct description in the requisition,
we just mark them with check. otherwise we write out the correct description
and highlighted (or put "cloud" around it) it on the requisition form.

 

[rainbows]

i have a stock list and in the stocklist they have been putting words in that just suits them , then from the stocklist they created BOMS from that stocklist each bom is for an assembles part let says 20 stock items per assembly
at this time the database that i along with everyones help on here it runs a query and lists all the parts required for all assemblies and rums that query upto 91 days in advance
therefore now the parts are in a requistion list so then i apply the P/o No and supplier etc and the p/o / p/o's are ready and can be ordered say 15 of them , so i want all the information in the stocklist correct so i dont have to change anything


ARNELGP
small world i build the motors and generators for Refinery/OIl and Gas up to 200 mw

steve

 

[CJ_London]

one of the members told me i should try and keep away from the word " like " when i was doing a query

Think the member either gave incomplete advice or you have misinterpreted it.

You shouldn’t use it as an alternative to = and when using it avoid if possible using the initial ‘*’ as this prevents the use of indexes. Sometimes this isn’t possible- as in your requirement, sometimes there are are better ways to achieve the objective. Sometimes the number of comparisons are limited so no impact on performance

in your initial post ‘.drawing’ is not allowed, in post 10, you exclude ‘dwg’ and ‘drg’ but not drawing

even if it is a short list I still think better to use a table to store the options