Type Mismatch Error - Please help :(

[systematic]

Hi there,

I have a form that uses ADO to create a new record in a table. I have used the form successfully throughout the development of my database (with Access 2003), but am now having trouble with earlier versions.

I get the following error -

-2147352571 "Type mismatch"

when attempting to write to the database. The error occurs when I am trying to write the value from a control, and the value is 'Nothing' or 'Null'.

Here is my code -

Set rs = New ADODB.Recordset
rs.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= ........../customer.mdb;"
rs.Open "tblCustomers", , adOpenKeyset, adLockOptimistic, adCmdTable

With rs
.AddNew
.Fields("Customer_Firstname") = Me.txtFirstName.Value
.Fields("Customer_Surname") = Me.txtSurname.Value
.Fields("Customer_Type") = Me.cboCusType.Value
.Fields("MDL") = Me.txtMDL.Value
.Fields("MVL") = Me.txtMVL.Value
.Fields("Address") = Me.txtAddress.Value
.Fields("PhoneH") = Me.txtHomePh.Value
.Fields("PhoneW") = Me.txtBusPh.Value
.Fields("Mobile") = Me.txtMobile.Value
.Fields("Fax") = Me.txtFax.Value
.Fields("Email") = Me.txtEmail.Value
.Fields("Suburb") = Me.txtSuburb.Value
.Fields("State") = Me.txtState.Value
.Fields("Postcode") = Me.txtPostcode.Value
.Fields("Contact") = Me.txtContact.Value
CusNo = .Fields("Customer_No")
.Update
End With

rs.Close
Set rs = Nothing

StrSQL = "UPDATE tblEnquiries SET Customer_No =" & Chr(34) & CusNo & Chr(34)
StrSQL = StrSQL & " WHERE ID = " & Forms!frmEnquiry!txtAns.Value & ";"
CurrentDb.Execute StrSQL

I have tried to get around this with the following method...

Dim Suburb As String

Suburb = Me.txtSuburb & ""

.Fields("Suburb") = Suburb

This then generates run-time error 91 (Object variable or with variable not set).

I should mention that both the 'First Name' and 'Surname' textboxes have input masks - but do not see how this could cause my problem.

I am at a complete loss to what is going wrong.

Could anyone help me out please?

Thanks

Robert

EDIT: Just some reading I have come across....apparently the problem may to do with Access 2000/2002 using the ADO as default instead of DAO...I have read that this may cause some problems. I should mention that changing my code to reference DAO is not really an option due to time constraints (I am near the end of this project) - but NEXT TIME!

 

[RuralGuy]

You can not move a Null to a string data type. Try using the Nz() function:
.Fields("Suburb") = Nz(Me.txtSuburb.Value,"")

By the way, the .Value property is the default property of most controls and need not be specified. ie: Fields("Suburb") = Me.txtSuburb

 

[systematic]

Hi Rural Guy,

Thanks for the tip with '.Value'. I'm still pretty new at this game so every little thing to help me tidy my code helps

I tried using Nz - and still had the same problem. Not sure what else to do other than recode using DAO....it may be the only option - but I would not of thought I am the first to have this problem.

Thanks for the help though.

Robert

 

[RuralGuy]

I feel certain thet ADO/DAO is not your problem. I'm sure ADO works just fine in ac2k and ac2k2 though I'm not well versed in ADO since I use DAO myself. Have you looked for bogus MISSING references? <ALT> F11 Tools>References If any are showing as missing then just unckeck them and try to compile again. You may also need to use the Nz() function on any field that may be Null. ac2k3 may have incorporated this feature into the base code to eliminate this common error.

 

[WayneRyan]

Robert,

I would imagine that the definition for tblCustomers has
"Allow zero length value" set to No for the field Suburb.

You're gonna either have to use the Nz function as above
(and supply a non-zero length default) or change the
property in the table's Design View.

Wayne

 

[systematic]

Hi Wayne/RG,

Thanks again for your help.

Unfortunately the problem still lies unresolved!

Zero length values were definately allowed in the table. I have also taken the approach of declaring each item and setting it to -

MyItem = Nz(MyControl, "")

.Fields("MyField") = MyItem

etc...

Still the same problem. Further reading on google has not led me to any other conclusion - other than my initial 'edit' where I read some problems about Access 2k/2k2 - because it uses ADO as it's default instead of DAO.

I have checked the references out (both ADO and DAO referenced), have changed the order and tinkered where possible..but still no solution.

I will post here if I find out what the problem is and find a solution!

Regards

Robert

 

[systematic]

Problem solved!

It was a problem with the ADO/DAO references in Access 2k/2k2.

Instead of using

Dim rs As ADODB.Recordset

I needed to declare them explicitly as -

Dim ADOrs As ADODB.Recordset - or
DIM DAOrs As DAO.Recordset

Just using 'rs' causes some conflict.

More here -

http://support.microsoft.com/kb/181542/en-us

Thanks again for helping out

Robert

 

[RuralGuy]

I would not have believed it but your experience puts the frosting on it. Thanks for posting back with your success Robert. Now *all* of us are a little wiser.

 

[systemx]

Hi,

Just on the note of posting back - I'm sure there are other newbie's like me out there. I also made reference to a Runtime 91 error - and have also resolved that problem.

The error was occurring when I tried to do anything with my textbox controls - either write using SQL, or perform a conditional check.

I have a sub that I call when the form is loaded or reset - which does your standard stuff like hide controls and clear textboxes.

For my textboxes I was using -

Me.MyTextBox.Value = Nothing

I believe this was causing the error.

I have since changed it to -

Me.MyTextBox = ""

And the errors have stopped occuring.

Take care

Robert