Combo boxes are blank after switching to SOL Server

  • Thread starter Thread starter jerryk
  • Start date Start date
J

jerryk

Hi,

I am migrating an Access Application to an SQL Server. One of the
issues I have run into is that my combo boxes do not work correctly.

The combo box is unbound and uses a query as it's RowSource. The query
(called ddQry) is in the same DB as the form. The query's SQL is :

SELECT ID, NAME from tblCode where codetype='flavor'

The relevant properties on the combo are:
RowSourceType: Table/Query
Row Source: ddQry
ColumnCount: 2
Column Widths: 0";2"

The results of the query are of the form:

1, Orange
2, Mint
3, Vanilla

What happens is when tblCode is linked to a table in an Access
database, the combo box works as expected. However, when I link to a
table in SQL server, all items show up when you drop down the list, but
after you select one the combo is empty. However, if you read the
value of the combo it is properly set. It is just that nothing is
displayed. Also, if you change the column widths to display the first
column the values do show after selection, but as expected just the
number is displayed.

Any thoughts?

Thanks,

Jerry
 
Jerry,

A few things come to mind. Firstly, since "name" is a reserved keyword,
enclose it in square brackets. While you're at it, you may as well do the
same thing for "ID".
SELECT [ID], [NAME] from tblCode where codetype='flavor'

Secondly, since ddQry doesn't join any tables, you may as well put its SQL
statement directly into the combo's RowSource (bypassing the query
altogether).

Thirdly, the combo's BoundColumn should be 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Graham said:
Jerry,

A few things come to mind. Firstly, since "name" is a reserved keyword,
enclose it in square brackets. While you're at it, you may as well do the
same thing for "ID".
SELECT [ID], [NAME] from tblCode where codetype='flavor'

Secondly, since ddQry doesn't join any tables, you may as well put its SQL
statement directly into the combo's RowSource (bypassing the query
altogether).

Thirdly, the combo's BoundColumn should be 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
Click to expand...

Graham,

Thanks for the reply. While these are all interesting points, I am
still trying to figure out why the behavior changed. I have not
changed any of this code and it works fine in when we link to the
access database and not when we link to an SQL database.

A couple of points. I used ID and NAME in the example, the real field
names are not keywords. The reason for the query vs. direct SQL is
because this query (and others against the code table) are used for any
combo box that utilizes a field defined in the code table.

jerry
 
Jerry,

I'm glad you find my points "interesting". What's the BoundColumn?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

jerryk said:
Jerry,

A few things come to mind. Firstly, since "name" is a reserved keyword,
enclose it in square brackets. While you're at it, you may as well do the
same thing for "ID".
SELECT [ID], [NAME] from tblCode where codetype='flavor'

Secondly, since ddQry doesn't join any tables, you may as well put its
SQL
statement directly into the combo's RowSource (bypassing the query
altogether).

Thirdly, the combo's BoundColumn should be 1.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------
Click to expand...

Graham,

Thanks for the reply. While these are all interesting points, I am
still trying to figure out why the behavior changed. I have not
changed any of this code and it works fine in when we link to the
access database and not when we link to an SQL database.

A couple of points. I used ID and NAME in the example, the real field
names are not keywords. The reason for the query vs. direct SQL is
because this query (and others against the code table) are used for any
combo box that utilizes a field defined in the code table.

jerry
Click to expand...
 
Graham said:
Jerry,

I'm glad you find my points "interesting". What's the BoundColumn?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...

Hi Graham,

The BoundColumn is 1. Interestingly when I set Bound column to 0 it
works, and now that I think about it shouldn't BoundColumn be 0 is I
want the value to the be the ID?
 
jerryk said:
The BoundColumn is 1. Interestingly when I set Bound column to 0 it
works, and now that I think about it shouldn't BoundColumn be 0 is I
want the value to the be the ID?
Click to expand...

Ignore the above comment. When the BoundColumn is set to 0 it works
very inconsistently. I set them back to 1, which is what they were
when I started.
 
jerryk wrote:

I got this work.

I had to change the query to a passthrough query. Once I did that
everything works as expected.

Anyone have any thoughts on why?
 
Jerry,

Not 100% sure, but think it's because it was compiled and optimised for
Jet. Either way, I'm glad you got it to work.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Back
Top