Problem with AutoExpand - Still Need Help

[Andi Plotsky]

Hi - I sent this request last week, but didn't get an answer to my problem -
let's try again:

In my A2K database, I have a form with 2 Unbound Comboboxes, both of which
open up forms to a particular record. One of these comboboxes works
perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a
number to lookup by typing it in, it ALWAYS says it's not in the list (Error
2237) - but if I select the item, it works fine. I only have 1 field in this
combobox - the SampleID. It is set up with the following Properties:

LimitToList=YES
Auto Expand=YES
Column Count=1
Bound Column=1
Row Source Type=Table/Query
Row Source=SELECT DISTINCT [tblSamples].[SampleID] FROM tblSamples ORDER BY
[tblSamples].[SampleID];

(I have also tried with multiple fields, but the bound column was always 1).

I have tried the following:
1) removing the combobox and putting on a fresh one, running repair, making
an entire 2nd form from scratch, etc.
2) making the NOT IN LIST property=NO, which will allow me to enter an
item - but if it's not a valid SampleID, I get an error msg when it tries to
open it up (and it doesn't auto expand).
3) Coding "cboSampleRef.Autoexpand=true" in the GotFocus Event.

Eventually the list of SampleID's is going to get rather lengthy, so the
AutoExpand feature is necessary....scrolling wouldn't work.
Is there some unwritten rule that you can only have ONE of this type of
combobox per form? Am I missing some obvious step? (I do these all the
time....)

Someone, PLEEEEEEZ, have a solution ......

Thanks!

Andi Plotsky

--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)
ir******@bellsouth.net

[Ed Robichaud]

Is [SampleID] a text or number field type? If it's a number, have you set a
format and/or input property on the combo-box that matches that set on the
table field?
-Ed

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:vT******************@bignews6.bellsouth.net.. .

Hi - I sent this request last week, but didn't get an answer to my
problem -
let's try again:

In my A2K database, I have a form with 2 Unbound Comboboxes, both of
which
open up forms to a particular record. One of these comboboxes works
perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a
number to lookup by typing it in, it ALWAYS says it's not in the list
(Error
2237) - but if I select the item, it works fine. I only have 1 field in
this
combobox - the SampleID. It is set up with the following Properties:

LimitToList=YES
Auto Expand=YES
Column Count=1
Bound Column=1
Row Source Type=Table/Query
Row Source=SELECT DISTINCT [tblSamples].[SampleID] FROM tblSamples ORDER
BY
[tblSamples].[SampleID];

(I have also tried with multiple fields, but the bound column was always
1).

I have tried the following:
1) removing the combobox and putting on a fresh one, running repair,
making
an entire 2nd form from scratch, etc.
2) making the NOT IN LIST property=NO, which will allow me to enter an
item - but if it's not a valid SampleID, I get an error msg when it tries
to
open it up (and it doesn't auto expand).
3) Coding "cboSampleRef.Autoexpand=true" in the GotFocus Event.

Eventually the list of SampleID's is going to get rather lengthy, so the
AutoExpand feature is necessary....scrolling wouldn't work.
Is there some unwritten rule that you can only have ONE of this type of
combobox per form? Am I missing some obvious step? (I do these all the
time....)

Someone, PLEEEEEEZ, have a solution ......

Thanks!

Andi Plotsky

--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)
ir******@bellsouth.net

[Andi Plotsky]

SampleID was set as a Long Integer. I didn't have anything in the Format
Property; the Decimal Places property was set to Auto. I tried changing it
to General Number and 0, respectively....but it didn't make a difference.

I should note that SampleID is not the Primary Key in the table - will that
matter?

Thanks for the suggestion, anyway....

Andi

"Ed Robichaud" <ed*********@wdn.com> wrote in message
news:fF*****************@monger.newsread.com...

Is [SampleID] a text or number field type? If it's a number, have you set a format and/or input property on the combo-box that matches that set on the
table field?
-Ed

"Andi Plotsky" <ir******@bellsouth.net> wrote in message
news:vT******************@bignews6.bellsouth.net.. .

Hi - I sent this request last week, but didn't get an answer to my
problem -
let's try again:

In my A2K database, I have a form with 2 Unbound Comboboxes, both of
which
open up forms to a particular record. One of these comboboxes works
perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a
number to lookup by typing it in, it ALWAYS says it's not in the list
(Error
2237) - but if I select the item, it works fine. I only have 1 field in
this
combobox - the SampleID. It is set up with the following Properties:

LimitToList=YES
Auto Expand=YES
Column Count=1
Bound Column=1
Row Source Type=Table/Query
Row Source=SELECT DISTINCT [tblSamples].[SampleID] FROM tblSamples ORDER
BY
[tblSamples].[SampleID];

