Ignoring spaces in combobox text

[Thelma Lubkin]

I am trying to limit the user's options to the choices offered by the
combobox text, but I don't want this to extend to how [s]he spaces out
the text, e.g.
'abcdefg' and 'ab cd efg'
should be equivalent. I know how to compare strings to see whether
they meet this criterion--I don't know how to let the combobox know
that this is what I want.

I am thinking of dealing w/ this in the NotInList_event, but I don't
know how to get to the combobox's valid values.
I also have a feeling that I should be able to set up a validation
criterion that will make this trivial...

thanks, --thelma

[Arno R]

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> schreef in bericht news:d7**********@uwm.edu...

I am trying to limit the user's options to the choices offered by the
combobox text, but I don't want this to extend to how [s]he spaces out
the text, e.g.
'abcdefg' and 'ab cd efg'
should be equivalent. I know how to compare strings to see whether
they meet this criterion--I don't know how to let the combobox know
that this is what I want.

I am thinking of dealing w/ this in the NotInList_event, but I don't
know how to get to the combobox's valid values.
I also have a feeling that I should be able to set up a validation
criterion that will make this trivial...

thanks, --thelma

The NotInList_event only occurs when LimitToList is set to True ...
So I think this is not what you want.

I would set LimitToList to false and validate the input (spaces removed ?) in the BeforeUpdate_Event.
You could use a DLookup against a query (maybe the rowsource for your combo?) for this.

Arno R

[Thelma Lubkin]

Arno R <ar***********@tiscali.nl> wrote:

: "Thelma Lubkin" <th****@alpha2.csd.uwm.edu> schreef in bericht news:d7**********@uwm.edu...
:>I am trying to limit the user's options to the choices offered by the
:> combobox text, but I don't want this to extend to how [s]he spaces out
:> the text, e.g.
:> 'abcdefg' and 'ab cd efg'
:> should be equivalent. I know how to compare strings to see whether
:> they meet this criterion--I don't know how to let the combobox know
:> that this is what I want.
:> <snip>

: The NotInList_event only occurs when LimitToList is set to True ...
: So I think this is not what you want.

: I would set LimitToList to false and validate the input (spaces removed ?) in the BeforeUpdate_Event.
: You could use a DLookup against a query (maybe the rowsource for your combo?) for this.

Thank you -- I think that this is exactly what I want.

I want to use the NotInList trigger because if I set
LimitToList false I will need to decide whether I need to go
through a Dlookup: if I limit to list the extra code will
only be seen for text that doesn't look legal to the
combobox
thank you for the help.
--thelma
: Arno R

[Arno R]

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> schreef in bericht news:d7**********@uwm.edu...

: The NotInList_event only occurs when LimitToList is set to True ...
: So I think this is not what you want.

: I would set LimitToList to false and validate the input (spaces removed ?) in the BeforeUpdate_Event.
: You could use a DLookup against a query (maybe the rowsource for your combo?) for this.

Thank you -- I think that this is exactly what I want.

I want to use the NotInList trigger because if I set
LimitToList false I will need to decide whether I need to go
through a Dlookup: if I limit to list the extra code will
only be seen for text that doesn't look legal to the
combobox
thank you for the help.

I advised you to set LimitToList to false, so this means the event does NOT fire then.
If you insist in LimitToList you can't accept values with spaces UNLESS you add these values in the combo ...
I don't think this is what you want, or ???

So back to the original advice:
-- Set LimitToList to false
-- Validate the input against a query (your combo for instance) in the BeforeUpdate-event
(You will remove the spaces in a variable I guess ??)
-- If validated then continue (value is accepted)
-- If not validated then set cancel to True (force the user to another valid input)

I assume that you have a function to remove the spaces as you told.
I also assume that you know how to use DLookup
If not post back (in this thread) please.
For this moment I am not sure where *exactly* you are having problems, other than using LimitToList where IMO you should not.

Arno R

[Thelma Lubkin]

Arno R <ar***********@tiscali.nl> wrote:
: I advised you to set LimitToList to false, so this means the event
: does NOT fire then.
: If you insist in LimitToList you can't accept values with spaces
: UNLESS you add these values in the combo ...
: I don't think this is what you want, or ???

