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.

 

[Pat Hartman]

Aside from the type mismatch, your code is not correct. Why are you looping through the fields collection of the record? You are specifically addressing the single field that you are updating so there is no need for the loop. The select statment shouldn't select all the columns. You are only updating one column. Select only the columns you need. And finally, a code loop is far less efficient than an action query. Create an update query using the QBE and just run it in your code. No loop is necessary.

 

[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.