Data type Mismatch error! 2

[CHTHOMAS]

Data type Mismatch error!

Hi,

Am getting data type mismatch error when i select a value in a combo box. When a value is selected in combo box it should open another form depending on the value selected in combo box.

The corresponding data type for the value in the table is Text. If the value is like 91000 then the combo box works fine and opens the form. But if the value contains special character, like 91000-10, then am getting the data type mismatch error. Any ideas or suggestions? can send the db if interested (very small in size.)

Warm and best Regards,

Charley

 

[KenReay]

Hi Charley

I am a bit lost, you mention a form which is opened depending on the value selected from the combo, but also a table.

Could you explain a little more?

Regards Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[pgk]

Hi,
Can you just post the code where the form is opened based on the Combo box selection? Hope it helps. Let me know what happens.
With regards,
PGK

 

[RacerGirl117]

Does each value in your combo box open a different form? Check the data types of the fields in the forms and see if they're all the same type and length.

 

[CHTHOMAS]

Ken Reay,

When i select a value in combo box, then it opens a form depending on the value selected in the combo box. The form which the combo box opens, gets the value from a table. To be more specific, the combo box gets the value from a table called "Project". Now the form which the combo box opens is again based on Table "Project".


PGK,

Am using a macro to open the form. please find the converted macro as below.

If ((Forms!MAIN!Combo1 <> 1 And Forms!MAIN!Combo1 <> 2 And Forms!MAIN!Combo1 <> 3) And (Left(Forms!MAIN!Combo1, 1) <> 8)) Then
' if the project value is 1 or 2 or 3 or a project
'number starting with 8, then open Project1 form
DoCmd.OpenForm &quot;PROJECT1&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]=[Forms]![MAIN]![Combo1]&quot;, , acNormal
Exit Function
End If
If ((Forms!MAIN!Combo1 = 1 Or Forms!MAIN!Combo1 = 2 Or Forms!MAIN!Combo1 = 3) Or (Left(Forms!MAIN!Combo1, 1) = 8)) Then
DoCmd.OpenForm &quot;Project2&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]=[Forms]![MAIN]![Combo1]&quot;, , acNormal
Exit Function
End If


RacerGirl117,

I checked the data types of the fields in the forms and they're all the same type

 

[pgk]

Hi,
Try using the Val function to get the numeric value of the selected Combo box text.

Eg.

If ((Val(Forms!MAIN!Combo1) <> 1 And Val(Forms!MAIN!Combo1) <> 2 And Val(Forms!MAIN!Combo1) <> 3) And (Val(Left(Forms!MAIN!Combo1, 1) <> 8))) Then
' if the project value is 1 or 2 or 3 or a project
'number starting with 8, then open Project1 form
DoCmd.OpenForm &quot;PROJECT1&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]=[Forms]![MAIN]![Combo1]&quot;, , acNormal
Exit Function
End If
If ((Val(Forms!MAIN!Combo1) = 1 Or Val(Forms!MAIN!Combo1) = 2 Or Val(Forms!MAIN!Combo1) = 3) Or (Val(Left(Forms!MAIN!Combo1, 1)) = 8)) Then
DoCmd.OpenForm &quot;Project2&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]=[Forms]![MAIN]![Combo1]&quot;, , acNormal
Exit Function
End If
Hope it helps. Let me know what happens.
With regards,
PGK

 

[CHTHOMAS]

pgk,

I tried using the Val function, but am getting the same error message.

 

[PaulBricker]

You say the field in the Table is Text but the syntax in this line is for Numeric.
DoCmd.OpenForm &quot;PROJECT1&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]=[Forms]![MAIN]![Combo1]&quot;, , acNormal

Try this.

DoCmd.OpenForm &quot;PROJECT1&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]= '&quot; & [Forms]![MAIN]![Combo1]& &quot;'&quot;, , acNormal

Note the extra punctuation around the Form identifier.

HTH
Paul

 

[KenReay]

Hi

In your initail post you say the field in the table is defined as text, but you are refering to it in you code as if it is a number, so therefore a value like 91000-10 will give you a datatype mismatch error.

I would say you nedd to treat all reference to the value in the combo bax as strings, so you need to bound them in quotes.

If ((Forms!MAIN!Combo1 <> &quot;1&quot; And Forms!MAIN!Combo1 <> &quot;2&quot; And Forms!MAIN!Combo1 <> &quot;3&quot And (Left(Forms!MAIN!Combo1, 1) <> &quot;8&quot) Then
' if the project value is 1 or 2 or 3 or a project
'number starting with 8, then open Project1 form
DoCmd.OpenForm &quot;PROJECT1&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]='&quot; & [Forms]![MAIN]![Combo1] & &quot;'&quot;, , acNormal
Exit Function
End If
If ((Forms!MAIN!Combo1 = &quot;1&quot; Or Forms!MAIN!Combo1 = &quot;2&quot; Or Forms!MAIN!Combo1 = &quot;3&quot Or (Left(Forms!MAIN!Combo1, 1) = &quot;8&quot) Then
DoCmd.OpenForm &quot;Project2&quot;, acNormal, &quot;&quot;, &quot;[PROJECT]![PROJECTNUM]='&quot; & [Forms]![MAIN]![Combo1] & &quot;'&quot;, , acNormal
Exit Function
End If

Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[CHTHOMAS]

That's it. The quotation mark solves the problem. Thanks a lot to PaulBricker, KenReay and all others who spared their valuble time helping me to sort out the problem.

Regards,

Charley