update recordset using vba

[gammaman]

I am trying to update a recordset using VBA based on the max "process instance" from another table. After the code executes, the field I am updating is still blank.

Set rs = db.OpenRecordset("myTable", dbOpenDynaset)
 
 If Not (rs.BOF And rs.EOF) Then
 rs.MoveFirst
 Do Until rs.EOF = True
   
 emplid = rs![Employee Number]
 lname = rs![Last Name]
 fname = rs![First Name]
  
 srSQl = "select max(process_instance) from member_tbl where emplid = '" & emplid & "' and lname = '" & lname & "' and fname = '" & fname & "'"
  
 'MsgBox srSql

 updateProc = "Update myTable Set process instance = '" & srSQl & "'"

 rs.Edit
 rs![Process Instance] = updateProc
  rs.Update
 rs.MoveNext
 Loop
 
 End If
 rs.Close

 

[pr2-eugin]

Hello gammaman, Hmmm...

• IMO I do not think this value needs to be updated.. Why? because it is going to be staying in the member table so why are you getting this value from there and copying it here? Could this not be simply JOINED when required?

• You are assigning the String value to the Process Instance.. As it is nowhere executed to get the value.. So the code will not work.. You just need to make some minor changes to the code to work.. But I would like to know what is the intent behind this...

 

[MikeLeBen]

I do not think you can use SQL instructions in DAO recordsets, but I might be wrong.

Either way, I'd use the DoCommand.RunSQL method to execute your update query.

Additionally, I'm not even too sure about the syntax of your query, adding a debug.print updateproc before your rs.edit call to verify it wouldn't be a bad idea.

When working with recordsets I always refer to this valuable resource: http://allenbrowne.com/ser-29.html

 

[gammaman]

After the macro finishes executing, I have the VBA macro export the table to excel for reporting purposes. "They" want to have the process instance on the report as a reference point.

 

[MikeLeBen]

excel reports.. mhmmm.
access does reports, but I guess this isn't for you to decide if you're exporting.

Did you try the RunSQL method?

 

[pr2-eugin]

The following statements,

After the macro finishes executing, I have the VBA macro export the table to excel for reporting purposes. "They" want to have the process instance on the report as a reference point.

highly suggests that the Data need not be stored at all.. You only need it to be displayed.. So do not worry about storing the data, you can create the column in the Query in Runtime.. For best result, just create another Query as..

SELECT emplid, fname, lname, Max(process_instance) As MaxOfInst FROM member_tbl 
GROUP BY emplid, fname, lname;

Then JOIN the Report Query with this Newly created Query.. You should be good to go..