Combo Box error 2113 1

[huskerdon]

Hi - I've checked the FAQs, and haven't found the answer for this problem. I've spent HOURS trying to resolve it !

I have a search form, with an option frame with several option boxes representing different fields to use for a search criteria. Once this option frame is updated, the RowSource for the combo box is set accordingly.

The combo box can handle strings, numeric values, or dates, and the SQL source is set on the fly using the proper delimiters according to the data type. This works great, except for one glitch.

The combo box RowSource is always updated correctly when the first criteria is selected. If it is a string, and then the next criteria is a numeric value, the combo box updates properly.

However, if the first criteria selected when the form is opened, is a numeric value, I get a problem when selecting a string after that. I get RTE 2113: "The value you entered isn't valid for this text". Even though the combo box RowSource is correct, and if you look at the list, it shows the correct values. But if you select one of these, you get the error, because it still thinks it's looking for a numerical value ?!?!

I can't even get into design view without deleting the text in the combo box. I have even set the RowSource to "" when switching from one criteria data type to the next. What makes it even stranger is, if I start with a string value, then I can toggle back and forth between numerical, date, and string values as often as I want with NO problems. It only happens when a numerical value is the first criteria selected.

If anyone has suggestions, I would greatly appreciate.

Thanks!

 

[MaxEd]

Did you check your table field type to make sure it matches with what you have bounded in the combo box data to. Say for example in your tables you the field bounded by a lookup text box storing a number but for your lookup you store a text string. I've done more than my fair share of this type of errors.

-Laughter works miracles.

 

[huskerdon]

MaxEd,

The underlying field types are not the problem. Here's an example of the steps, and what works and what doesn't.

1) Select the option box for Voltage (numerical)
2) The RowSource for the combo box is set via code, and it displays the correct choices (numerical)
3) The first numerical value is show in the text portion at the top of the combo box
4) I can select a value from the list, and everything is fine.

5) I then select a different option box for Location (string)
6) The RowSource for the combo box is set via code, and the choices shown in the list are CORRECT
7) The text porton at the top of the combo box is BLANK
8) When I click on one of the string values in the list, I get the RTE 2113, "The value you entered isn't valid for this text".
=========================================

Now if I do the EXACT same thing EXCEPT choose Location first, and then later choose Voltage, everything works with NO ERRORS.

The problem isn't with the field type in the table, but getting the combo box to accept strings after numerical values. The RowSourceType doesn't change - it's always "Table/Query". If the combo box is changing to show the correct values, why does it still not allow one of the "correct" values to be selected?

Thanks for your help

 

[MaxEd]

Try nulling out the other combobox before setting the row source to see if that helps. I'm not sure why you are having such a headache with it.

 

[huskerdon]

MaxEd,

Thanks. I've tried that, setting the combo box value to vbNullString (or 0), before setting the new RowSource.

I decided to stop messing with it. I will just set the default field criteria choice to a string type when the form is first opened. Whenever a string is the first selection, I can toggle back and forth between string, numeric, and dates with no problem at all.

Previously, I was setting the option group default to a hidden option button, so the combo box would not be updated until the user clicked on their first choice. Now I just start out with a string type field. Doesn't make sense, but it's a solution.

 

[TheAceMan1]

How are ya huskerdon . . . .

You need to post the code that sets up the SQL!

See Ya! . . . . . .

 

[huskerdon]

TheAceMan1,

I'm going to leave things the way they are now because it's working, but I am still curious why it was giving me a problem. Setting the SQL doesn't seem to be the problem, because the combo box is DISPLAYING THE CORRECT VALUES in it. This is always the case whether it's showing string, numerical, or date values!

The problem was why it would not let me select a string value FROM the combo box in certain situations (see previous posts).

Anyway, here's the code to build the RowSource:

' fraSearchField is the Option Group frame
' the Option Buttons are named "opt1" thru "opt22"
' the tag property of each Option Button
' contains the actual field name to use
  strFldName = Me.Controls("opt" & fraSearchField.Value).Tag

