Drop Down Auto Entry and Alike Functions

[C. Sharp]

We have converted our database to Access 2007 and I'm noticing 2 "features"
that do not work like they used to.

Feature #1: When designing a query and I enter in the criteria the
following: Like "Need Itinerary" Or "Need Itin" Or "Pending" it coverts the
Like to Alike and my criteria does not work. I've tried to look up what
Alike is but cannot find any info. What is this function and how does it
work?

Feature #2: We changed the design of our DB to have Tables for our drop
down lists. The fields appear correctly but we have to use our mouse to
select the entry from the drop down list. We used to be able to start typing
the entry in the field, i.e. Atl and it would assume Atlanta. We would then
hit our tab key and not have to coninue typing. How can we make this work
again? When doing data entry this really slows the process down.

Thanks in advance for your help!
CSharp

 

[Clifford Bass]

Hi,

Well, I cannot address why it changes to ALike, although that seems to
work for me. But why use the like at all if you are not using wild cards?
And [Field] Like "Need Itinerary" Or "Need Itin" Or "Pending" translates to:

where [Field] like "Need Itinerary" or [Field] = "Need Itin" or [Field] =
"Pending"

which is equivalent to:

where [Field] = "Need Itinerary" or [Field] = "Need Itin" or [Field] =
"Pending"

Is that what you really are wanting? If you really are wanting to
locate any of those items anywhere in [Field] then use this instead:

Like "*Need Itinerary*" Or Like "*Need Itin*" Or Like "*Pending*"

If it insists on changing it to ALike, change the asterisks (*) to
percents (%).

About the combo box, make sure its Auto Expand property is set to Yes.

Hope that helps,

Clifford Bass

 

[Jeff Boyce]

If I recall, the setting under Access Options that controls the Like -->
Alike is under Object Designer, Query Design. Look for the settings
concerning "SQL Server Compatible Syntax (ANSI 92)" and try fiddling with
that setting...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

.... second issue...

I can't tell from your description whether you are trying to do this
directly in the tables or (as more appropriate and recommended) in forms.
Use the forms, Luke!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[C. Sharp]

Cliff -
The * did not work but the % signs was the ticket with the Alike function!
Thanks!

I'm still having an issued with #2. I guees its been a long day and I'm
just not seeing it. I have looked on both the properties on my table and
form and do not see the auto expand option. Where exactly is this supposed
to show up? What tab?

This if an example of how I want to use this feature. I created a table for
salutations: Dr. Mr. Mrs. Ms. etc... I then used a combo box and this the
table_Salutation as my control source in my Contacts table. But when I open
either the table or form for my Contacts and starty typing the letter "d" it
still does not auto expand to Dr. It won't expand to anything! It just sits
there and lets me type whatever my heart desires. What am I doing wrong?

Hi,

Well, I cannot address why it changes to ALike, although that seems to
work for me. But why use the like at all if you are not using wild cards?
And [Field] Like "Need Itinerary" Or "Need Itin" Or "Pending" translates to:

where [Field] like "Need Itinerary" or [Field] = "Need Itin" or [Field] =
"Pending"

which is equivalent to:

where [Field] = "Need Itinerary" or [Field] = "Need Itin" or [Field] =
"Pending"

Is that what you really are wanting? If you really are wanting to
locate any of those items anywhere in [Field] then use this instead:

Like "*Need Itinerary*" Or Like "*Need Itin*" Or Like "*Pending*"

If it insists on changing it to ALike, change the asterisks (*) to
percents (%).

About the combo box, make sure its Auto Expand property is set to Yes.

Hope that helps,

Clifford Bass

We have converted our database to Access 2007 and I'm noticing 2 "features"
that do not work like they used to.

Feature #1: When designing a query and I enter in the criteria the
following: Like "Need Itinerary" Or "Need Itin" Or "Pending" it coverts the
Like to Alike and my criteria does not work. I've tried to look up what
Alike is but cannot find any info. What is this function and how does it
work?

Feature #2: We changed the design of our DB to have Tables for our drop
down lists. The fields appear correctly but we have to use our mouse to
select the entry from the drop down list. We used to be able to start typing
the entry in the field, i.e. Atl and it would assume Atlanta. We would then
hit our tab key and not have to coninue typing. How can we make this work
again? When doing data entry this really slows the process down.

Thanks in advance for your help!
CSharp

 

[Clifford Bass]

HI,

You are welcome.

From what you describe, it does indeed sound like Auto Expand is set to
No. The Auto Expand property is on the Data tab, second from the bottom. It
is also possible that you may want to set the Limit To List property to Yes.
However, that does not impact the issue.

Clifford Bass

 

[C. Sharp]

Clifford -
I found the auto expand property on my form and it was already set to Yes as
was the limit to list property. Any other suggestions?!?!?!

 

[Clifford Bass]

Hi,

In rereading your prior post I just realized that you may have it set
up incorrectly. Is the Control Source really the table? Or was that just a
mistyping? It should be the salutation field in your Contacts list. And the
table should be the Row Source of the combo box.

If that is not it you could try a compact and repair. And if that does
not do it, try a little test. Create a new table with one field--salutation.
Don't put anything in the LookUp part of the table field properties. Create
a new form for the table with the field as a combo box, set up appropriately
to use the salutation table as its row source. Try typing d and see what
happens. If you find it still will not work, that could be an indication of
some kind of corruption. In which case I would probably create a new 2007
database, import all of the stuff from the current database. Make sure the
references match up with the current database (VBA Editor, Tools menu,
References), do a compile in the VBA Editor (Debug menu, Compile Database),
and then see if the combo boxes work.

Hope one of those helps,

Clifford Bass

 

[C. Sharp]

Cliff,
Well, I tried creating the new table/form like you suggested and even
compacted and repaired but no luck.

I also verified Control Source = Salutation and Row Source = SELECT
TBL_REF_Salutation.[Salutation ID], TBL_REF_Salutation.Salutation FROM
TBL_REF_Salutation ORDER BY TBL_REF_Salutation.Salutation; Row Source Type -
Tabel/Query and bound column is 2 since 1 is the ID field for the salutations.

What does Show Only Row Source Value do? It is set to NO right now.

I also noticed that if I actually entered one of the items in the drop down
list, i.e. Dr. exactly like it is on the Table_Salutation it said "The text
you entered isn't an item in the list. Select an item from the list, or
enter text that matches one fo the listed items." So I definitely have
something wacky going on!

 

[Clifford Bass]

Hi,

Time to back up a bit. If you have the thing defined also in the table
design on the salutation field's lookup tab, change the Display Control back
to Text Box so that the settings from the table do not somehow clash with any
settings on a form. They should not, but who knows? Next, do you have a
relationship defined between TBL_REF_Salutation and the Contacts tables? If
so, which fields are the linking fields? They should be Salutation in
TBL_REF_Salutation to Salutation in Contacts. I am thinking that you can get
rid of the Salutation ID field. It is not really needed. Just make
Salutation the primary key. Then change your combo box's Row Source to
TBL_REF_Salutation. Change Column Count and Bound Column to 1. Hopefully it
will work.

The Show Only Row Source Values, when Yes, tells it only to display
values that are in the Row Source of the box. So, if you have a Contacts
table with existing data and there is a salutation in it that is not in the
salutations table, say Capt., it will not show Capt. when you view a record
with Capt. It probably would just show a blank.

Clifford Bass