fieldtype of unbound control

  • Thread starter Thread starter Oliver Goehre
  • Start date Start date
O

Oliver Goehre

On a search form a user can select fields functions and
values which the search must fullfill. The value field is
a dropdown field which is not bound to any control. The
dropdownfield is filled with all unique values from which
the user can select but he can also enter his own data.
The frist field which normally is shown is the record
number and is numeric. if a name field is chosen, the drop
down list is filled with all names, but access will not
allow to enter any characters. It seems as if access has
set the fieldtype of this unbound control. Is there a
possibilty to prevent this or to set the fieldtype?
 
Even unbound combos have a Bound column, i.e. the column that is the Value
of the combo. The data type should normally be the type of the bound column.

It may be possible to set the combo's RowSource so that it considers the
bound column to be Text. e.g. :
SELECT Str(ID) As CompanyID, CompanyName FROM tblCompany;
 
If this is a bug, then I have missed the point of what you are saying. I
think your complaint is that Access recognises the data type of the bound
column in an unbound combo?

Here are the steps to demonstrate how you can enter text into an unbound
combo that has a numeric Bound column:

1. Open Northwind sample database.

2. Create a new form not bound to any table.

3. Place a combo box in the form, and give it these properties:
- Column Count: 3
- Column Widths: 1";1";1"
- Limit To List: No
- RowSource: SELECT Str([EmployeeID]) AS ID, LastName, FirstName FROM
Employees;


Note that EmployeeID is a numeric, field, but the rowsource above informs
Access that it is to be treated as text. You can then type text values into
the unbound combo.

The fact that Access recognises the data type of the Bound Column - even for
unbound combos - is highly desirable behavior in my view.

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

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

Oliver Goehre said:
Thanks for the answer but the answer is not that simple. It looks like a
Click to expand...
real error. In order to repeat the error you need a table with two field: id
(numeric) and name (string). I added two dropdown fields (unbound) to an
unbound form. One field ("idfirst") has a select statement on id as the
rowsource, the other field ("namefirst") has a Select Statement on name as
the rowsource. Now add a button command to the form with the following code:
Private Sub BefehlChange_Click()
Dim strSQL As String
strSQL = IDFirst.RowSource
IDFirst.RowSource = NameFirst.RowSource
NameFirst.RowSource = strSQL
NameFirst.Requery
IDFirst.Requery
End Sub

If you open the form you will not be able to add an alphanumeric data to
Click to expand...
"idfirst", even though the field data is not restricted "to in list". If you
now click on the button to execute the code, idfirst will show alphanumeric
data in the dropdown field, but it will refuse to accept data from the
dropdown list.
Actually it shows, that the first row source determines the allowed field
Click to expand...
input. Maybe Microsoft can add this to the knowledge base, it would have
saved me quit some work. For future versions it would be great, if it werde
possible to set allowed field types for unbound fields or to allow all
alphanumeric and numeric input for such fields.
 
Oliver

I haven't followed the detail of your problem, but maybe this is relevant.

The data type of the bound column is picked-up from the data type of the
underlying database field - as Allen rightly said. But the data type of
every *unbound* column is always TEXT, when returned by the Column(s?)
property. This can lead to unexpected type-conversion problems. For example,
the combo is bound to a data source like SELECT number1, number2, number3
FROM mytable. If number1 is the bound column, it will have type numeric, but
the other two columns will have type TEXT.

Is that relevant?

TC


Oliver Goehre said:
Thanks for the answer but the answer is not that simple. It looks like a
Click to expand...
real error. In order to repeat the error you need a table with two field: id
(numeric) and name (string). I added two dropdown fields (unbound) to an
unbound form. One field ("idfirst") has a select statement on id as the
rowsource, the other field ("namefirst") has a Select Statement on name as
the rowsource. Now add a button command to the form with the following code:
Private Sub BefehlChange_Click()
Dim strSQL As String
strSQL = IDFirst.RowSource
IDFirst.RowSource = NameFirst.RowSource
NameFirst.RowSource = strSQL
NameFirst.Requery
IDFirst.Requery
End Sub

If you open the form you will not be able to add an alphanumeric data to
Click to expand...
"idfirst", even though the field data is not restricted "to in list". If you
now click on the button to execute the code, idfirst will show alphanumeric
data in the dropdown field, but it will refuse to accept data from the
dropdown list.
Actually it shows, that the first row source determines the allowed field
Click to expand...
input. Maybe Microsoft can add this to the knowledge base, it would have
saved me quit some work. For future versions it would be great, if it werde
possible to set allowed field types for unbound fields or to allow all
alphanumeric and numeric input for such fields.
 
TC, if you set the Format property of an unbound control, that's enough to
get Access to recognise the data type.

For example, create a text box named txtA on a form named Form1.
Set its Format property to General Number.
In the Immediate window:
? TypeName(Forms!Form1!txtA.Value)
Double
 
