Need help automating client id entry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have built a database that links to a client table from another software
package.

For some reason, however, client #'s in this other source sometimes have
spaces in front of them. There is a pattern however.

If the client # is 3 digits, it has 7 spaces in front of it.
If the client # is 4 digits, it has 6 spaces in front of it.
If the client # is 3 or 4 digits plus a letter, it has 0 spaces in front of
it.

The form I built has a combo that is currently very cumbersome to use
because the user has to either enter the spaces or scroll through hundreds of
clients to get to the one they want.

Is there a way to automate the data entry with some VBA code that would
evaluate the client # typed and then add the appropriate amount of spaces
when necessary?

Any help appreciated.
 
Sounds like it is simply right justifying numbers and left justifying
letters. Pretty common.

Why would you care about leading spaces? In the query for your combo box,
just trim the entry so spaces are ignored as you work with the data.
 
Instead I would suggest modifying the rowsource query of the combo - create
a calculated field based on the Client# field then use the calculated field
as the first visible field in the combo.

In the query designer you would type the following for your new field:

txtClientNum: trim([Client Num])

Change "Client Num" to the name of the actual field in the table.
 
Back
Top