(I have also tried with multiple fields, but the bound column was always
1).

I have tried the following:
1) removing the combobox and putting on a fresh one, running repair,
making
an entire 2nd form from scratch, etc.
2) making the NOT IN LIST property=NO, which will allow me to enter an
item - but if it's not a valid SampleID, I get an error msg when it tries to
open it up (and it doesn't auto expand).
3) Coding "cboSampleRef.Autoexpand=true" in the GotFocus Event.

Eventually the list of SampleID's is going to get rather lengthy, so the
AutoExpand feature is necessary....scrolling wouldn't work.
Is there some unwritten rule that you can only have ONE of this type of
combobox per form? Am I missing some obvious step? (I do these all the
time....)

Someone, PLEEEEEEZ, have a solution ......

Thanks!

Andi Plotsky

--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)
ir******@bellsouth.net

[jimfortune]

Andi Plotsky wrote:

Hi - I sent this request last week, but didn't get an answer to my problem -
let's try again:

In my A2K database, I have a form with 2 Unbound Comboboxes, both of which
open up forms to a particular record. One of these comboboxes works
perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a
number to lookup by typing it in, it ALWAYS says it's not in the list (Error
2237) - but if I select the item, it works fine. I only have 1 field in this
combobox - the SampleID. It is set up with the following Properties:

LimitToList=YES
Auto Expand=YES
Column Count=1
Bound Column=1
Row Source Type=Table/Query
Row Source=SELECT DISTINCT [tblSamples].[SampleID] FROM tblSamples ORDER BY
[tblSamples].[SampleID];

(I have also tried with multiple fields, but the bound column was always 1).

I have tried the following:
1) removing the combobox and putting on a fresh one, running repair, making
an entire 2nd form from scratch, etc.
2) making the NOT IN LIST property=NO, which will allow me to enter an
item - but if it's not a valid SampleID, I get an error msg when it tries to
open it up (and it doesn't auto expand).
3) Coding "cboSampleRef.Autoexpand=true" in the GotFocus Event.

Eventually the list of SampleID's is going to get rather lengthy, so the
AutoExpand feature is necessary....scrolling wouldn't work.
Is there some unwritten rule that you can only have ONE of this type of
combobox per form? Am I missing some obvious step? (I do these all the
time....)

Someone, PLEEEEEEZ, have a solution ......

Thanks!

Andi Plotsky

--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)
ir******@bellsouth.net

Here are a few tests to try:

Does the second combobox work when the first one is removed?

Does a second combobox that is exactly like the first one except for
the name work when both are present?

Can you execute a query that uses an inner join on SampleID from
tblSamples and another table containing SampleID?

James A. Fortune

CNN/Money best places to live:
http://money.cnn.com/best/bplive/top100_1.html

[Andi Plotsky]

In answer to your questions:

Here are a few tests to try:

Does the second combobox work when the first one is removed? NO

Does a second combobox that is exactly like the first one except for
the name work when both are present? YES

Can you execute a query that uses an inner join on SampleID from
tblSamples and another table containing SampleID? YES, the following query works fine -
(SELECT tblSamples.SampleID, tblSamplesMissing.SampleID
FROM tblSamplesMissing INNER JOIN tblSamples ON tblSamplesMissing.SampleID =
tblSamples.SampleID
WITH OWNERACCESS OPTION;)
- what does this tell me?

Andi

<ji********@compumarc.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com... Andi Plotsky wrote:

Hi - I sent this request last week, but didn't get an answer to my problem - let's try again:

In my A2K database, I have a form with 2 Unbound Comboboxes, both of which open up forms to a particular record. One of these comboboxes works
perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a
number to lookup by typing it in, it ALWAYS says it's not in the list (Error 2237) - but if I select the item, it works fine. I only have 1 field in this combobox - the SampleID. It is set up with the following Properties:

LimitToList=YES
Auto Expand=YES
Column Count=1
Bound Column=1
Row Source Type=Table/Query
Row Source=SELECT DISTINCT [tblSamples].[SampleID] FROM tblSamples ORDER BY [tblSamples].[SampleID];

(I have also tried with multiple fields, but the bound column was always 1).
I have tried the following:
1) removing the combobox and putting on a fresh one, running repair, making an entire 2nd form from scratch, etc.
2) making the NOT IN LIST property=NO, which will allow me to enter an
item - but if it's not a valid SampleID, I get an error msg when it tries to open it up (and it doesn't auto expand).
3) Coding "cboSampleRef.Autoexpand=true" in the GotFocus Event.

