Eliminating leading spaces in numbers in a combo box

  • Thread starter Thread starter ArkRoyal
  • Start date Start date
A

ArkRoyal

I have designed a database to record saving deposits made by members of a
local Credit Union. A number of members act as collectors on a rota basis,
when the office is open. The collection form has a combo box which is bound
to the member's ID number in a "Member" table. The combo box is set to
"Limited to List", and I use the "Not in List" event if a new member's ID
Number is to be added.

My problem is that several of the members seem to be pressing the space bar
before they start typing the members ID. This triggers the "Not In List"
event because it thinks that new new member is being added, causing a
duplicate name with a similar ID number to be added. How can I eliminate the
leading space before the "Not in List" event is triggered. I have tried
using the left() and Mid() functions as follows, but the "Not in List Event"
is still triggered.

If Left(Member ID,1) = " " then
Member ID = Mid(Member ID,2)
End If

I designed the database on Access2007, and saved it for use on Access2000.
 
Use the Trim function to remove spaces before and after the string when the
value is added to the table

= Trim(Value)
 
In addition

If the values are numeric, using the Val function will remove spaces
Val([Value])


About you code, for future reference, when using a field that is sperated to
two names or more, you need to put it in square brackets

If Left(Member ID,1) = " " then

Into
If Left([Member ID],1) = " " then
 
Ofer Cohen said:
In addition

If the values are numeric, using the Val function will remove spaces
Val([Value])


About you code, for future reference, when using a field that is sperated to
two names or more, you need to put it in square brackets

If Left(Member ID,1) = " " then

Into
If Left([Member ID],1) = " " then
--
Good Luck
BS"D


ArkRoyal said:
I have designed a database to record saving deposits made by members of a
local Credit Union. A number of members act as collectors on a rota basis,
when the office is open. The collection form has a combo box which is bound
to the member's ID number in a "Member" table. The combo box is set to
"Limited to List", and I use the "Not in List" event if a new member's ID
Number is to be added.

My problem is that several of the members seem to be pressing the space bar
before they start typing the members ID. This triggers the "Not In List"
event because it thinks that new new member is being added, causing a
duplicate name with a similar ID number to be added. How can I eliminate the
leading space before the "Not in List" event is triggered. I have tried
using the left() and Mid() functions as follows, but the "Not in List Event"
is still triggered.

If Left(Member ID,1) = " " then
Member ID = Mid(Member ID,2)
End If

I designed the database on Access2007, and saved it for use on Access2000.
Click to expand...
Click to expand...

Thanks for the advise on how to eliminate the space. I know about the
convention of putting [] around the 2 word field name, but it slipped my mind
that there might be some people who are new to Access and would be mislead by
the way I typed the function, so I extend my apologies to those people.
 
Back
Top