btw, I would love to break up the very long line, by adding _ & ( or what ever """" ' ' '"" ,quotes, double/double, single/double) so that the strsearch2 line is easy to read.....I have tried several times, and have watched the double/double double/trouble quote video too many times.....it still hurts my head....I think it is some sort of Kobayashi Maru Test created by a sick person at MS!

How can I eliminate creating a new record?  thanks

Are you setting a field as the default field?
Are you clicking into a field?
Do you have any fields with Default Values?
Does the > turn to a ✏️ ? i.e. is the record dirty?

Alex , thank you!!

Just your questions lead me instantly to my problem.  

Way back when, I had code to kick in on "get focus" of the prim key to set a "default" value based on incongruent criteria (not a constant), and that and that was causing the key field to update causing a new record to be generated.

I had subsequently rewrote the code to set the default after the MemName was "updated" as it would always have data, and never removed the "on focus"

all is well, except that I still can not get my line breaks in my code to work

Go watch the Convert Query SQL to VBA video.
Not only does Richard explain how to split out your SQL but you create a little tool to automatically do so.

Be sure to read the Comments below that video.  There are a couple of little errors that were caught afterward but the tool works great!

Thanks Scott, that is an awesome vid..I have watched it several times, including the extra.  I have watched the double double damn I am in trouble too many times....

It has not clicked and I think that I have zoned out...It will never click.  Unfortunately I created the code "by hand" so it is not in query SQL form , it is already in VBA form and the converter adds 6,000 """"" all over the place.

I am hoping that some one can just provide a sub for the bbbb and the cccc.  this one line works fine when it is one line, but when I put :

a line feed at the bbbb  and
tabs  at the cccc

the VBA goes to hell in a hand basket.

"SELECT * from A_MemberT where  ((MemName like ""*" & strText2 & "*"") bbbb
ccccor (MemMstrID like ""*" & strText2 & "*"") bbbb
ccccor (Group like ""*" & strText2 & "*"") bbbb
ccccor (MemIDer like ""*" & strText2 & "*"") bbbb
ccccor (email like ""*" & strText2 & "*"") bbbb
ccccor ([Managedby] like ""*" & strText2 & "*"") bbbb
ccccor (MemID like ""*" & strText2 & "*"")) "

Put your SQL into a variable.  Then use MsgBox to make sure it gets concatenated correctly.
     S = SELECT blah blah all on one line. (You said that works - correct?)
     MsgBox S
Run it

Now put in one line continuation - forget about the tab for the moment.  When you do that you have to close the quote for the first line and add the & _
Like this:  
    S = "SELECT * from A_MemberT where  ((MemName like ""*" & strText2 & "*"") " & _
or (MemMstrID like ""*" & strText2 & "*"") blah blah <- This will turn RED

Now since you started a new line you have to open your quote again right before the or   "or (MemMstrID like ""*" & strText2 & "*"") <-The rest of the line should turn black.

To test it do a MsgBox S to make sure it runs.    

Just do one at a time. Testing as you go.  It is a pain but you will eventually see the pattern in what you are doing.  Once you do about a kabillion times it will sink in.  There really isn't a magic bullet.  You just have to do it.

Once you have the line broken out like you want you can go add in your tabs to move it over and make it look pretty.
Access doesn't care about that.  It DOES matter on SPACE characters for your SQL within the quotes.

Thanks Scott,

That was easy just  add:        " & _
and on next line:                   "

Now I no longer have to use my double screen to review the code....wowzer