Run-time Error 13 - Type mismatch

[Kimberly]

Here's the code for which I keep getting the above error.
I'm trying to add records to a table using VB in Access.
Any help that you can provide is much appreciated.

Public Sub cmdequipment_Click()

Dim dbMyDB As Database

Set dbMyDB = OpenDatabase
("Palletizer/Palletizing_Cost_Tracking.mdb")

Dim rsMyRS As New Recordset

Set rsMyRS = dbMyDB.OpenRecordSet
("tbl_equipmentparts_list", dbOpenDynaset)

rsMyRS.AddNew
rsMyRS!Date = Me.txtdate.Value
rsMyRS!Item_Descrip = Me.txtitemdesc.Value
rsMyRS!Item_Qty = Me.txtitemqty.Value
rsMyRS!Item_Unit_Cost = Me.txtitemcost.Value
rsMyRS!Payment_Frm_Id = Me.cbopayment.Value
rsMyRS!Purchase_Order_Num = Me.txtpo.Value

rsMyRS.Update

End Sub

 

[Dirk Goldgar]

Here's the code for which I keep getting the above error.
I'm trying to add records to a table using VB in Access.
Any help that you can provide is much appreciated.

Public Sub cmdequipment_Click()

Dim dbMyDB As Database

Set dbMyDB = OpenDatabase
("Palletizer/Palletizing_Cost_Tracking.mdb")

Dim rsMyRS As New Recordset

Set rsMyRS = dbMyDB.OpenRecordSet
("tbl_equipmentparts_list", dbOpenDynaset)

rsMyRS.AddNew
rsMyRS!Date = Me.txtdate.Value
rsMyRS!Item_Descrip = Me.txtitemdesc.Value
rsMyRS!Item_Qty = Me.txtitemqty.Value
rsMyRS!Item_Unit_Cost = Me.txtitemcost.Value
rsMyRS!Payment_Frm_Id = Me.cbopayment.Value
rsMyRS!Purchase_Order_Num = Me.txtpo.Value

rsMyRS.Update

End Sub

First, if you want to use a DAO recordset in Access 2000 or 2002, you
must add the required reference to the Microsoft DAO 3.6 Object Library.
You do this from the VB Editor environment, by clicking Tools ->
References..., locating that reference in the list, and putting a check
mark in the box next to it.

Second, since you now likely have a reference to both DAO *and* ADO (the
ActiveX Data Objects Library) and they both define a Recordset object,
you must tell Access which library you mean when you declare the
recordset. Also, don't use the New keyword in this declaration, since
the recordset will be created for you by the OpenRecordset method.
Instead, write your declaration like this:

Dim rsMyRS As DAO.Recordset

Your code will probably work now, but it may still be "wrong" in one
sense. If "Palletizer/Palletizing_Cost_Tracking.mdb" is the same
database as the one in which the code is running, then you don't need to
use the OpenDatabase method. Instead, you should write this:

Set dbMyDB = CurrentDb

Finally, although the above code should work, it's probably not as
efficient as just executing an append query that you build in code.
Instead of opening a recordset just to add a single record, you could
write something like this:

CurrentDb.Execute _
"INSERT INTO tbl_equipmentparts_list (" & _
"[Date], Item_Descrip, Item_Qty, " & _
"Item_Unit_Cost, Payment_Frm_Id, " & _
"Purchase_Order_Num" & _
") VALUES (" & _
Format(Me.txtDate, "\#mm/dd/yyyy\#") & ", " & _
Chr(34) & Me.txtitemdesc.Value & Chr(34) & ", " & _
Me.txtitemqty.Value & ", " & _
Me.txtitemcost.Value & ", " & _
Me.cbopayment.Value & ", " & _
Chr(34) & Me.txtpo.Value & Chr(34) & ")", _
dbFailOnError

Note that I've assumed in the above that Item_Descrip and
Purchase_Order_Num are text fields, that [Date] is a date field, and
that the rest are numeric or currency fields.