2003 vs 2007 "The value you entered..."

[MB]

I created a database in 2007. It has a switchboard, a data entry form and
several reports. It works like a charm in 2007, but if someone opens it with
2003, they cannot advance the records in the data entry form and they also
cannot advance the pages in the reports. They get the following message:

“The value you entered isn’t valid for this field.â€

I do have look-up fields in my table:

Field: LeakTypeLU
Display Control: List Box
Row Source Type: Value List
Row Source: Beam Break;Hole in Pipe;Split Pipe;Unsure-Pipe Burst;Leaking
Repair Band;Leaking Clamp;Line Moved;TapLeak;Rotted Bolts;1/2 Copper
Service;Pressure Leak

Field: PipeMaterialLU
Display Control: list Box
Row Source Type: Value List
Row Source: Cast Iron - lined;Cast Iron - unlined;Ductile;PVC;HDPE

Field: PressureZone
Display Control: Combo Box
Row Source Type: Table
Row Source: tblPressureZone
Bound Column: 1
Column Count: 2

I used the lookup wizard (which I’m seeing in the Newsgroups that this is
not particularly wise). For all three fields. I tried have the Pipe
Material and Leak Type look to a table, but then my queries didn’t work:

MaterialScore: IIf([PipeMaterialLU] Like
"Cast*","6",IIf([PipeMaterialLU]="Ductile","4","2"))

LeakTypeScore: IIf([LeakTypeLU] Like "Beam*","4",IIf([LeakTypeLU] Like
"Hole*","2",IIf([LeakTypeLU] Like "Split*","6","1")))

Do you know the reason there are no problems with 2007, but I get that
message with 2003? And, I don’t know how to fix the problem so 2003 works.
(I have no choice but to get 2003 working -- many managers still have 2003).

Thank you.

 

[MB]

Thanks for your reply, Chris. I put the code in and now when the user clicks
on the record navigation to advance to the next record, he gets your message
instead of the "The value you entered...".

The user is not adding or entering anything, merely clicking for the next
record. How do I fix this?

Thank you.
--
MB

This is a very common problem when using lookup fields and why they're
avoided. To test it add this code to the form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2113 Then
Response = acDataErrContinue
MsgBox "Wrong data type. If this is a lookup field " _
& "you're likely typing text in a numerical field."
End If
End Sub


Chris
Microsoft MVP

I created a database in 2007. It has a switchboard, a data entry form and
several reports. It works like a charm in 2007, but if someone opens it with
2003, they cannot advance the records in the data entry form and they also
cannot advance the pages in the reports. They get the following message:

“The value you entered isn’t valid for this field.â€

I do have look-up fields in my table:

Field: LeakTypeLU
Display Control: List Box
Row Source Type: Value List
Row Source: Beam Break;Hole in Pipe;Split Pipe;Unsure-Pipe Burst;Leaking
Repair Band;Leaking Clamp;Line Moved;TapLeak;Rotted Bolts;1/2 Copper
Service;Pressure Leak

Field: PipeMaterialLU
Display Control: list Box
Row Source Type: Value List
Row Source: Cast Iron - lined;Cast Iron - unlined;Ductile;PVC;HDPE

Field: PressureZone
Display Control: Combo Box
Row Source Type: Table
Row Source: tblPressureZone
Bound Column: 1
Column Count: 2

I used the lookup wizard (which I’m seeing in the Newsgroups that this is
not particularly wise). For all three fields. I tried have the Pipe
Material and Leak Type look to a table, but then my queries didn’t work:

MaterialScore: IIf([PipeMaterialLU] Like
"Cast*","6",IIf([PipeMaterialLU]="Ductile","4","2"))

LeakTypeScore: IIf([LeakTypeLU] Like "Beam*","4",IIf([LeakTypeLU] Like
"Hole*","2",IIf([LeakTypeLU] Like "Split*","6","1")))

Do you know the reason there are no problems with 2007, but I get that
message with 2003? And, I don’t know how to fix the problem so 2003 works.
(I have no choice but to get 2003 working -- many managers still have 2003).

 

[MB]

I did that before and then my queries wouldn't work. Is there a reason why
everything works wellin 2007, but not 2003?

My queries create scores for data entered. For example, for pipe material,
if ductile is entered then it is given a 6; if cast iron is entered, it is
given a 4, PVC a 2, all others a 0. When I changed the look-up field in the
table as you recommended, my query gives an answer of 1 for all.