Does Access have a mind of its own?

[DFS]

I swear that sometimes things behave differently from one day to the next.
Access 2003.

Example: I have 4 radio buttons that set the rowsource of a combobox
depending on which option you choose. One of the 4 options has a text field
as the first column in the rowsource, and the other 3 are numbers. The
combobox has no formatting set.

Using the text option sometimes returns:

'The value you entered isn't valid for this field. For example, you may
have entered text in a numeric field...blah blah'

So then I choose one of the number options, then go back to the text option
and voila! it's fine.

[Tim Marshall]

DFS wrote:

'The value you entered isn't valid for this field. For example, you may
have entered text in a numeric field...blah blah'

So then I choose one of the number options, then go back to the text option
and voila! it's fine.

I would say it has to do with how the combo box row source is specified,
if not blank, on opening the form and/or with how you construct the SQL
for the row source after your radio button option group is updated.
I've run into this sort of thing in the past and I had to be consistent
in how I defined data types for the SQL statement. Remember that
everything in a rowsource is treated as text anyway and you can coerce a
text "2" into a numeric by using val("2") when you're later using values
from the combo box.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[DFS]

Tim Marshall wrote:

DFS wrote:

'The value you entered isn't valid for this field. For example, you
may have entered text in a numeric field...blah blah'

So then I choose one of the number options, then go back to the text
option and voila! it's fine.

I would say it has to do with how the combo box row source is
specified, if not blank, on opening the form and/or with how you
construct the SQL for the row source after your radio button option
group is updated. I've run into this sort of thing in the past and I
had to be consistent in how I defined data types for the SQL
statement. Remember that everything in a rowsource is treated as
text anyway and you can coerce a text "2" into a numeric by using
val("2") when you're later using values from the combo box.

I'm not sure what you mean by 'everything in a rowsource is treated as
text'. When I pass a number or text field from a combobox (or listbox)
rowsource into a query or VBA code or function it's treated as a number or
text - depending on the underlying datatype.

I hate having to work around Access oddities like I describe. The worst
part is, it worked fine for a year and a half, but I've been doing
development on it and now it sometimes throws that error.

Thanks

[Salad]

DFS wrote:

I swear that sometimes things behave differently from one day to the next.
Access 2003.

Example: I have 4 radio buttons that set the rowsource of a combobox
depending on which option you choose. One of the 4 options has a text field
as the first column in the rowsource, and the other 3 are numbers. The
combobox has no formatting set.

Using the text option sometimes returns:

'The value you entered isn't valid for this field. For example, you may
have entered text in a numeric field...blah blah'

So then I choose one of the number options, then go back to the text option
and voila! it's fine.

If you know the error number you might also want to check out what the
rowsource is...
If err.Number = xxxx then
msgbox me.combo.rowsou rce
endif
and view the SQL for the rowsource. Maybe something is not quite right

[DFS]

Salad wrote:

DFS wrote:

I swear that sometimes things behave differently from one day to the
next. Access 2003.

Example: I have 4 radio buttons that set the rowsource of a combobox
depending on which option you choose. One of the 4 options has a
text field as the first column in the rowsource, and the other 3 are
numbers. The combobox has no formatting set.

Using the text option sometimes returns:

'The value you entered isn't valid for this field. For example, you
may have entered text in a numeric field...blah blah'

So then I choose one of the number options, then go back to the text
option and voila! it's fine.

If you know the error number you might also want to check out what the
rowsource is...
If err.Number = xxxx then
msgbox me.combo.rowsou rce
endif
and view the SQL for the rowsource. Maybe something is not quite
right

It's one of those errors without a system number. It appears like a
validation rule error.

The funny thing is it's basically random. When I open the form, the
combobox is populated with a rowsource where the first column is text.
Trying to choose from that list right after opening the form sometimes
throws the error. So I choose another option that populates the rowsource
so the first column is a Long, then go back to the Text, and it works.

It's frustrating, 'cause the system is going out to 100+ users eventually,
and I can't find the pattern, or the fix, so far.

[Salad]

DFS wrote:

Salad wrote:

DFS wrote:

I swear that sometimes things behave differently from one day to the
next. Access 2003.

Example: I have 4 radio buttons that set the rowsource of a combobox
depending on which option you choose. One of the 4 options has a
text field as the first column in the rowsource, and the other 3 are
numbers. The combobox has no formatting set.

Using the text option sometimes returns:

'The value you entered isn't valid for this field. For example, you
may have entered text in a numeric field...blah blah'

So then I choose one of the number options, then go back to the text
option and voila! it's fine.

If you know the error number you might also want to check out what the
rowsource is...
If err.Number = xxxx then
msgbox me.combo.rowsou rce
endif
and view the SQL for the rowsource. Maybe something is not quite
right

It's one of those errors without a system number. It appears like a
validation rule error.

The funny thing is it's basically random. When I open the form, the
combobox is populated with a rowsource where the first column is text.
Trying to choose from that list right after opening the form sometimes
throws the error. So I choose another option that populates the rowsource
so the first column is a Long, then go back to the Text, and it works.

It's frustrating, 'cause the system is going out to 100+ users eventually,
and I can't find the pattern, or the fix, so far.

If it isn't a "trappable" error use the forms OnError event and see what
DataErr is equal to.

I don't know if the combo is bound. If you start switching columns
around, maybe you aren't setting the real "key" in the list correctly.
IOW, if column 5 is the key, but you maybe moved it so the bound column
is now 4 then I'd expect you to get an error. Check those settings if
it's a bound column.

I think you'll find it if you do a
If DataErr = dataerrnumberyo udetermined then
debug.print Me.combobox.row source
msgbox "Check the debug window"
response = acdataerrcontin ue
endif
you'll find one part of the problem. And DO check the bound column and
the ControlSource.

[jimfortune]

DFS wrote:

I swear that sometimes things behave differently from one day to the next.
Access 2003.

Example: I have 4 radio buttons that set the rowsource of a combobox
depending on which option you choose. One of the 4 options has a text field
as the first column in the rowsource, and the other 3 are numbers. The
combobox has no formatting set.

Using the text option sometimes returns:

'The value you entered isn't valid for this field. For example, you may
have entered text in a numeric field...blah blah'

So then I choose one of the number options, then go back to the text option
and voila! it's fine.

Here's another shot in the dark. Try putting CStr(Nz(FieldNa me,"")) AS
ComboFieldName around first column numeric fields in the SQL for the
rowsource. The type conversion functions often allow Access to
determine the query field's type. The further coercion of everything
to text may prevent the mismatch. You can also try using the
PARAMETERS declaration in SQL to bounce back and forth between text and
numeric types after setting the combobox value to Null to avoid an
error when the type changes.

I hope this helps,
James A. Fortune