Using a single Query as RowSource for many Combos - funny behaviou

[Guest]

Hi all.

I got a set of Combos on my Form, and i'm using a single Query as their
RowSource.
When the user clicks on a Combo, the BeforeUpdate Event is used to
'construct' the required SQL string, which is then 'passed' to the above
mentioned Query with DAO (QueryDefs etc.).

This works fine, i.e. the Combos are a bit faster, and display the correct
set/subset of information.

The problem is, sometimes they act funny.

That is, when the user picks a value from the Combo list, this value is
treated as Numeric (instead of Text), which creates an Error (you can't
assign this value to this field etc.).
The Query has the correct SQL string, and the Combo list is also ok, but the
chosen value is (sometimes, not always!) recognized as Numeric (though it's
Text).

Here's the code i use to assign the SQL that's being constructed when the
BeforeUpdate event of a Combo is fired to the Single Query that's used as the
Combos' RowSouce.
This code resides in the Form where the Combos are:
---------------------------------------------------------------
Dim dbs as DAO.Database
Dim qdf as DAO.QueryDef
Dim strQrySel as String

strQrySel = "Qry_ActiveSelection"
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs(strQrySel)
qdf.SQL = strSQL ' strSQL = the SQL string that's already
constructed
dbs.QueryDefs.Refresh ' Refreshing the QueryDefs Collection
Set qdf = Nothing
Me.Requery
Me.Refresh
-----------------------------------------------------------------
Again, this generates the correct data set for the Combos, the problem is
(sometimes!) the Combos decide that this data is Numeric.

Am i missing something here?
Any suggestion is welcome.

Thanks,
Alex

 

[Rick Brandt]

Hi all.

I got a set of Combos on my Form, and i'm using a single Query as
their RowSource.
When the user clicks on a Combo, the BeforeUpdate Event is used to
'construct' the required SQL string, which is then 'passed' to the
above mentioned Query with DAO (QueryDefs etc.).

This works fine, i.e. the Combos are a bit faster, and display the
correct set/subset of information.

The problem is, sometimes they act funny.

That is, when the user picks a value from the Combo list, this value
is treated as Numeric (instead of Text), which creates an Error (you
can't assign this value to this field etc.).
The Query has the correct SQL string, and the Combo list is also ok,
but the chosen value is (sometimes, not always!) recognized as
Numeric (though it's Text).

Here's the code i use to assign the SQL that's being constructed when
the BeforeUpdate event of a Combo is fired to the Single Query that's
used as the Combos' RowSouce.
This code resides in the Form where the Combos are:
---------------------------------------------------------------
Dim dbs as DAO.Database
Dim qdf as DAO.QueryDef
Dim strQrySel as String

strQrySel = "Qry_ActiveSelection"
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs(strQrySel)
qdf.SQL = strSQL ' strSQL = the SQL string that's
already constructed
dbs.QueryDefs.Refresh ' Refreshing the QueryDefs Collection
Set qdf = Nothing
Me.Requery
Me.Refresh
-----------------------------------------------------------------
Again, this generates the correct data set for the Combos, the
problem is (sometimes!) the Combos decide that this data is Numeric.

Am i missing something here?
Any suggestion is welcome.

Thanks,
Alex

Can you explain why you are using one query if you want them to have different
SQL? I see no possible benefit to that. Especially since the RowSource can use
a SQL string directly. There is no reason to even use a saved query at all
(much less a shared one).

 

[Guest]

Hi Rick, thanks for your reply.

The reason i'm using a Query as a RowSource for the Combos is that somewhere
in Access's Help i read that this makes Combos faster (the advice was
something like "base your combos on an existing Query rather than an SQL
string to make them faster").
This has indeed made the Combos a bit faster (the Table against which the
SQL strings work is quite big).
Thus i opted for this design rather than passing the SQL string to the
Combos' RowSource property.

 

[Rick Brandt]

Hi Rick, thanks for your reply.

The reason i'm using a Query as a RowSource for the Combos is that
somewhere in Access's Help i read that this makes Combos faster (the
advice was something like "base your combos on an existing Query
rather than an SQL string to make them faster").
This has indeed made the Combos a bit faster (the Table against which
the SQL strings work is quite big).
Thus i opted for this design rather than passing the SQL string to the
Combos' RowSource property.

Generally the speed difference is miniscule to non-existent, but if you do see
one then make a separate query for each ComboBox. There is no reason to try to
"share" one and if you are modifying the query then this will be problematic (as
you have seen).

 

[Guest]

Thanks for the suggestion.
So i guess it's back to the old design, passing the SQL strings to the
Combos' RowSource property.
The Combos' behaviour in this issue proved to be a slight disappointment im
afraid.

 

[Guest]

Wasn't your initial concern that you're sometimes getting invalid values, as
opposed to how you define your rowsource? To fix that, you can define the
field in question as an expression that uses the Str function:

NewFieldName: Str(FieldWithValue)

 

[Guest]

Hi Jim, thanks for the suggestion.

How do you suggest that i define the field as an expression, im not sure i
get this.
In the Table Design, it's already Text.
Also, the SQL string is constructed dynamically in VBA based on the values
of the various controls of the Form and the chosen control.

 

[Guest]

In your query definition, wherever that is created, for the field in
question, use
SELECT Str(field) as NewName ...

where 'field' is the field in question and 'NewName' is a new name you give
it.