' the strFieldType and strDelimiter variables are used later
' so this section is not really needed for setting the RowSource
  Select Case fraSearchField.Value
    Case 2, 3, 5, 7, 8, 10 To 13, 18 To 21
      strFieldType = "TypeText"
      strDelimiter = "'"
    Case 1, 4, 9, 14 To 16
      strFieldType = "TypeNumeric"
      strDelimiter = vbNullString
    Case Else       ' for dates
      strFieldType = "TypeDate"
      strDelimiter = "#"
   End Select
  
' I've tried various combinations of these 3 lines
' commented out or not
  Me.cboSearch.RowSource = vbNullString
  Me.cboSearch.Value = vbNullString
 ' Me.cboSearch.Requery
  
  strSearchSQL = "Select Distinct [myqueryname].[" & strFldName & "] & _
from myqueryname" & "order by [myqueryname].[" & strFldName & "];"
  
  Me.cboSearch.RowSource = strSearchSQL

As I said, the combo box does show the correct values everytime. If you're curious, try a test yourself - select a numerical value type from the option group 1st, and it will let you select one of the values from the combo box.

Then select a string value type from the option group next, and it won't let you select any of the string values that populate the combo box.

 

[huskerdon]

Oops -

The last 2 lines should be:

  strSearchSQL = "Select Distinct [myqueryname].[" & strFldName & "] & _
  "from [myqueryname] order by [myqueryname].[" & strFldName & "];"
  
  Me.cboSearch.RowSource = strSearchSQL

 

[TheAceMan1]

huskerdon . . . . .