Eventually the list of SampleID's is going to get rather lengthy, so the
AutoExpand feature is necessary....scrolling wouldn't work.
Is there some unwritten rule that you can only have ONE of this type of
combobox per form? Am I missing some obvious step? (I do these all the
time....)

Someone, PLEEEEEEZ, have a solution ......

Thanks!

Andi Plotsky

--
Andi Plotsky
IRIS, LLC
2859 Galahad Drive
Atlanta, GA 30345

404-321-9459 (office)
404-636-2331 (fax)
ir******@bellsouth.net

Here are a few tests to try:

Does the second combobox work when the first one is removed?

Does a second combobox that is exactly like the first one except for
the name work when both are present?

Can you execute a query that uses an inner join on SampleID from
tblSamples and another table containing SampleID?

James A. Fortune

CNN/Money best places to live:
http://money.cnn.com/best/bplive/top100_1.html

[jimfortune]

Andi Plotsky wrote:

In answer to your questions:

Here are a few tests to try:

Does the second combobox work when the first one is removed? NO

Does a second combobox that is exactly like the first one except for
the name work when both are present? YES

Can you execute a query that uses an inner join on SampleID from
tblSamples and another table containing SampleID? YES, the following

query works fine -
(SELECT tblSamples.SampleID, tblSamplesMissing.SampleID
FROM tblSamplesMissing INNER JOIN tblSamples ON tblSamplesMissing.SampleID =
tblSamples.SampleID
WITH OWNERACCESS OPTION;)
- what does this tell me?

Andi

The first two tests show that an unwritten rule is not causing the
problem and that the unwritten rule doesn't even exist. The inner join
was a way of finding out if the SampleID in tblSamples can be equated
by SQL to the SampleID in the other tables to test for weirdness in
tblSamples. Something weird happening in the data could cause Access
to think that the value is not in the list since the typed value would
not match the weird value but the value in the dropdown would. Make
sure tblSamplesMissing was not created from tblSamples. That would
defeat the purpose of the test for weirdness. The SQL in your
RowSource looks O.K. except that I don't think you need the ORDER BY
with SELECT DISTINCT. Are you using the latest service packs for A2K?
Can the error be duplicated when the database is copied to a different
machine? Does it occur after converting the database to A97 or A2K2 or
A2K3? I can also send you a valid email address if you'd like to send
me a sample with just the essentials required to get the error so that
I can try to duplicate the error.

James A. Fortune

[Andi Plotsky]

Hi, Jim -

I opened it up in another database, on another computer, and in A2K2. The
problem persists. I don't have Access97, so I couldn't do that one. I just
recently upgraded my Access2000 SP for Windows2000 on one of my computers.
Tried opening up the database on the un-upgraded one, but the problem is
there.

I would like to send you a mini-base so you can see if anything pops up that
you notice. There are two files - the form and the back-end data
(DataTablesR.mdb).

I should say that this combobox DID work at one point in time. I have tried
replacing it with another, but to no avail.

Let me know where to send the datafiles to.

Thanks!

Andi Plotsky
ir******@bellsouth.net
404-321-9459

<ji********@compumarc.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Andi Plotsky wrote:

In answer to your questions:

Here are a few tests to try:

Does the second combobox work when the first one is removed? NO

Does a second combobox that is exactly like the first one except for
the name work when both are present? YES

Can you execute a query that uses an inner join on SampleID from
tblSamples and another table containing SampleID? YES, the following

query works fine -
(SELECT tblSamples.SampleID, tblSamplesMissing.SampleID
FROM tblSamplesMissing INNER JOIN tblSamples ON tblSamplesMissing.SampleID = tblSamples.SampleID
WITH OWNERACCESS OPTION;)
- what does this tell me?

Andi

The first two tests show that an unwritten rule is not causing the
problem and that the unwritten rule doesn't even exist. The inner join
was a way of finding out if the SampleID in tblSamples can be equated
by SQL to the SampleID in the other tables to test for weirdness in
tblSamples. Something weird happening in the data could cause Access
to think that the value is not in the list since the typed value would
not match the weird value but the value in the dropdown would. Make
sure tblSamplesMissing was not created from tblSamples. That would
defeat the purpose of the test for weirdness. The SQL in your
RowSource looks O.K. except that I don't think you need the ORDER BY
with SELECT DISTINCT. Are you using the latest service packs for A2K?
Can the error be duplicated when the database is copied to a different
machine? Does it occur after converting the database to A97 or A2K2 or
A2K3? I can also send you a valid email address if you'd like to send
me a sample with just the essentials required to get the error so that
I can try to duplicate the error.

James A. Fortune