This is how LimitToList has been acting for me: when set to
true, possible valid choices are displayed in the combo-box input
window only as long as the user's text matches a valid choice, but
[s]he can continue to enter text until hitting <return>--
then the NotInList event happens and I put my further
processing of the user's invalid, as typed, text there.

In skeleton sketch:

-------------------------------------------------------------------------
Private Sub cboChooser_NotInList(NewData As String, Response As
Integer)

Dim Blank, Q2 As String
Blank = " "
Q2 = ""

Dim A_ID As Integer
Dim DataVal As String

' ---DataVal = User choice w/ blanks removed---
DataVal = Replace(cboChooser.Text, Blank, Q2)

' ---This Doesn't Work
A_ID = DLookup("[AA_ID]", "A0toD", "Replace(" & _
[Name] & ", Blank, Q2) = '" _& DataVal & "'")

' ----This one Works, so something is wrong w/ Replace handling---
' A_ID = DLookup("[AA_ID]", "A0toD", "[Name] = '" & DataVal & "'")

If A_ID = Null Then
MsgBox cboChooser.Text & " is not in the DataBase"
cboChooser.Value = Null
Response = acDataErrContinue 'suppress Access message
Else
MsgBox "HEREIAM: " & A_ID
' Will process *valid* input here
End If
End Sub
-------------------------------------------------------------------------

When I input badly spaced data to the combo-box, Access responds with

"Run-time error '2001' You canceled the previous operation"

I think that this is happening because I have errors in the Dlookup
statement: I'm getting lost in the placement of brackets, single
quotes, double quotes, parentheses...

thank you for looking --thelma
: So back to the original advice:
: -- Set LimitToList to false
: -- Validate the input against a query (your combo for instance) in the BeforeUpdate-event
: (You will remove the spaces in a variable I guess ??)
: -- If validated then continue (value is accepted)
: -- If not validated then set cancel to True (force the user to another valid input)

: I assume that you have a function to remove the spaces as you told.
: I also assume that you know how to use DLookup
: If not post back (in this thread) please.
: For this moment I am not sure where *exactly* you are having problems, other than using LimitToList where IMO you should not.

: Arno R

[Arno R]

Suppose "abcde" is a valid entry.
Suppose I type "abc de". What do you want to happen ?
You want this to be accepted, Yes ?

Do you want to add this value "abc de" to your combo as a valid entry?
If yes, you can use LimitToList =True
You can add the value to the list with code in the NotInList-event and continue.
If no (as I suspect), IME you can NOT use LimitToList =True.
But in that case you can easily validate the entry's in the BeforeUpdate-event as I explained.
You can use almost the very same code in the BeforeUpdate_event of the combo.

In pseudocode: (LimitToList =FALSE here ...)
Private Sub cboChooser_BeforeUpdate(Cancel As Integer)
Grab the value, remove the spaces and put this in your var DataVal, and then ...
If not IsNull(DLookup("[AA_ID]", "A0toD", "[Name] = '" & DataVal & "'") ) then
'OK here except for the fact that you seem to use a reserved word 'Name' in the table ... You will have to fix this
else
Msgbox "This is not valid, blah blah"
Cancel = true
end if
End Sub

If you insist in using LimitToList = True, then I can't help you

Arno R

[Thelma Lubkin]

Arno R <ar***********@tiscali.nl> wrote:
: Suppose "abcde" is a valid entry.
: Suppose I type "abc de". What do you want to happen ?
: You want this to be accepted, Yes ?

Not quite.
Suppose "ab cde" is a valid entry
I type "abcd e".
I want this to be accepted, so I need to
remove the spaces not only in the user's text,
but also in the field's value before I compare the two.

: Do you want to add this value "abc de" to your combo as a valid entry?
: If yes, you can use LimitToList =True
: You can add the value to the list with code in the NotInList-event
: and continue.
: If no (as I suspect), IME you can NOT use LimitToList =True.

You are right about this.

: But in that case you can easily validate the entry's in the
: BeforeUpdate-event as I explained.

I will never use this form for anything but *viewing* data, so I
find the idea of depending on the BeforeUpdate_event a little
strange. After the entry is accepted, the form will call another form
to display data; the ID value retrieved determines
for which entity to display information. I thought that I
could put that call to the next form in the NotInList event code
and thereby 'escape'...but if I can't I'm of course ready to set
LimitToList false and depend on the BeforeUpdate_event [although
it seems to me that the OnClick_event would be more relevant]
: You can use almost the very same code in the BeforeUpdate_event
: of the combo.

