Problem w/AutoExpand in Combobox

[DBQueen]

Hi -

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:

Not In List=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
AuotExpand 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

[MGFoster]

DBQueen wrote:

Hi -

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:

Not In List=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
AuotExpand 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....)

On Not In List should be the name of a function or a macro, not "Yes."
Limit To List should be set to "Yes."
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[jimfortune]

DBQueen wrote:

Hi -

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:

Not In List=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
AuotExpand 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

It turns out that the Null value in the combobox causes the problem
with the autofill. It explains why the combobox worked before but
stopped working once the Null value was suddenly in the table. I added
' WHERE tblSamples.SampleID IS NOT NULL' after 'FROM tblSamples' and
the autofill started working again. It fixed the problem for both A97
and A2K (I didn't try later versions).

James A. Fortune