Auto expand doesn't work on a number field.

[Timothy via AccessMonster.com]

I have a form with 6 combo boxes that have the auto expand set to yes. It
works on 5 of them great, 4 text and 1 date field, however one is a long
integer and it does not work. It is almost as if I am typing in text, even
though it is a number. If I open the the drop down, I can see all the
numbers, but when I start typing, it doesn't match up to any of them. I have
read through the posts on this setting, but nothing so far has helped. I
have a similar form with 8 combo boxes on it with the same problem. 7 work
great, the 8th is a long integer and doesn't work. I am using Access 2003.
Usually these little issues turn out to have a simple solution, but I have
been searching the past two days with no success. Any direction would be
greatly appreciated.

Thanks,

Tim

 

[Allen Browne]

Timothy, tell us more about this combo.

Its Control Source property? What is there?
If it is a field in your table, open the table in Design view, and tell us
the Data Type of the field.

Its Row Source property?
Is this a table? query? SQL statement?
How many records in this table/query?
Paste the query or SQL statement.

The Bound Column property?
The Column Count property?
The Column Widths property?

Just as a wild guess, you could try adding DISTINCT to the RowSource
query/SQL statement.

Otherwise, I'm guessing that the data type doesn't match, or there is
something else (leading space?) messing it up.

Or possibly, the count of records in the RowSource exceeds the limit for
loading into lists.

 

[Timothy via AccessMonster.com]

Allen,

This combo box is unbound. The form feeds paramaters to another form fed by
a parameter query (which everything works fine). The row source is a query
with about 30K distinct listings, about 63K records in all. (all the other
combo boxes have queries based on the same table and work as expected). The
bound column propert says 1, column count 1, column width is blank (but I can
see the data in the combo box when I click the dropdown as well as select it
normally). Here is the SQL of the query. I have a total of 6 fields in the
query, the one for the combo which is the only one that is displayed, and
distinct set. I am using paramater queries based under the other five
columns based on the combo boxes in the same form with the Nz function to
list all/limit choices in the other combo boxes and this combo when something
has been selected in this combo or any other combo. Again that all works
fine, it is just the auto expand on this one combo box. Is it that this is
all to much for the object? I will paste the SQL of the query, but it is
very very ugly with all the criteria added after adding the Nz parameters. I
am not sure if you can get anything out of it, probably just the first few
lines anyway.