: In pseudocode: (LimitToList =FALSE here ...)
: Private Sub cboChooser_BeforeUpdate(Cancel As Integer)
: Grab the value, remove the spaces and put this in your var DataVal,
: and then ...
: If not IsNull(DLookup("[AA_ID]", "A0toD", "[Name] = '" & DataVal &
: "'") ) then
: 'OK here except for the fact that you seem to use a
: reserved word 'Name' in the table ... You will have to fix this

Sorry about the 'Name' thing--I actually changed all the actual
table and field names for the sake of brevity, and I thought,
clarity--messed that up too...

But the real problem now is in that dlookup: I need to compare the
user's 'despaced' text, not with the field's actual text, but
with the field's 'despaced' text

When I try to use the Replace function on the Name field in the dlookup
criteria, I get an error message that implies that I have a
datatype mismatch--this is the strange error message I mention
in the previous post, something like
"You have canceled this operation"

Please forgive me for sounding so dense and cantankerous
I'm not really cantankerous, but I'm beginning to feel
awfully dense...
--thelma

: else
: Msgbox "This is not valid, blah blah"
: Cancel = true
: end if
: End Sub

: If you insist in using LimitToList = True, then I can't help you

: Arno R

[Arno R]

"Thelma Lubkin" <th****@alpha2.csd.uwm.edu> schreef in bericht news:d7**********@uwm.edu...

Not quite.
Suppose "ab cde" is a valid entry
I type "abcd e".
I want this to be accepted, so I need to
remove the spaces not only in the user's text,
but also in the field's value before I compare the two. You can use a Variable (as I thought you did with your 'DataVal') to compare.
No need to change the field's value at this point.
I will never use this form for anything but *viewing* data, so I
find the idea of depending on the BeforeUpdate_event a little
strange. After the entry is accepted, the form will call another form
to display data; the ID value retrieved determines
for which entity to display information. I thought that I
could put that call to the next form in the NotInList event code
and thereby 'escape'...but if I can't I'm of course ready to set
LimitToList false and depend on the BeforeUpdate_event [although
it seems to me that the OnClick_event would be more relevant]
I understand what you are trying to do now, (at least I think I do ...)
Your form may be *only for viewing*, but there still is a BeforeUpdate_event for the combo.
This event is *the* event for validating purposes; there is a 'Cancel' at your disposal to *force* a valid entry.
The OnClick_event is NOT a good choice IMO. (you enter the combo here, probably no input yet )
A better choice would be AfterUpdate then ... (you leave the combo here, probably user typed 'something')
But the real problem now is in that dlookup: I need to compare the
user's 'despaced' text, not with the field's actual text, but
with the field's 'despaced' text
Again:
Private Sub cboChooser_BeforeUpdate(Cancel As Integer)
Dim strValueToValidate as string
'Grab the value, remove the spaces
strValueToValidate = DeSpacedValueThatUserTypedInTheCombo
'Next two lines one the same line !
If not IsNull(DLookup("NameOfLookupField", "NameOfLookupTableOrQuery",
"[NameOfLookupField] = '" & strValueToValidate & "'") ) then
'Everything OK here, proceed opening the specific form for this value...
Else
Cancel = true 'user CAN NOT leave the combo unless valid input or 'Undo'
'You can inform the user and FORCE a valid input by using the BeforeUpdate_event
'If you want the user to 'get away' with this then use the SAME code (without Cancel =true) in the AfterUpdate_event
End if
When I try to use the Replace function on the Name field in the dlookup
criteria, I get an error message that implies that I have a
datatype mismatch--this is the strange error message I mention
in the previous post, something like
"You have canceled this operation"

It is not a strange error... IMO it's a bit strange to do it this way ....
You ONLY use the Replace-function to 'despace' user-input and to put the despaced value in the var strValueToValidate.
Don't use the function again in the DLookup!

I am getting the impression that you need or want the 'despaced' value in the combo to proceed with opening your form?
If so: Use a hidden control and put the value (AFTER validation) in this control.
You could use the AfterUpdate_event to fill the hidden control and proceed from here ...