Any of your string selections from the combo have an apostrophe ( [purple]'[/purple] ) in it?

See Ya! . . . . . .

 

[huskerdon]

TheAceMan1,

Thanks for pointing that out. Yes, there are a few that have single quotes in it, and also a few with double quotes. The double quotes don't cause a problem, but when I just now tested a string with "'" in it, sure enough I got RTE 3075.

However, none of this explains why I can't select ANYTHING from the properly populated combo box at certain times.

Thanks...

 

[TheAceMan1]

OK huskerdon . . . .

Wow! . . . . Single or Double quotes in Field Names is a very [purple]bad Idea[/purple], particularly when concatenating SQL in VBA. [blue]I would take care of this before I did anything else[/blue]. Your certainly prompting future problems here.

Also there are still errors in your SQL. Should be:

[blue]  strSearchSQL = "Select Distinct [myqueryname].[" & strFldName & [purple][b]"] "[/b][/purple] & _
  "from [myqueryname] order by [myqueryname].[" & strFldName & "];"[/blue]

To explain further: Open any Module & hit F1 for help. Select the Answer Wizard Tab and enter [purple]Quotation Marks In Strings[/purple]. Read that and then try an imagine what your single & double quotes can do!

Also to test/check SQL concatenation in VBA (even if it gets thru the complier), always do a Debug.Print & check it in the Immediate Window. This always reveals if the integrity your concatenation is true . . . . .

See Ya! . . . . . .

 

[huskerdon]

OK TheAceMan1,

Your SQL piece is identical to mine (see the correction I made earlier)

What do you do when a text field in the table has a value such as Fred's House ??? The single quotes need to be in the value! I know how to deal with quotes in strings, and I'll take care of that.

Anyway, for the last time, none of this explains why the combo box gives me RTE 2113 periodically - which was my original question. I could remove ALL text values with single or double quotes, and would still have the same issue.

 

[TheAceMan1]

huskerdon . . . .

I've spent all my resources (including those here at work) and can't find this error ( first time I couldn't find an error#). So I can't tell ya, and if I can't find an error, I chanlange anyone else too.

In my previous posts, I just tired to explain possibilities (one I hope you take care of).

One more possibilty (until someone can further define RTE 2113): you can delete and reconstruct the CB. Sometimes controls don't work as they should first time out (no explanination, and never makes sense).

Anyway (for my own curiosity), can you post the exact text for the error.

Also does the code break on a line?
If not, I would set a break point and single step till it comes up.

At this point, sorry I couldn't help ya more. I'll still be looking for that error . . . . .

See Ya! . . . . . .

 

[MaxEd]

Last thing to try it just step through the code and watch how your variables are changing. Sometimes the errors are really tough to narrow down the cause of it unless you do the debugging REALLY slow.

 

[huskerdon]

TheAceMan1,

I wanted to forget about this because I'm running out of time, but curiosity got the better of me!

What's so frustrating is that the error doesn't occur on a line of code. I get an Access-generated message box with this message:
__________________________________________
The value you entered isn't valid for this field.

For example, you may have entered text in a numeric field or a number that is larger than the FieldSize setting permits.
__________________________________________

The msgbox icon is vbInformation, and the button is vbOKOnly.

This appears when I select a value from the combo box, even though the values in the combo box reflect the CORRECT RowSource.

Please keep in mind that this only happens in this specific situation:

FIRST - I select a numeric data type to populate the combo box. Everything works fine. Then...
SECOND - I select a string (text) data type to populate the combo box. The correct values are shown in the combo box, but when I click on one of them, that's when I get the message box.

I can generate the error in code, by trying to set the combo box value, for example:

Me.cboSearch.Value = Me.cboSearch.ItemData(0)

This will generate the RTE 2113, with the Error Description of "The value you entered isn't valid for this field."

That's all I know! I can e-mail you a small sample DB... if that's acceptable on this site. Don't want to cause any more grief!

 

[huskerdon]

Hi everyone,

This combo box issue really has me baffled. I've got a simple zipped 15k mdb sample if someone wants to look at it. Send me your address if you want it (if this is acceptable to do). I've tried to explain the problem as much as I possibly can.

My address is huskerfansatcharter
(substitute you-know-what for at, and add .net to the end)

Thanks,

huskerdon

 

[CajunCenturion]

I've been able to recreate the problem quite easily, and an explanation for what is happening.

The first time that you load the ComboBox, the underlying datatype of the corresponding TextBox is set to the datatype of the returned values. If it is numeric, then from then on, the TextBox portion must always be numeric. The reason that the list still contains the valid entries is because the ItemData array -- which contains all the possible entries -- is by definition, of type Variant, so its accepts everything. If you subsequently try to select a text entry, you are in essence getting a Type Mismatch Error, trying to put a String (from the Variant ItemData array) into a Numeric field (the TextBox portion of the ComboBox).

On the reverse side, if you first load a bunch of strings, then the default datatype for the TextBox portion of the ComboBox is String, and the system has no problem coercing numeric values into their string equivalents, so it does it. One way you can tell this is happening, is that the numeric values is left justified (default alignment for strings) in the TextBox portion once you select a number from the list. Compare that to loading the numbers first, when the display is right-justified, the default for numeric types.

So that explains what is happening. One workaround, but with sort issues is when loading numeric values, but only for the first time, use a Trim function which will force the datatype to string.
If (FirstTime = True) Then
SQlStmt = "SELECT DISTINCT Trim(NumFld) FROM tblName;"
FirstTime = False
Else
SQlStmt = "SELECT DISTINCT NumFld FROM tblName;"
End If

It works, but I don't particular care for that approach. I'll keep playing and see if I can come up with a way to reset the Textbox datatype type.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[huskerdon]

CajunCenturion,

You're absolutely right!! How did you come up with that? I guess you don't think of a combo box actually being 2 parts: the text box and the item data array.

I tested your suggestion on my small sample DB and it now makes sense. I don't have time right now to try it in the actual DB. Will selecting the text value of "50" produce the same results as selecting the numerical value 50 from the list? Do I need to use the val() function then to force the text numbers back to numerical, when setting the recordsource for the accompanying subform that is opened upon the combo box selection?

I'll be gone for awhile, but will check back later. Thanks for your help!

huskerdon

 

[MaxEd]

Man this problem is a first for me. I can't understand why it is not possible to do that.. maybe you can remove the control and readd it dynamically? hehe.. j/k.. I don't think that's possible... other way is to just do a hide and seek with another text box.

-Laughter works miracles.