trim dropdown value before executing query

Access 2010 vba

I have a combobox where the row source is a query.
When a value/copied and pasted./ typed into the combo box.

The query brings back the result.

I want to trim the value pasted before the query executes ?


me.cbo_Names = trim(me.cbo_names)

Thanks
fordraiders
LVL 3
FordraidersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
if this was a textbox, you could correct the value on the BeforeUpdate event of the control.  However, since this is a combobox, that may or not be possible ... what is the RowSource, ColumnCount, and ColumnWidths?

Alternately, you can perhaps reference the control in the query as:

trim(forms!MyFormName!cbo_names & "")
0
PatHartmanCommented:
Crystal is correct.  Since this is a combo, you cannot alter the displayed value.  We need to step back and look at what caused the problem.  Is the combo based on a single column list?  Is it a value list or does it come from a table.  If the values contain extraneous spaces, you will need to fix the source.  Trimming in the query won't work because referential integrity would fail.  You did establish RI didn't you?
1
Helen FeddemaCommented:
Also, what is the purpose of trimming the value?  Trim() just removes extra space.  Do you need something more than that?
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jeffrey CoachmanMIS LiasonCommented:
Agree with Pat...
We need to step back and look at what caused the problem.
Can you post the code and the query you are using now?
It is not clear how this value is being passed to the query, ...or what event of the combo you are using...

What you are asking for here, (If I am understanding you correctly), works fine for me in this very basic attached sample...
Database22.accdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FordraidersAuthor Commented:
just trying to remove spaces
0
FordraidersAuthor Commented:
Thanks all...
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome ~ happy to help

if the combo is not limited to the list, provided it is a string, you can do this on the control After Update event:
with me.controlname
   if isnull(.value) then exit sub
   .value = trim(.value)
end with

Open in new window

0
Jeffrey CoachmanMIS LiasonCommented:
Glad you were able to get what you were after.
;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.