I am sorry if I don't understand you correctly...
If so: let me know. I will stay with you until this is 'solved'.

Arno R

[rkc]

Thelma Lubkin wrote:

Arno R <ar***********@tiscali.nl> wrote:
: Suppose "abcde" is a valid entry.
: Suppose I type "abc de". What do you want to happen ?
: You want this to be accepted, Yes ?

Not quite.
Suppose "ab cde" is a valid entry
I type "abcd e".
I want this to be accepted, so I need to
remove the spaces not only in the user's text,
but also in the field's value before I compare the two.

why not something as simple as:

For i = 1 To Me.Combo0.ListCount
If removeSpace(Me.Combo0.ItemData(i)) _
= removeSpace(Me.Combo0.value) Then
' input is acceptable
Exit For
End If
Next

[Thelma Lubkin]

Arno R <ar***********@tiscali.nl> wrote:

: "Thelma Lubkin" <th****@alpha2.csd.uwm.edu> schreef in bericht
: news:d7**********@uwm.edu...
:> Not quite.
:> Suppose "ab cde" is a valid entry
:> I type "abcd e".
:> I want this to be accepted, so I need to
:> remove the spaces not only in the user's text,
:> but also in the field's value before I compare the two.
: You can use a Variable (as I thought you did with your 'DataVal')
: to compare.
: No need to change the field's value at this point.

:> But the real problem now is in that dlookup: I need to compare the
:> user's 'despaced' text, not with the field's actual text, but
:> with the field's 'despaced' text

: Again:
: Private Sub cboChooser_BeforeUpdate(Cancel As Integer)
: Dim strValueToValidate as string
: 'Grab the value, remove the spaces
: strValueToValidate = DeSpacedValueThatUserTypedInTheCombo
: 'Next two lines one the same line !
: If not IsNull(DLookup("NameOfLookupField", "NameOfLookupTableOrQuery",
: "[NameOfLookupField] = '" & strValueToValidate & "'") ) then
: 'Everything OK here, proceed opening the specific form for this value...

The above criterion for the example above would be
"ab cde" =? "abcde"
and the lookup would fail because the 2 strings are still different
I will use the BeforeUpdate_event though.

: I am getting the impression that you need or want the 'despaced'
: value in the combo to proceed with opening your form?

No, the form opens on the ID related to the actual value --
I want to use dlookup to retrieve the ID field with the
criterion based on 'despaced' equality of the user input
and the name field
I make an assumption -- and I'll probably get burned by it --
that my data is restricted enough that I will never have 2
valid values in this field which differ *only* in their
spacing, e.g.

user types Mary Jane Jones
database has MaryJane Jones

I want my validation criterion to accept these as the same name

: I am sorry if I don't understand you correctly...
: If so: let me know. I will stay with you until this is 'solved'.

Thank you very much: I grew up in a UNIX, and dare I admit
it, all the way back to Univac, environment. I've been able
to avoid Windows for a very long time, and I'm finding the
transition hard. I only run Mac OS X and SCO UNIX at home, so
I don't get enough time to play with it.
--thelma
: Arno R

[Thelma Lubkin]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
: Thelma Lubkin wrote:
:> Arno R <ar***********@tiscali.nl> wrote:
:> : Suppose "abcde" is a valid entry.
:> : Suppose I type "abc de". What do you want to happen ?
:> : You want this to be accepted, Yes ?
:>
:> Not quite.
:> Suppose "ab cde" is a valid entry
:> I type "abcd e".
:> I want this to be accepted, so I need to
:> remove the spaces not only in the user's text,
:> but also in the field's value before I compare the two.
:>

: why not something as simple as:

: For i = 1 To Me.Combo0.ListCount
: If removeSpace(Me.Combo0.ItemData(i)) _
: = removeSpace(Me.Combo0.value) Then
: ' input is acceptable
: Exit For
: End If
: Next

I like Arno's idea of using Dlookup because then the
looping is done internally and the code looks less busy.

But the real reason I didn't try something like this is
that I couldn't figure out how to reach the items in the
combobox. Thanks for showing me this. I'm finding that
there's a huge amount of documentation out there, but it's
not easy to give google the words that flush out what I
need.
--thelma

[rkc]

