Wrong Data Type Error for Selected Value of a ComboBox

[flores]

Error 2113 on a ComboBox - wrong data type for control

I have a form with an unbound combobox that displays a list of projects
(text key field). Based on one of two option buttons selected, I switch the
projects list to a list of internal orders (number key field). The combobox
works fine when the option is to diaplay internal orders list, and it gives
data type mismatch error (#2113) when an item for the project list is
selected.

Is there a way to explicitely set the data type for the combobox? I like to
set it to text type so that it will take the number or string.

 

[Douglas J. Steele]

AFAIK, items in combo boxes and list boxes are text, regardless of what
they're displaying.

What's the code that's raising your error?

 

[flores]

Douglas,

This is the event procedure that switches the list of from Projects (text
key field) or Internal Work Orders (number key field)

Private Sub frmCost_Object_Type_AfterUpdate()


If Initialize Then
Me.frmCost_Object_Type.SetFocus
txtCost_Object.Value = Empty ' intializing here is a problem if the
record is being edited.
End If

Select Case frmCost_Object_Type.Value
'Note: option one is not visible because charges to cost centers currenly
are not used.
Case 1
' ' Receiver CCtr currently is not used--no list is associated at
this point.
' txtCost_Object.RowSource = ""
lblCost_Object.Caption = "Select Cost" & vbCrLf & "Center to charge"

' txtCost_Object.Value = Empty
' txtCost_Object.RowSource = "Select [Old Cost Center], [Stat Order
#],Description FROM [Hospital - Stat Order #] WHERE Description Like ""R&D
non Project related activities"" ORDER BY [Old Cost Center] ASC;"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Old
Cost Center], [Hospital - Stat Order #].[Stat Order #], [Hospital - Stat
Order #].Description " _
& "FROM [Hospital - Stat Order #] " _
& "WHERE ((([Hospital - Stat Order
#].Description) Like ""*non Project related activities*"")) " _
& "ORDER BY [Hospital - Stat Order
#].[Old Cost Center];"
txtCost_Object.ColumnWidths = "0.8 in;0 in; 3 in"
txtCost_Object.BoundColumn = 1 ' null
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 5
txtCost_Object.ColumnCount = 1
txtCost_Object.ListWidth = 3.8 * 1440 'convert to inches.

Case 2
lblCost_Object.Caption = "Select Stat" & vbCrLf & "Order Number"

' txtCost_Object.Value = Empty ' intializing here is a problem if
the record is being edited.
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Stat
Order #], [Hospital - Stat Order #].[Projects / Territories] FROM [Hospital -
Stat Order #];"
txtCost_Object.ColumnWidths = "0.8 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.3 * 1440 'convert to inches.

Case 3
lblCost_Object.Caption = "Select" & vbCrLf & "Project"

' txtCost_Object.Value = Empty
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True ' allow free input, until OnExit
event.
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.

Case Else
End Select

' Me.txtCost_Object = ""

End Sub


When I am testing the combobox selection. The data mismatch error occurs
when I select a Project item on the list. It seems to me that the combobox
is expecting a numeric value, and I think that the Data Type is being
inherited from the Query's first field which is the value that is saved to
the combobox when a listed item is selected. Note that the types are
diffetent for the Projects and Internal Orders.

Is there a way to explicitly set the data type for the combobox? I would
like to avoid chaning table properties because I am not the original designer
(controller originally designed this). Any ideas will be greately
appreciated.

 

[Douglas J. Steele]

Sorry, your explanation doesn't make any sense to me.

What makes you think that "the combobox is expecting a numeric value"?

Can you single-step through your code and determine exactly what line of
code is raising the error?

In answer to your specific question, no, there's no way to explicitly set
the data type for the combobox. That's because a combobox is strictly a
display mechanism.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

This is the event procedure that switches the list of from Projects (text
key field) or Internal Work Orders (number key field)

Private Sub frmCost_Object_Type_AfterUpdate()


If Initialize Then
Me.frmCost_Object_Type.SetFocus
txtCost_Object.Value = Empty ' intializing here is a problem if the
record is being edited.
End If

Select Case frmCost_Object_Type.Value
'Note: option one is not visible because charges to cost centers
currenly
are not used.
Case 1
' ' Receiver CCtr currently is not used--no list is associated at
this point.
' txtCost_Object.RowSource = ""
lblCost_Object.Caption = "Select Cost" & vbCrLf & "Center to
charge"

' txtCost_Object.Value = Empty
' txtCost_Object.RowSource = "Select [Old Cost Center], [Stat
Order
#],Description FROM [Hospital - Stat Order #] WHERE Description Like ""R&D
non Project related activities"" ORDER BY [Old Cost Center] ASC;"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Old
Cost Center], [Hospital - Stat Order #].[Stat Order #], [Hospital - Stat
Order #].Description " _
& "FROM [Hospital - Stat Order #] " _
& "WHERE ((([Hospital - Stat Order
#].Description) Like ""*non Project related activities*"")) " _
& "ORDER BY [Hospital - Stat Order
#].[Old Cost Center];"
txtCost_Object.ColumnWidths = "0.8 in;0 in; 3 in"
txtCost_Object.BoundColumn = 1 ' null
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 5
txtCost_Object.ColumnCount = 1
txtCost_Object.ListWidth = 3.8 * 1440 'convert to inches.

Case 2
lblCost_Object.Caption = "Select Stat" & vbCrLf & "Order Number"

' txtCost_Object.Value = Empty ' intializing here is a problem if
the record is being edited.
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Stat
Order #], [Hospital - Stat Order #].[Projects / Territories] FROM
[Hospital -
Stat Order #];"
txtCost_Object.ColumnWidths = "0.8 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.3 * 1440 'convert to inches.

Case 3
lblCost_Object.Caption = "Select" & vbCrLf & "Project"

' txtCost_Object.Value = Empty
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True ' allow free input, until OnExit
event.
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.

Case Else
End Select

' Me.txtCost_Object = ""

End Sub


When I am testing the combobox selection. The data mismatch error occurs
when I select a Project item on the list. It seems to me that the
combobox
is expecting a numeric value, and I think that the Data Type is being
inherited from the Query's first field which is the value that is saved to
the combobox when a listed item is selected. Note that the types are
diffetent for the Projects and Internal Orders.

Is there a way to ? I would
like to avoid chaning table properties because I am not the original
designer
(controller originally designed this). Any ideas will be greately
appreciated.

AFAIK, items in combo boxes and list boxes are text, regardless of what
they're displaying.

What's the code that's raising your error?

 

[flores]

Dauglas,

The error reads as follows: "The value you entered isn't valid for the
field. For example, you may have entered text in a numberic field or anumber
that is larger than the FieldSize setting permits."

I am sending a revised event procedure that solved the error. The event
procedure below is for an options group. The combobox is setup each time a
new option is selected. The error occurs at the combobox when a value is
selected after the event procedure sets the new SQL for the combobox.

I think that I know what is happening. I was able to get rid of the error
by temporarily assigning to property RowSource of combobox the query that
makes the combo box expect a text. Then the Case does its normal job and
reassigns the correct SQL query. The combobox is variant and somehow expects
the type of values that are found in the column that relates to property
txtCost_Object.BoundColumn = 1.

Look at my SQL string for Case 2, is there a syntax that may be used to
define column one as data type Text? This may solve my work-around.


Private Sub frmCost_Object_Type_AfterUpdate()

If Initialize Then
Me.frmCost_Object_Type.SetFocus
txtCost_Object.Value = Empty ' intializing here is a problem if the
record is being edited.

' just to force the combobox to become Text type.
lblCost_Object.Caption = "Select" & vbCrLf & "Project"
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.
End If

Select Case frmCost_Object_Type.Value
'Note: option one is not visible because charges to cost centers currenly
are not used.
Case 1
' Receiver CCtr currently is not used--no list is associated at
this point.
lblCost_Object.Caption = "Select Cost" & vbCrLf & "Center to charge"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Old
Cost Center], [Hospital - Stat Order #].Description " _
& "FROM [Hospital - Stat Order #] " _
& "WHERE ((([Hospital - Stat Order
#].Description) Like ""*non Project related activities*"")) " _
& "ORDER BY [Hospital - Stat Order
#].[Old Cost Center];"
txtCost_Object.ColumnWidths = "0.8 in;0 in; 3 in"
txtCost_Object.ListWidth = 3.8 * 1440 'convert to inches.

Case 2
lblCost_Object.Caption = "Select Stat" & vbCrLf & "Order Number"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Stat
Order #], [Hospital - Stat Order #].[Description] FROM [Hospital - Stat Order
#];"
txtCost_Object.ColumnWidths = "0.8 in;3 in"
txtCost_Object.ListWidth = 4.3 * 1440 'convert to inches.

Case 3
lblCost_Object.Caption = "Select" & vbCrLf & "Project"
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.
Case Else
End Select
' factored out from each select case
txtCost_Object.ListRows = -8 * (txtCost_Object.ListCount > 8) -
txtCost_Object.ListCount * (txtCost_Object.ListCount <= 8) ' number or row to
display equals ListCount or 8 maximum
txtCost_Object.LimitToList = True ' allow free input, until OnExit
event.
txtCost_Object.ColumnCount = 2
txtCost_Object.BoundColumn = 1
End Sub

Sorry, your explanation doesn't make any sense to me.

What makes you think that "the combobox is expecting a numeric value"?

Can you single-step through your code and determine exactly what line of
code is raising the error?

In answer to your specific question, no, there's no way to explicitly set
the data type for the combobox. That's because a combobox is strictly a
display mechanism.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

This is the event procedure that switches the list of from Projects (text
key field) or Internal Work Orders (number key field)

Private Sub frmCost_Object_Type_AfterUpdate()


If Initialize Then
Me.frmCost_Object_Type.SetFocus
txtCost_Object.Value = Empty ' intializing here is a problem if the
record is being edited.
End If

Select Case frmCost_Object_Type.Value
'Note: option one is not visible because charges to cost centers
currenly
are not used.
Case 1
' ' Receiver CCtr currently is not used--no list is associated at
this point.
' txtCost_Object.RowSource = ""
lblCost_Object.Caption = "Select Cost" & vbCrLf & "Center to
charge"

' txtCost_Object.Value = Empty
' txtCost_Object.RowSource = "Select [Old Cost Center], [Stat
Order
#],Description FROM [Hospital - Stat Order #] WHERE Description Like ""R&D
non Project related activities"" ORDER BY [Old Cost Center] ASC;"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Old
Cost Center], [Hospital - Stat Order #].[Stat Order #], [Hospital - Stat
Order #].Description " _
& "FROM [Hospital - Stat Order #] " _
& "WHERE ((([Hospital - Stat Order
#].Description) Like ""*non Project related activities*"")) " _
& "ORDER BY [Hospital - Stat Order
#].[Old Cost Center];"
txtCost_Object.ColumnWidths = "0.8 in;0 in; 3 in"
txtCost_Object.BoundColumn = 1 ' null
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 5
txtCost_Object.ColumnCount = 1
txtCost_Object.ListWidth = 3.8 * 1440 'convert to inches.

Case 2
lblCost_Object.Caption = "Select Stat" & vbCrLf & "Order Number"

' txtCost_Object.Value = Empty ' intializing here is a problem if
the record is being edited.
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Stat
Order #], [Hospital - Stat Order #].[Projects / Territories] FROM
[Hospital -
Stat Order #];"
txtCost_Object.ColumnWidths = "0.8 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.3 * 1440 'convert to inches.

Case 3
lblCost_Object.Caption = "Select" & vbCrLf & "Project"

' txtCost_Object.Value = Empty
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True ' allow free input, until OnExit
event.
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.

Case Else
End Select

' Me.txtCost_Object = ""

End Sub


When I am testing the combobox selection. The data mismatch error occurs
when I select a Project item on the list. It seems to me that the
combobox
is expecting a numeric value, and I think that the Data Type is being
inherited from the Query's first field which is the value that is saved to
the combobox when a listed item is selected. Note that the types are
diffetent for the Projects and Internal Orders.

Is there a way to ? I would
like to avoid chaning table properties because I am not the original
designer
(controller originally designed this). Any ideas will be greately
appreciated.

AFAIK, items in combo boxes and list boxes are text, regardless of what
they're displaying.

What's the code that's raising your error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Error 2113 on a ComboBox - wrong data type for control

I have a form with an unbound combobox that displays a list of projects
(text key field). Based on one of two option buttons selected, I
switch
the
projects list to a list of internal orders (number key field). The
combobox
works fine when the option is to diaplay internal orders list, and it
gives
data type mismatch error (#2113) when an item for the project list is
selected.

Is there a way to explicitely set the data type for the combobox? I
like
to
set it to text type so that it will take the number or string.

 

[Douglas J. Steele]

The error sounds to me as though your combo box is bound, and the bound
field is incorrect.

Even if it was a numeric field, type coercion should have no problem putting
a numeric field into a text box: it's the other way around that would be a
problem.

I'll repeat: no, there's no way to set data types in a combo box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Dauglas,

The error reads as follows: "The value you entered isn't valid for the
field. For example, you may have entered text in a numberic field or
anumber
that is larger than the FieldSize setting permits."

I am sending a revised event procedure that solved the error. The event
procedure below is for an options group. The combobox is setup each time
a
new option is selected. The error occurs at the combobox when a value is
selected after the event procedure sets the new SQL for the combobox.

I think that I know what is happening. I was able to get rid of the error
by temporarily assigning to property RowSource of combobox the query that
makes the combo box expect a text. Then the Case does its normal job and
reassigns the correct SQL query. The combobox is variant and somehow
expects
the type of values that are found in the column that relates to property
txtCost_Object.BoundColumn = 1.

Look at my SQL string for Case 2, is there a syntax that may be used to
define column one as data type Text? This may solve my work-around.


Private Sub frmCost_Object_Type_AfterUpdate()

If Initialize Then
Me.frmCost_Object_Type.SetFocus
txtCost_Object.Value = Empty ' intializing here is a problem if the
record is being edited.

' just to force the combobox to become Text type.
lblCost_Object.Caption = "Select" & vbCrLf & "Project"
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.
End If

Select Case frmCost_Object_Type.Value
'Note: option one is not visible because charges to cost centers
currenly
are not used.
Case 1
' Receiver CCtr currently is not used--no list is associated at
this point.
lblCost_Object.Caption = "Select Cost" & vbCrLf & "Center to
charge"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Old
Cost Center], [Hospital - Stat Order #].Description " _
& "FROM [Hospital - Stat Order #] " _
& "WHERE ((([Hospital - Stat Order
#].Description) Like ""*non Project related activities*"")) " _
& "ORDER BY [Hospital - Stat Order
#].[Old Cost Center];"
txtCost_Object.ColumnWidths = "0.8 in;0 in; 3 in"
txtCost_Object.ListWidth = 3.8 * 1440 'convert to inches.

Case 2
lblCost_Object.Caption = "Select Stat" & vbCrLf & "Order Number"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order #].[Stat
Order #], [Hospital - Stat Order #].[Description] FROM [Hospital - Stat
Order
#];"
txtCost_Object.ColumnWidths = "0.8 in;3 in"
txtCost_Object.ListWidth = 4.3 * 1440 'convert to inches.

Case 3
lblCost_Object.Caption = "Select" & vbCrLf & "Project"
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.
Case Else
End Select
' factored out from each select case
txtCost_Object.ListRows = -8 * (txtCost_Object.ListCount > 8) -
txtCost_Object.ListCount * (txtCost_Object.ListCount <= 8) ' number or row
to
display equals ListCount or 8 maximum
txtCost_Object.LimitToList = True ' allow free input, until OnExit
event.
txtCost_Object.ColumnCount = 2
txtCost_Object.BoundColumn = 1
End Sub

Sorry, your explanation doesn't make any sense to me.

What makes you think that "the combobox is expecting a numeric value"?

Can you single-step through your code and determine exactly what line of
code is raising the error?

In answer to your specific question, no, there's no way to explicitly set
the data type for the combobox. That's because a combobox is strictly a
display mechanism.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

This is the event procedure that switches the list of from Projects
(text
key field) or Internal Work Orders (number key field)

Private Sub frmCost_Object_Type_AfterUpdate()


If Initialize Then
Me.frmCost_Object_Type.SetFocus
txtCost_Object.Value = Empty ' intializing here is a problem if
the
record is being edited.
End If

Select Case frmCost_Object_Type.Value
'Note: option one is not visible because charges to cost centers
currenly
are not used.
Case 1
' ' Receiver CCtr currently is not used--no list is associated
at
this point.
' txtCost_Object.RowSource = ""
lblCost_Object.Caption = "Select Cost" & vbCrLf & "Center to
charge"

' txtCost_Object.Value = Empty
' txtCost_Object.RowSource = "Select [Old Cost Center], [Stat
Order
#],Description FROM [Hospital - Stat Order #] WHERE Description Like
""R&D
non Project related activities"" ORDER BY [Old Cost Center] ASC;"
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order
#].[Old
Cost Center], [Hospital - Stat Order #].[Stat Order #], [Hospital -
Stat
Order #].Description " _
& "FROM [Hospital - Stat Order #] "
_
& "WHERE ((([Hospital - Stat Order
#].Description) Like ""*non Project related activities*"")) " _
& "ORDER BY [Hospital - Stat Order
#].[Old Cost Center];"
txtCost_Object.ColumnWidths = "0.8 in;0 in; 3 in"
txtCost_Object.BoundColumn = 1 ' null
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 5
txtCost_Object.ColumnCount = 1
txtCost_Object.ListWidth = 3.8 * 1440 'convert to inches.

Case 2
lblCost_Object.Caption = "Select Stat" & vbCrLf & "Order
Number"

' txtCost_Object.Value = Empty ' intializing here is a problem
if
the record is being edited.
txtCost_Object.RowSource = "SELECT [Hospital - Stat Order
#].[Stat
Order #], [Hospital - Stat Order #].[Projects / Territories] FROM
[Hospital -
Stat Order #];"
txtCost_Object.ColumnWidths = "0.8 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.3 * 1440 'convert to inches.

Case 3
lblCost_Object.Caption = "Select" & vbCrLf & "Project"

' txtCost_Object.Value = Empty
txtCost_Object.RowSource = "WBS - R&D Costs Project List"
txtCost_Object.ColumnWidths = "1.1 in;3 in"
txtCost_Object.BoundColumn = 1
txtCost_Object.LimitToList = True ' allow free input, until
OnExit
event.
txtCost_Object.ListRows = 8
txtCost_Object.ColumnCount = 2
txtCost_Object.ListWidth = 4.1 * 1440 'convert to inches.

Case Else
End Select

' Me.txtCost_Object = ""

End Sub


When I am testing the combobox selection. The data mismatch error
occurs
when I select a Project item on the list. It seems to me that the
combobox
is expecting a numeric value, and I think that the Data Type is being
inherited from the Query's first field which is the value that is saved
to
the combobox when a listed item is selected. Note that the types are
diffetent for the Projects and Internal Orders.

Is there a way to ? I would
like to avoid chaning table properties because I am not the original
designer
(controller originally designed this). Any ideas will be greately
appreciated.




:

AFAIK, items in combo boxes and list boxes are text, regardless of
what
they're displaying.

What's the code that's raising your error?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Error 2113 on a ComboBox - wrong data type for control

I have a form with an unbound combobox that displays a list of
projects
(text key field). Based on one of two option buttons selected, I
switch
the
projects list to a list of internal orders (number key field). The
combobox
works fine when the option is to diaplay internal orders list, and
it
gives
data type mismatch error (#2113) when an item for the project list
is
selected.

Is there a way to explicitely set the data type for the combobox? I
like
to
set it to text type so that it will take the number or string.