I absolutely agree that it is a feature for Access to
recognize the data type of the Bound Column.

If you change the data source and requery the combo box,
Access does NOT try to recognize the data type but sticks
to the data type it used with the former record source of
the bound column.

-----Original Message-----
If this is a bug, then I have missed the point of what you are saying. I
think your complaint is that Access recognises the data type of the bound
column in an unbound combo?

Here are the steps to demonstrate how you can enter text into an unbound
combo that has a numeric Bound column:

1. Open Northwind sample database.

2. Create a new form not bound to any table.

3. Place a combo box in the form, and give it these properties:
- Column Count: 3
- Column Widths: 1";1";1"
- Limit To List: No
- RowSource: SELECT Str([EmployeeID]) AS ID, LastName, FirstName FROM
Employees;


Note that EmployeeID is a numeric, field, but the rowsource above informs
Access that it is to be treated as text. You can then type text values into
the unbound combo.

The fact that Access recognises the data type of the Bound Column - even for
unbound combos - is highly desirable behavior in my view.

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

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

Oliver Goehre said:
Thanks for the answer but the answer is not that
Click to expand...
simple. It looks like a
real error. In order to repeat the error you need a table with two field: id
(numeric) and name (string). I added two dropdown fields (unbound) to an
unbound form. One field ("idfirst") has a select statement on id as the
rowsource, the other field ("namefirst") has a Select Statement on name as
the rowsource. Now add a button command to the form with the following code:
Private Sub BefehlChange_Click()
Dim strSQL As String
strSQL = IDFirst.RowSource
IDFirst.RowSource = NameFirst.RowSource
NameFirst.RowSource = strSQL
NameFirst.Requery
IDFirst.Requery
End Sub

If you open the form you will not be able to add an
Click to expand...
alphanumeric data to
"idfirst", even though the field data is not
Click to expand...
restricted "to in list". If you
 
Yes, Access seems to interpret the data type of the bound column when the
form loads, and will not interpret until the form is closed and reopened.

Reassigning the data type of a column dynamically can have unpredictable
results. In one case several years ago (A97) we saw Access crashing (illegal
operation, closed by Windows) when the data type of a bound control changed
at runtime, so we are now very careful not do do that.

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

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

Oliver Goehre said:
I absolutely agree that it is a feature for Access to
recognize the data type of the Bound Column.

If you change the data source and requery the combo box,
Access does NOT try to recognize the data type but sticks
to the data type it used with the former record source of
the bound column.

-----Original Message-----
If this is a bug, then I have missed the point of what you are saying. I
think your complaint is that Access recognises the data type of the bound
column in an unbound combo?

Here are the steps to demonstrate how you can enter text into an unbound
combo that has a numeric Bound column:

1. Open Northwind sample database.

2. Create a new form not bound to any table.

3. Place a combo box in the form, and give it these properties:
- Column Count: 3
- Column Widths: 1";1";1"
- Limit To List: No
- RowSource: SELECT Str([EmployeeID]) AS ID, LastName, FirstName FROM
Employees;


Note that EmployeeID is a numeric, field, but the rowsource above informs
Access that it is to be treated as text. You can then type text values into
the unbound combo.

The fact that Access recognises the data type of the Bound Column - even for
unbound combos - is highly desirable behavior in my view.

Oliver Goehre said:
Thanks for the answer but the answer is not that
Click to expand...
simple. It looks like a
real error. In order to repeat the error you need a table with two field: id
(numeric) and name (string). I added two dropdown fields (unbound) to an
unbound form. One field ("idfirst") has a select statement on id as the
rowsource, the other field ("namefirst") has a Select Statement on name as
the rowsource. Now add a button command to the form with the following code:
Private Sub BefehlChange_Click()
Dim strSQL As String
strSQL = IDFirst.RowSource
IDFirst.RowSource = NameFirst.RowSource
NameFirst.RowSource = strSQL
NameFirst.Requery
IDFirst.Requery
End Sub

If you open the form you will not be able to add an
Click to expand...
alphanumeric data to
"idfirst", even though the field data is not
Click to expand...
restricted "to in list". If you
now click on the button to execute the code, idfirst will show alphanumeric
data in the dropdown field, but it will refuse to accept data from the
dropdown list. the allowed field
input. Maybe Microsoft can add this to the knowledge base, it would have
saved me quit some work. For future versions it would be great, if it werde
possible to set allowed field types for unbound fields or to allow all
alphanumeric and numeric input for such fields.
Click to expand...
Click to expand...
 
Ok! That's news to me. Thanks for the info :-)

But what I said still stands for the unbound columns of a list- or
combo-box, no? All those columns are taken as TEXT, when referenced via the
Column(s?) property, regardless of the data types of the SELECT field that
populate those (unbound) columns.

TC
 
Back
Top