Value not valid for field in Combobox

[b_bds]

Hi

i'm having a problem and I really have no idea how to fix this bug.

I have multiple comboboxes (option 1 to 5). When option is selected, the rowsource of all the other ones is set to a value that I keep in a table (tblfield). Everything works perfectly but sometimes I get the error : "The value you entered isn't valid for this field".

when I enter a value A in the comboboxe, everything works well.
when I enter a value B in the comboboxe, everything works well.
But when I enter the value A and then go to value B the value B gives me the error. It always works when the first data is a text and the next one is a number. I wonder if the type is related to the old value of that comboboxe.

I'm really clueless on this one. Anyone can help me?

thanks

P.S. If you have a hard time understanding this don't hesitate

 

[jal]

If a combobox is a bound one, probably the user can only select one of the preloaded values. Therefore if you get the error when you type B, this suggsts that your cbo no longer has B as one of its values. Is "B" still one of the choices in the dropdown when you get the error? (I should think not).

What code are you using to populate the CBOs?

And what code, if any, is used to repopulate them at some stage?

What data are you populating them with?

 

[b_bds]

Hi

all the cbos are unbound.

to populate the cbos I use a table that contains all the vba code for all the possibilities. When click on cboOption1 the vba looks for the value of cbo option 1 and another cbo and finds in the table (tblfield) the value for the rowsource of the cboOption2, cboOption3, cboOption4 and cboOption5. if no value is available for the cbo.rowsource, it put's visible to false.

after update I refresh the cbos with a requery.

the data is selected with a select querry.

any ideas where it comes from?

Bruno

 

[raskew]

Hi -

Can you post examples?

Bob

 

[b_bds]

Hi

for example you have the tblField
categoryID
DisciplineID
Category
option2Caption
option2rowsource
option3Caption
option3rowsource
etc...

on the onclick event of cboCategory the prog looks to see if their is a value for the caption. if their is the cbo is visible.

hire is the code to see if their is a rowsource for the specific option

Me.lblOption2.Caption = Nz(DLookup( _
                            Expr:="[Option2Caption]", _
                            domain:="tblField", _
                            criteria:="([CAtegoryID] = " & Nz(Me.cboOption1) & " AND [DisciplineID] = " & Me.cboDiscipline & ")"), "")
Me.cboOption2.RowSource = Nz(DLookup( _
                            Expr:="[Option2Rowsource]", _
                            domain:="tblField", _
                            criteria:="([CAtegoryID] = " & Me.cboOption1 & " AND [DisciplineID] = " & Me.cboDiscipline & ")"), "")

then I check for the visible part

If (Me.lblOption2.Caption = "") Then
    Me.cboOption2.Visible = False
Else
    Me.cboOption2.Visible = True
End If

then I look continue for the rest of the cbos
Here is a example of a rowsource

Option3Rowsource
SELECT DISTINCT tblSpecPiping.Diameter FROM tblSpecPiping WHERE (([tblSpecPiping]![Spec]=[Forms]![frmEstimation]![cboOption2]))ORDER BY tblSpecPiping.Diameter ASC;

the things is that the result of the select query can be either a number of a text so how can I make the cbo accept all of them?

thanks
Bruno

 

[b_bds]

Hi

I had the same problem but solved it by disabling the allow filter option of the form.

I used to just right click than select remove filter and then I just disabled the allow filter option.

hope this helps

Bruno

 

[b_bds]

Hi

anyone has any clue?

Thanks

Bruno

 

[Irrelephant]

I know this is an old thread, but I was struggling with the same problem and couldn't find a solution anywhere.

I did, however, manage to find the bug.

Like Bruno, the first time I made a selection in my combo box it worked fine, but on the second it would error with "The value you entered isn't valid for this field".

Ultimately, I discovered that the combo box's format was being defaulted back to the last non-text (or "") format used...

So my fix was to code a code in a format change to "" under the change event if the combo box selection was text.

I'd love to know if this is a known bug or if there's a reason for the format change