SELECT DISTINCT [Data Table].[Outlet #]
FROM [Data Table]
WHERE ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND
(([Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent Name])=
[Forms]![frmDispute]![AgtName]) AND (([Data Table].[Outlet Name])=[Forms]!
[frmDispute]![OutName])) OR ((([Data Table].[Call/Error Date])=[Forms]!
[frmDispute]![ErrDate]) AND (([Data Table].Error)=[Forms]![frmDispute]![Err])
AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND (([Data
Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]!
[frmDispute]![TranNum],""))="")) OR ((([Data Table].[Transaction #])=[Forms]!
[frmDispute]![TranNum]) AND (([Data Table].Error)=[Forms]![frmDispute]![Err])
AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND (([Data
Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]!
[frmDispute]![ErrDate],""))="")) OR ((([Data Table].Error)=[Forms]!
[frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]![frmDispute]!
[AgtName]) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]![OutName])
AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND ((Nz([Forms]![frmDispute]
![ErrDate],""))="")) OR ((([Data Table].[Call/Error Date])=[Forms]!
[frmDispute]![ErrDate]) AND (([Data Table].[Transaction #])=[Forms]!
[frmDispute]![TranNum]) AND (([Data Table].[Agent Name])=[Forms]![frmDispute]!
[AgtName]) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]![OutName])
AND ((Nz([Forms]![frmDispute]![Err],""))="")) OR ((([Data Table].[Call/Error
Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data Table].[Agent Name])=
[Forms]![frmDispute]![AgtName]) AND (([Data Table].[Outlet Name])=[Forms]!
[frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![Err],""))="")) OR ((([Data Table].[Transaction #])
=[Forms]![frmDispute]![TranNum]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]
![OutName]) AND ((Nz([Forms]![frmDispute]![ErrDate],""))="") AND ((Nz([Forms]!
[frmDispute]![Err],""))="")) OR ((([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]
![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND ((Nz([Forms]!
[frmDispute]![ErrDate],""))="") AND ((Nz([Forms]![frmDispute]![Err],""))=""))
OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND ((
[Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Outlet Name])=
[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![AgtName],""))
="")) OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate])
AND (([Data Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].
[Outlet Name])=[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]!
[TranNum],""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR (((
[Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Outlet Name])=
[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![ErrDate],""))
="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR ((([Data Table].
Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Outlet Name])=[Forms]!
[frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND ((Nz([Forms]![frmDispute]!
[AgtName],""))="")) OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]
![ErrDate]) AND (([Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]
) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND ((Nz(
[Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]![frmDispute]![AgtName],
""))="")) OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]
) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND ((Nz(
[Forms]![frmDispute]![TranNum],""))="") AND ((Nz([Forms]![frmDispute]![Err],
""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR ((([Data Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data Table].[Outlet
Name])=[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![ErrDate]
,""))="") AND ((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]!
[frmDispute]![AgtName],""))="")) OR ((([Data Table].[Outlet Name])=[Forms]!
[frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND ((Nz([Forms]![frmDispute]!
[Err],""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR ((([Data
Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data Table].Error)=
[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR
((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND ((
[Data Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent
Name])=[Forms]![frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![TranNum]
,""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data Table]
..[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data Table].Error)=
[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data Table].Error)=
[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND ((Nz([Forms]![frmDispute]!
[OutName],""))="")) OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]
![ErrDate]) AND (([Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]
) AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND ((Nz(
[Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]![frmDispute]![OutName],
""))="")) OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]
) AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND ((Nz(
[Forms]![frmDispute]![TranNum],""))="") AND ((Nz([Forms]![frmDispute]![Err],
""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data Table].[Agent
Name])=[Forms]![frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![ErrDate]
,""))="") AND ((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]!
[frmDispute]![OutName],""))="")) OR ((([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND ((Nz([Forms]![frmDispute]!
[Err],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data
Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data Table].Error)=
[Forms]![frmDispute]![Err]) AND ((Nz([Forms]![frmDispute]![AgtName],""))="")
AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data Table].
[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data Table].Error)=
[Forms]![frmDispute]![Err]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="")
AND ((Nz([Forms]![frmDispute]![AgtName],""))="") AND ((Nz([Forms]![frmDispute]
![OutName],""))="")) OR ((([Data Table].[Transaction #])=[Forms]![frmDispute]!
[TranNum]) AND (([Data Table].Error)=[Forms]![frmDispute]![Err]) AND ((Nz(
[Forms]![frmDispute]![ErrDate],""))="") AND ((Nz([Forms]![frmDispute]!
[AgtName],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR (((
[Data Table].Error)=[Forms]![frmDispute]![Err]) AND ((Nz([Forms]![frmDispute]!
[TranNum],""))="") AND ((Nz([Forms]![frmDispute]![ErrDate],""))="") AND ((Nz(
[Forms]![frmDispute]![AgtName],""))="") AND ((Nz([Forms]![frmDispute]!
[OutName],""))="")) OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]
![ErrDate]) AND (([Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]
) AND ((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]![frmDispute]!
[AgtName],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR (((
[Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND ((Nz(
[Forms]![frmDispute]![TranNum],""))="") AND ((Nz([Forms]![frmDispute]![Err],
""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="") AND ((Nz([Forms]!
[frmDispute]![OutName],""))="")) OR ((([Data Table].[Transaction #])=[Forms]!
[frmDispute]![TranNum]) AND ((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]![frmDispute]!
[AgtName],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR (((Nz
([Forms]![frmDispute]![TranNum],""))="") AND ((Nz([Forms]![frmDispute]!
[ErrDate],""))="") AND ((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz(
[Forms]![frmDispute]![AgtName],""))="") AND ((Nz([Forms]![frmDispute]!
[OutName],""))=""))
ORDER BY [Data Table].[Outlet #];

Timothy, tell us more about this combo.

Its Control Source property? What is there?
If it is a field in your table, open the table in Design view, and tell us
the Data Type of the field.

Its Row Source property?
Is this a table? query? SQL statement?
How many records in this table/query?
Paste the query or SQL statement.

The Bound Column property?
The Column Count property?
The Column Widths property?

Just as a wild guess, you could try adding DISTINCT to the RowSource
query/SQL statement.

Otherwise, I'm guessing that the data type doesn't match, or there is
something else (leading space?) messing it up.

Or possibly, the count of records in the RowSource exceeds the limit for
loading into lists.

I have a form with 6 combo boxes that have the auto expand set to yes. It
works on 5 of them great, 4 text and 1 date field, however one is a long

[quoted text clipped - 10 lines]

been searching the past two days with no success. Any direction would be
greatly appreciated.

 

[Allen Browne]

So the query returns about 30000 records?
What setting do you have under:
Tools | Options | Edit/Find | Don't display lists ...
If the number is less than 30k, it may account for the non-match.

If that is not the issue, what is the data type of the [Outlet #] field in
the [Data Table] table? If Number, try setting the combo's Format property:
General Number
to indicate a data type for the unbound combo.

The WHERE clause is not pretty. I didn't try counting the ANDs but since the
query works that can't be the issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

This combo box is unbound. The form feeds paramaters to another form fed
by
a parameter query (which everything works fine). The row source is a
query
with about 30K distinct listings, about 63K records in all. (all the other
combo boxes have queries based on the same table and work as expected).
The
bound column propert says 1, column count 1, column width is blank (but I
can
see the data in the combo box when I click the dropdown as well as select
it
normally). Here is the SQL of the query. I have a total of 6 fields in
the
query, the one for the combo which is the only one that is displayed, and
distinct set. I am using paramater queries based under the other five
columns based on the combo boxes in the same form with the Nz function to
list all/limit choices in the other combo boxes and this combo when
something
has been selected in this combo or any other combo. Again that all works
fine, it is just the auto expand on this one combo box. Is it that this
is
all to much for the object? I will paste the SQL of the query, but it is
very very ugly with all the criteria added after adding the Nz parameters.
I
am not sure if you can get anything out of it, probably just the first few
lines anyway.

SELECT DISTINCT [Data Table].[Outlet #]
FROM [Data Table]
WHERE ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate])
AND
(([Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]) AND
(([Data
Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent
Name])=
[Forms]![frmDispute]![AgtName]) AND (([Data Table].[Outlet Name])=[Forms]!
[frmDispute]![OutName])) OR ((([Data Table].[Call/Error Date])=[Forms]!
[frmDispute]![ErrDate]) AND (([Data
Table].Error)=[Forms]![frmDispute]![Err])
AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND
(([Data
Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]!
[frmDispute]![TranNum],""))="")) OR ((([Data Table].[Transaction
#])=[Forms]!
[frmDispute]![TranNum]) AND (([Data
Table].Error)=[Forms]![frmDispute]![Err])
AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND
(([Data
Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND ((Nz([Forms]!
[frmDispute]![ErrDate],""))="")) OR ((([Data Table].Error)=[Forms]!
[frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]![frmDispute]!
[AgtName]) AND (([Data Table].[Outlet
Name])=[Forms]![frmDispute]![OutName])
AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]
![ErrDate],""))="")) OR ((([Data Table].[Call/Error Date])=[Forms]!
[frmDispute]![ErrDate]) AND (([Data Table].[Transaction #])=[Forms]!
[frmDispute]![TranNum]) AND (([Data Table].[Agent
Name])=[Forms]![frmDispute]!
[AgtName]) AND (([Data Table].[Outlet
Name])=[Forms]![frmDispute]![OutName])
AND ((Nz([Forms]![frmDispute]![Err],""))="")) OR ((([Data
Table].[Call/Error
Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data Table].[Agent Name])=
[Forms]![frmDispute]![AgtName]) AND (([Data Table].[Outlet Name])=[Forms]!
[frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="")
AND
((Nz([Forms]![frmDispute]![Err],""))="")) OR ((([Data Table].[Transaction
#])
=[Forms]![frmDispute]![TranNum]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND (([Data Table].[Outlet
Name])=[Forms]![frmDispute]
![OutName]) AND ((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz([Forms]!
[frmDispute]![Err],""))="")) OR ((([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND (([Data Table].[Outlet
Name])=[Forms]![frmDispute]
![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]!
[frmDispute]![ErrDate],""))="") AND
((Nz([Forms]![frmDispute]![Err],""))=""))
OR ((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND
((
[Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Outlet
Name])=
[Forms]![frmDispute]![OutName]) AND
((Nz([Forms]![frmDispute]![AgtName],""))
="")) OR ((([Data Table].[Call/Error
Date])=[Forms]![frmDispute]![ErrDate])
AND (([Data Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].
[Outlet Name])=[Forms]![frmDispute]![OutName]) AND
((Nz([Forms]![frmDispute]!
[TranNum],""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR
(((
[Data Table].[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Outlet
Name])=
[Forms]![frmDispute]![OutName]) AND
((Nz([Forms]![frmDispute]![ErrDate],""))
="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR ((([Data Table].
Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Outlet
Name])=[Forms]!
[frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="")
AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz([Forms]![frmDispute]!
[AgtName],""))="")) OR ((([Data Table].[Call/Error
Date])=[Forms]![frmDispute]
![ErrDate]) AND (([Data Table].[Transaction
#])=[Forms]![frmDispute]![TranNum]
) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND
((Nz(
[Forms]![frmDispute]![Err],""))="") AND
((Nz([Forms]![frmDispute]![AgtName],
""))="")) OR ((([Data Table].[Call/Error
Date])=[Forms]![frmDispute]![ErrDate]
) AND (([Data Table].[Outlet Name])=[Forms]![frmDispute]![OutName]) AND
((Nz(
[Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![Err],
""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR ((([Data
Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].[Outlet
Name])=[Forms]![frmDispute]![OutName]) AND
((Nz([Forms]![frmDispute]![ErrDate]
,""))="") AND ((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]!
[frmDispute]![AgtName],""))="")) OR ((([Data Table].[Outlet
Name])=[Forms]!
[frmDispute]![OutName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="")
AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz([Forms]![frmDispute]!
[Err],""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="")) OR
((([Data
Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data
Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=
[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![OutName],""))=""))
OR
((([Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND ((
[Data Table].Error)=[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent
Name])=[Forms]![frmDispute]![AgtName]) AND
((Nz([Forms]![frmDispute]![TranNum]
,""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data
Table]
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=
[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![ErrDate],""))="")
AND
((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data Table].Error)=
[Forms]![frmDispute]![Err]) AND (([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="")
AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz([Forms]![frmDispute]!
[OutName],""))="")) OR ((([Data Table].[Call/Error
Date])=[Forms]![frmDispute]
![ErrDate]) AND (([Data Table].[Transaction
#])=[Forms]![frmDispute]![TranNum]
) AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND
((Nz(
[Forms]![frmDispute]![Err],""))="") AND
((Nz([Forms]![frmDispute]![OutName],
""))="")) OR ((([Data Table].[Call/Error
Date])=[Forms]![frmDispute]![ErrDate]
) AND (([Data Table].[Agent Name])=[Forms]![frmDispute]![AgtName]) AND
((Nz(
[Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![Err],
""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data
Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data Table].[Agent
Name])=[Forms]![frmDispute]![AgtName]) AND
((Nz([Forms]![frmDispute]![ErrDate]
,""))="") AND ((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]!
[frmDispute]![OutName],""))="")) OR ((([Data Table].[Agent Name])=[Forms]!
[frmDispute]![AgtName]) AND ((Nz([Forms]![frmDispute]![TranNum],""))="")
AND
((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz([Forms]![frmDispute]!
[Err],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR
((([Data
Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data
Table].
[Transaction #])=[Forms]![frmDispute]![TranNum]) AND (([Data
Table].Error)=
[Forms]![frmDispute]![Err]) AND
((Nz([Forms]![frmDispute]![AgtName],""))="")
AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR ((([Data Table].
[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND (([Data
Table].Error)=
[Forms]![frmDispute]![Err]) AND
((Nz([Forms]![frmDispute]![TranNum],""))="")
AND ((Nz([Forms]![frmDispute]![AgtName],""))="") AND
((Nz([Forms]![frmDispute]
![OutName],""))="")) OR ((([Data Table].[Transaction
#])=[Forms]![frmDispute]!
[TranNum]) AND (([Data Table].Error)=[Forms]![frmDispute]![Err]) AND ((Nz(
[Forms]![frmDispute]![ErrDate],""))="") AND ((Nz([Forms]![frmDispute]!
[AgtName],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR
(((
[Data Table].Error)=[Forms]![frmDispute]![Err]) AND
((Nz([Forms]![frmDispute]!
[TranNum],""))="") AND ((Nz([Forms]![frmDispute]![ErrDate],""))="") AND
((Nz(
[Forms]![frmDispute]![AgtName],""))="") AND ((Nz([Forms]![frmDispute]!
[OutName],""))="")) OR ((([Data Table].[Call/Error
Date])=[Forms]![frmDispute]
![ErrDate]) AND (([Data Table].[Transaction
#])=[Forms]![frmDispute]![TranNum]
) AND ((Nz([Forms]![frmDispute]![Err],""))="") AND
((Nz([Forms]![frmDispute]!
[AgtName],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR
(((
[Data Table].[Call/Error Date])=[Forms]![frmDispute]![ErrDate]) AND ((Nz(
[Forms]![frmDispute]![TranNum],""))="") AND
((Nz([Forms]![frmDispute]![Err],
""))="") AND ((Nz([Forms]![frmDispute]![AgtName],""))="") AND
((Nz([Forms]!
[frmDispute]![OutName],""))="")) OR ((([Data Table].[Transaction
#])=[Forms]!
[frmDispute]![TranNum]) AND ((Nz([Forms]![frmDispute]![ErrDate],""))="")
AND
((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz([Forms]![frmDispute]!
[AgtName],""))="") AND ((Nz([Forms]![frmDispute]![OutName],""))="")) OR
(((Nz
([Forms]![frmDispute]![TranNum],""))="") AND ((Nz([Forms]![frmDispute]!
[ErrDate],""))="") AND ((Nz([Forms]![frmDispute]![Err],""))="") AND ((Nz(
[Forms]![frmDispute]![AgtName],""))="") AND ((Nz([Forms]![frmDispute]!
[OutName],""))=""))
ORDER BY [Data Table].[Outlet #];

Timothy, tell us more about this combo.

Its Control Source property? What is there?
If it is a field in your table, open the table in Design view, and tell us
the Data Type of the field.

Its Row Source property?
Is this a table? query? SQL statement?
How many records in this table/query?
Paste the query or SQL statement.

The Bound Column property?
The Column Count property?
The Column Widths property?

Just as a wild guess, you could try adding DISTINCT to the RowSource
query/SQL statement.

Otherwise, I'm guessing that the data type doesn't match, or there is
something else (leading space?) messing it up.

Or possibly, the count of records in the RowSource exceeds the limit for
loading into lists.

I have a form with 6 combo boxes that have the auto expand set to yes.
It
works on 5 of them great, 4 text and 1 date field, however one is a long

[quoted text clipped - 10 lines]

been searching the past two days with no success. Any direction would
be
greatly appreciated.

 

[Timothy via AccessMonster.com]

Allen,

The number is 1000 under the don't display lists field. I changed it to
31000 with no difference in the behavior of the combo box. Even with 1000, I
can scroll through all 30K listings in the combo by using the scroll bar, and
select one and it passes it on properly to the next form/query combo. The
data type is currently set to general number ( one of the earlier things I
tried). Indeed the query is not pretty, but everything works on the
form/query and database for that matter with this one issue being the lone
exception. very frustrating......

So the query returns about 30000 records?
What setting do you have under:
Tools | Options | Edit/Find | Don't display lists ...
If the number is less than 30k, it may account for the non-match.

If that is not the issue, what is the data type of the [Outlet #] field in
the [Data Table] table? If Number, try setting the combo's Format property:
General Number
to indicate a data type for the unbound combo.

The WHERE clause is not pretty. I didn't try counting the ANDs but since the
query works that can't be the issue.

Allen,

[quoted text clipped - 266 lines]

 

[Allen Browne]

Might be time to enlist Access's help to understand what's happening.

After selecting a value in the combo, and while the focus is still in the
combo, press Ctrl+G to open the Immediate Window, and ask questions like:
? Forms!Form1!Combo1.Text
? Asc(Forms!Form1!Combo1.Text)
? Forms!Form1!Combo1.Value
? TypeName(Forms!Form1!Combo1)
? Forms!Form1!Combo1.ListCount
etc, so see if you can trace what Access is seeing in the combo.

To test if the WHERE clause has anything to do with it, you might simplify
it down to (say) just one phrase, to see if that makes a difference. If it
does, you could build the SQL statement dynamically for the RowSource, from
only those boxes that have a value. The basic idea for building this kind of
WHERE clause is illustrated here:
http://allenbrowne.com/ser-62.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

The number is 1000 under the don't display lists field. I changed it to
31000 with no difference in the behavior of the combo box. Even with
1000, I
can scroll through all 30K listings in the combo by using the scroll bar,
and
select one and it passes it on properly to the next form/query combo. The
data type is currently set to general number ( one of the earlier things I
tried). Indeed the query is not pretty, but everything works on the
form/query and database for that matter with this one issue being the lone
exception. very frustrating......

So the query returns about 30000 records?
What setting do you have under:
Tools | Options | Edit/Find | Don't display lists ...
If the number is less than 30k, it may account for the non-match.

If that is not the issue, what is the data type of the [Outlet #] field in
the [Data Table] table? If Number, try setting the combo's Format
property:
General Number
to indicate a data type for the unbound combo.

The WHERE clause is not pretty. I didn't try counting the ANDs but since
the
query works that can't be the issue.

Allen,

[quoted text clipped - 266 lines]

be
greatly appreciated.