Why doesn't this work?

[FISHiEE]

Trying to run the following code to add a record to a table based on data within an unbound form. The code halts at line 5 with the error message "Run time error 13: Type missmatch". I can't work out why but it's probably very simple. I've not done this type of thing before.

Private Sub Command12_Click()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_TempCurrentStockTakeArchive")

With rst
.AddNew
![Account Code] = "Work In Progress"
![Stock Take Date] = Me.txt_StockTakeDate
![Stock Value] = Me.txt_StockTakeDate
.Update
End With
rst.Close

End Sub

 

[allan57]

looks like your trying to add the date into the [Stock Value] field:

![Stock Value] = Me.txt_StockTakeDate

 

[FISHiEE]

Ah yes. though the code doesn't get as far as that yet for me to have picked up that mistake. Thanks for that though.

 

[mresann]

Check the datatype on your underlying table for the column or field [Account Code]. I'm guessing it's a number type, and you are trying to input text into that field.

Also, if there is no data in the text boxes, the program may be trying to add a Null Value into the database. Some datatypes don't accept Nulls.

 

[FISHiEE]

Nope. They are both set as text. thanks for trying to help though.

Set rst = db.OpenRecordset("tbl_TempCurrentStockTakeArchive")

is the line causing the problems.

 

[namliam]

You are probably working in A2K or above

Your code is DAO and your working with ADO....

Check your references search the FAQ if you dont know how...

Regards

 

[mresann]

Only two things possible.

First, you may have a recordset object conflict. Ensure that your references include the DAO Object module, probably version 3.6, although the version number is not important. If it's not present, you may be trying to open an ADO recordset, which has different coding conventions. On the VB Module menu, navigate "Tools", "References", scroll down to "Microsoft DAO 3.6 Object Library", click the checkbox to add it, then click OK.

Now, even if the DAO object is present, you may still have problems since Access may be trying to open the ADO object. Go to the declaration and explicitely declare the object. To do this, go to the line...

Dim rst As Recordset

...and change it to

Dim rst As DAO.Recordset



The only other possibility is that the table name in your code doesn't match the actual table name. Make sure it's an exact match, otherwise you may be accessing an object that doesn't exist.

 

[FISHiEE]

I am working in access 2002 namlian.

Thanks for the advice mresann. I'll try those suggestions and see what happens!

 

[FISHiEE]

"Dim rst As DAO.Recordset" was the sloution.

Works perfectly now mresann.

Thanks for the help. Much appreciated

 

[namliam]

Which is what I said, but i told you to check your references and do some searching to get at the hard of the problem.

This is a working solution, but you still dont know where it came from.

Regards