Combo box strange ? behavior.

P

polman

I run into the following problem that I like to share with you and even
an reasonable explanation of it.
I have a combobox that its rowsource property is populated with the
following code Me!cboWhat1.RowSource = Me!cboWhere1.Column(3). That is
the 2nd column of another combobox which in fact is an SQL sentence
like: SELECT DISTINCT tblPatientRecord.BirthYear FROM tblPatientRecord;
or SELECT DISTINCT tblPatientRecord.LastName FROM tblPatientRecord; Let
say the first time I choose the second choice (LastName) and then I
choose the first choice (BirthYear). So far so good I' m happy with the
results.
BUT! when my first choice is the BirthYear (that is numeric of course)
and then I try to choose LastName (string) an error is produced that
the value is not accepted. (I think is an 2112 error no valid value for
that type of field).
I noticed that when I first choose numeric values to populate the combo
they align right as they should but then it (the combo) does not accept
string values. In the other case (first populate with stings and then
with numeric) it works fine. No errors. (This time the numeric values
are interpreted as string and align left).
After 6 hours of searching and wondering I found that if I choose in
design view as row source string values then when I return to normal
view I can select the values that populate the combo in any order (like
first numeric and then stings) without errors (numeric is interpreted
as strings but that was ok for me). This solved my problem but I wonder
why this is happening. It seems to me that a hidden (or not ?) property
of the combo is activated depended on the type of values that is first
populate it and this does not change afterwards (in normal view).
Please for your comment and sorry for my lengthy post.
Manolis.
 
D

Damian S

Hi Polman,

What field is your combo box bound to? Sounds to me that you have it bound
to a numeric field, which is why it throws an error when you return a date or
string with the combo box.

Damian.
 
J

Jeff Boyce

If you are truly using "...Column(3)" in your code/source, you are pointing
to the FOURTH column of the underlying SQL statement, not the second.

If the other combobox is based on the example SELECT statements you
provided, NEITHER of those have more than one column, so I don't understand
how you would expect the 2nd (or the 4th) column to be used?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
P

polman

Ok thank you both for your time, and sorry for missleading you.
The combo is unbound (was actually... the reason to bound it is for
solving the problem I had. It does not care to what is bound because I
alter it the first time I use it...) and the rowsourse sentence
(SELECT...) is contained in rows of a column (Jeff you are right 4rth
in my case but this is not the point) of another combo.
The point is that the "data type" accepted by the combo is "declared"
somehow the first time I use the combo and that does not alter if I
alter the rowsourse afterwards. That is if I ask the combo to display
numeric values then I cannot display string values and for that reason
the error is produced.
And this is what I cannot understand... Why if I alter the rowsource of
the combo it does not alter and the data type that can display as I
expect... Is there any property I am not aware of ?
I hope this will clear out my point.
Thank you again.
Manolis.
 
P

polman

Damian the combo is unbound. Sorry I 'm tired and my mind is not very
clear. Please omit the phrase :
was actually...
the reason to bound it is for
solving the problem I had. It does not care to what is bound because I
alter it the first time I use it...
Click to expand...
in my previous post. The error is produced by the combo not to what it
is bound of...

Ï/Ç polman Ýãñáøå:
 
D

Damian S

Hi again.

Are you setting the value of the combo box back to Null before you change
the record source?

Damian.
 
P

polman

Hi Damian,
Yes I do.
I think you can reproduce the "error" if you:
1: Build an unbound combo (without any rowSource)
2: Populate it with numeric values (from a table though rowSource
property)
3: Try to populate it with string values (after set it to null of
course)
P.S. Do steps 2 and 3 in normal view or through code.
Best regards
Manolis

Ï/Ç Damian S Ýãñáøå:
 
Top