Access ADP - combo box problem

[bradmaunsell]

Microsft Access 2003
SQL Server 2005 Express
Windows XP
Beginner with SQL Server 2005 Express



I have a problem with a combo box. The problem is the cruser will not "jump down" the list while making an entry. The column is properly sorted. The cursor does not jump down until the full number is entered.

Example, supose the left field is numberic column with values 1000 thru 8000.

If I am entering a number of 1570, I expect the curser to jump down each time another number is keyed. That is, after enter "1" the curser jumps to the first occurance of "1". When the "5" is entered, the curser should jump to the first occurance of "15"....etc.

When the full number (1570) is enetered, the curser then jumps to that correct row on the drop down list.

This problem exits only with combo boxes that use a stored procedure as the row source. If a combo box has row source as table, the curser jumps as it should.


'===========================================================

Here is the Access VBA code is am using to set the rowsource for the combo box:

Me.cboSelection.AutoExpand = True
Select Case srt

Case 1 'By AppID
drp = 1
Me.cboSelection.ColumnCount = 7
Me.cboSelection.ColumnWidth = 7.75
Me.cboSelection.ColumnWidths = ".75 in; 2.75 in; 2.75 in; 1.5 in; 0 in; 0 in; 0 in"
Me.cboSelection.BoundColumn = 1
Me.cboSelection.RowSource = "EXEC sp_SELECT_APP_ByAppID " & parWIP_INQ & ", " & parProd & ", " & parEnt1 & ", " & parEnt2

Case 2
drp = 2 'By FileID
Me.cboSelection.ColumnCount = 8
Me.cboSelection.ColumnWidth = 8.55
Me.cboSelection.ColumnWidths = ".75 in; .75 in; 2.75 in; 2.75 in; 1.5 in; 0 in; 0 in; 0 in"
Me.cboSelection.BoundColumn = 1
Me.cboSelection.RowSource = "EXEC sp_SELECT_APP_ByFileID " & parWIP_INQ & ", " & parProd & ", " & parEnt1 & ", " & parEnt2
'===========================================================

Here is my stored procedure in SQL Express:

USE [MGA]
GO
/****** Object: StoredProcedure [dbo].[sp_SELECT_APP_ByAppID] Script Date: 08/31/2007 09:47:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_SELECT_APP_ByAppID]
@AppGroup as int,
@parProd as int,
@parEnt1 as int,
@parEnt2 as int
AS
BEGIN
SELECT TOP (100) PERCENT dbo.tblApps.AppID,
dbo.tblPolicyholders.NamedInsured1, dbo.tblPolicyholders.NamedInsured2,
dbo.tblApps.AppDate_Recd,
dbo.zrefAppStatus.AppGrp, dbo.tblApps.Product, dbo.tblApps.EntityID
FROM dbo.tblApps LEFT OUTER JOIN
dbo.tblPolicyholders ON dbo.tblApps.PolicyHolderID = dbo.tblPolicyholders.PolicyholderID LEFT OUTER JOIN
dbo.zrefAppStatus ON dbo.tblApps.AppStatus = dbo.zrefAppStatus.ID
WHERE (dbo.zrefAppStatus.AppGrp = @AppGroup)
AND (dbo.tblApps.Product = @parProd)
AND (dbo.tblApps.EntityID >= @parEnt1 AND dbo.tblApps.EntityID <= @parEnt2)
ORDER BY dbo.tblApps.AppID

END



'===========================================================

The SQL Express 2005 Primary Key in table tblApps is AppID

Based on what I have read, I wonder if the combo is not recognizing the left column as an indexed column.


Any help will be appreciated - I have spent hours struggling without a solution.

Thanks,
Brad
Burlington, Vermont

 

[Crowley16]

try putting set nocount on just after the begin in your stored procedure...

also check the properties on your combobox, I think there's a behaviour property of some sort...

--------------------
Procrastinate Now!

 

[bradmaunsell]

No luck!!

Actually I tried the NOCOUNT before. I think the Expand property should affect this but it doesn't seem to have any affect. I have been through each of the combo properties HELP and haven't found annything that changes this problem.