Need Help with Update Query in Loop Statement

[alloyd1]

I am using Imacros, but I want to flag update each record with a date in the field [post] as it loops through the recordset. Here is my code, but the update part is not working at all. Can someone please let me know what I am doing wrong?

Option Compare Database
Public Function PostCL()
On Error GoTo Err_PostCL_Click
Dim rs, iim1, sql, tempvalueid
Dim myname, mypath, connstring
Dim iret
Set rs = CreateObject("ADODB.Connection")
connstring = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\cdata\clist_data.mdb"
rs.Open (connstring)
sql = "select * from tbl_READY_approved_agents_post_Clist WHERE Posted Is Null"
Set rs = rs.Execute(sql)
Set iim1 = CreateObject("Imacros")
iret = iim1.iimInit
iret = iim1.iimDisplay("Posting")
Do Until rs.EOF
tempvalueid = rs!ListingID
DoCmd.RunSQL "UPDATE tbl_READY_approved_agents_post_Clist SET Posted = 'Date()' WHERE listingid = tempvalueid"
'Set the variable
    iret = iim1.iimSet("-var_UserName", rs.Fields(36))
    iret = iim1.iimSet("-var_Password", rs.Fields(37))
'Play the Macro
   'iret = iim1.iimPlay("__PostCL")
   If iret < 0 Then
    MsgBox iim1.iimGetLastError()
 
   End If
  rs.MoveNext
  MsgBox tempvalueid
Loop
'iret = iim1.iimDisplay("Done!")
'iret = iim1.iimExit
'WScript.Quit (0)
 
Exit_PostCL_Click:
    Exit Function
Err_PostCL_Click:
    MsgBox Error
    Resume Exit_PostCL_Click
End Function

 

[pbaldy]

Try this:

CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET Posted = #" & Date() & "# WHERE listingid = " & tempvalueid

 

[alloyd1]

Paul,
Thank you for your help. I had to make a minor change but it worked like a champ. You put me in the right direction.

Ultimately I am going to move the table to an SQL server database, and the logic I am attempting is to have multiple machines reading from the data source in differnt locations. As a computer grabs a record looping through the data I want to write a date to the record and have the other machines that are accessing the same database look to make sure there is no date and grab the next record. I have not tested my theory yet, but I am hoping this will work. Do I need to do anything special that I might be missing before continuing forward?

CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET Posted = #" & Date & "# WHERE ListingID = '" & tempvalueid & "'"

 

[pbaldy]

Sure, sounds like that would work. If other users are grabbing a recordset to begin with, you'd need to make sure to requery the data to see that this record now had a date.

 

[alloyd1]

I determined I would just delete the record after it is done with it. Of course I will have it append to a differnt table before deleting, but testing my code, I now get a type mismatch and for the life of me I can not figure it out. The data type is text for the field [ListingId].

Any help would be appreciated. Here is the code below:

''''''''''''''''''''Note the UPDATE QUERIES work great.
CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET Posted = #" & Date & "# WHERE ListingID = '" & tempvalueid & "'"
CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET PostedUser = '" & tempvaluePostedby & "' WHERE ListingID = '" & tempvalueid & "'"
CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET Machine = '" & tempvalueMachine & "' WHERE ListingID = '" & tempvalueid & "'"
'''''''''''''''''''''Everything work great until I run the below and I get "type mismatch" (but still deletes even after the error comes up)
CurrentDb.Execute "DELETE tbl_READY_approved_agents_post_Clist.* FROM tbl_READY_approved_agents_post_Clist WHERE ListingID = '" & tempvalueid & "'"

 

[JPaulo]

ListingID = '" & tempvalueid & "'" 'text
ListingID = " & tempvalueid & "" 'number

 

[alloyd1]

Jpaulo,
It is a text field and it has mixed data in the field. Some are straight up numbers such as 0912423 and then others are like U10231 or T09212312.

I currently have it set with single quotes ' and it dont work. It works for the UPDATE queries with out a type mismatch error but not at all for the DELETE query.

If I remark out the DELETE query line and run it, all records update successfully. Using the same crietia for selection of the record, I get a type mismatch. Is there something else I am missing?

 

[JPaulo]

the field Posted is format Date/Time or text ?

 

[alloyd1]

That field is formated as date.

 

[JPaulo]

test please:

On Error Resume Next
CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET Posted = Date() WHERE ListingID = '" & tempvalueid & "'"
CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET PostedUser = '" & tempvaluePostedby & "' WHERE ListingID = '" & tempvalueid & "'"
CurrentDb.Execute "UPDATE tbl_READY_approved_agents_post_Clist SET Machine = '" & tempvalueMachine & "' WHERE ListingID = '" & tempvalueid & "'"

 

[alloyd1]

Thank did work. It deleted each record. Is there an error with the code or we just tricking the system to move on regardless?

 

[JPaulo]

this correction was:

SET Posted = Date()

but use the same On Error Resume Next