Thelma Lubkin wrote:

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
: Thelma Lubkin wrote:
:> Arno R <ar***********@tiscali.nl> wrote:
:> : Suppose "abcde" is a valid entry.
:> : Suppose I type "abc de". What do you want to happen ?
:> : You want this to be accepted, Yes ?
:>
:> Not quite.
:> Suppose "ab cde" is a valid entry
:> I type "abcd e".
:> I want this to be accepted, so I need to
:> remove the spaces not only in the user's text,
:> but also in the field's value before I compare the two.
:>

: why not something as simple as:

: For i = 1 To Me.Combo0.ListCount
: If removeSpace(Me.Combo0.ItemData(i)) _
: = removeSpace(Me.Combo0.value) Then
: ' input is acceptable
: Exit For
: End If
: Next

I like Arno's idea of using Dlookup because then the
looping is done internally and the code looks less busy.

It's your choice of course. I was just pointing out another option.

The problem your having with dlookup and the replace function is
probably caused by not enclosing the value returned by the replace
function in quotes. dlookup needs strings to be enclosed in quotes
regardless of where they come from.

For example:

DLookup("[Field1]", "ULTMDICT", "[field1]= " & "'" & Replace("AB AC I",
" ", "")& "'")

Now that is definately busy looking code. And it it gets even
more so if you need to handle imbeded single or double quotes.

[Thelma Lubkin]

rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
: Thelma Lubkin wrote:
:> rkc <rk*@rochester.yabba.dabba.do.rr.bomb> wrote:
:> : Thelma Lubkin wrote:
:> :> Arno R <ar***********@tiscali.nl> wrote:
:> :> : Suppose "abcde" is a valid entry.
:> :> : Suppose I type "abc de". What do you want to happen ?
:> :> : You want this to be accepted, Yes ?
:> :>
:> :> Not quite.
:> :> Suppose "ab cde" is a valid entry
:> :> I type "abcd e".
:> :> I want this to be accepted, so I need to
:> :> remove the spaces not only in the user's text,
:> :> but also in the field's value before I compare the two.
:> :>
: The problem your having with dlookup and the replace function is
: probably caused by not enclosing the value returned by the replace
: function in quotes. dlookup needs strings to be enclosed in quotes
: regardless of where they come from.

: For example:

: DLookup("[Field1]", "ULTMDICT", "[field1]= " & "'" & Replace("AB AC I",
: " ", "")& "'")

: Now that is definately busy looking code. And it it gets even
: more so if you need to handle imbeded single or double quotes.

The really bad part is that I want not

"[field1]= " & "" '" & Replace("AB AC I", " ", "")& "'"

but I need to replace the spaces in the *field* too before
making the comparison -- *that's* what your loop does so
nicely. The second Replace can be fed to Dlookup as an
already computed variable; the first (on the field) Replace
is the problem.

...and when I look from your perspective, maybe I *am*
mischaracterizing which is cleaner.

--thelma

[Arno R]

> user types Mary Jane Jones

database has MaryJane Jones

I finally understand why you need to 'despace' the field in the Lookuptable also.
I think *I* am one of the 'dense' people...

First idea that comes to my mind now is that I would create the 'despaced' values in the LookupQuery:
Database has:
MaryJane Jones
JoAnne Cook

LookupQuery has: (use the 'despace function' here)
MaryJaneJones
JoAnneCook

This way the validation would be a simple Lookup again where you only need to 'despace' the user-input.

Arno R

[Thelma Lubkin]

Arno R <ar***********@tiscali.nl> wrote:
:> user types Mary Jane Jones
:> database has MaryJane Jones

: I finally understand why you need to 'despace' the field in the
: Lookuptable also.
: First idea that comes to my mind now is that I would create the
: 'despaced' values in the LookupQuery:
: Database has:
: MaryJane Jones
: JoAnne Cook

: LookupQuery has: (use the 'despace function' here)
: MaryJaneJones
: JoAnneCook

: This way the validation would be a simple Lookup again where you
: only need to 'despace' the user-input.

I was thinking of that as an option if all else fails, but
rkc (I think) has shown me how to reach the combobox's
contents--somehow in all my documentation searching I never
could find that--and I think I'll try his/her loop.

Thanks for the help. I wish I could someday reciprocate, but I
doubt whether I'll ever know something that you don't.
--thelma
: Arno R