trim dropdown value before executing query

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015
Commented:
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 & "")
Distinguished Expert 2017
Commented:
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?
Top Expert 2009

Commented:
Also, what is the purpose of trimming the value?  Trim() just removes extra space.  Do you need something more than that?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

MIS Liason
Most Valuable Expert 2012
Commented:
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

Author

Commented:
just trying to remove spaces

Author

Commented:
Thanks all...
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
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

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Glad you were able to get what you were after.
;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial