Help with update table code.

[blackie1982]

Hi all,

I have some code which I want to update a field in a table. But I get the error datatype mismatch and I'm not sure why I get that error. Here is my update code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Select * from faults where printed = 0")

Do While Not rs.EOF
Dim i As Integer
For i = 0 To (rs.Fields.Count - 1)
If rs(printed).Value = "0" Then
rs.Edit
rs(printed).Value = "1"
rs.Update
End If
Next i
rs.MoveNext
Loop

Thank You

 

[reclusivemonkey]

I'm sure someone will correct me here, but if you want the value to be a number, I think you leave the quotes off. Alternative, you should be able to declare a variable for your results and set it to integer, although I think you will need to alter your code in this case.

 

[robthedog]

Is the type mismatch because your'e using Yes/No field?

Anyway, why not try something like the following::

The easy way...


dim sSql as string

sSql="UPDATE Faults SET Faults.printed = 1 WHERE (((Faults.printed)=False));"

DoCmd.RunSQL stSql

If your're not sure of the SQL then use Access to build the query, then switch to SQL view and copy & paste the SQL.