Updating table from code!

[camidon]

I'm trying to update a table with some information from code and I'm having a bit of a problem with it. First, I'll show you my code and then explain my problem:

Private Sub btnApprv3_Click()
Dim db As Database
Dim rs As Recordset
Dim currentuser As String
Dim strSQL As String

Set db = CurrentDb()

currentuser = Forms!frmLogin!txtUserName
Date = Now()
ID = Forms!PurchaseRequisition!PRID

strSQL = strSQL & "SELECT * FROM tblPR WHERE "
strSQL = strSQL & "PRID = " & Forms!PurchaseRequisition!PRID

Set rs = db.OpenRecordset(strSQL)

DeptAppr = rs![ManagerAppr]

If IsNull(DeptAppr) Then
[txtManagerAppr] = currentuser
[txtManagerApprDate] = Date
Me.Requery
MsgBox "Credentials Verified! PR approved!"
Else
MsgBox "This approval has already been marked approved!"
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Sub

The problem that I'm having is that the fields actually get updated, but the table doesn't get updated until I close the form or move to another record on the form. I need to issue a command to input the data into the table. me.requery doesn't seem to have the desired affect.

Thanks,

Chris

 

[vbajock]

Sounds like your having some kind of timing issue on flushing the data to disk. Some networks seem to have this problem, and in the past I've found a good work-around is to use the transaction processing code to force a flush:
Here's the DAO code:

'add this dec
Dim wrkDefault As Workspace

'set this before set of db
Set wrkDefault = DBEngine.Workspaces(0)
Set db = CurrentDb()

'right before the call to requery, use this code to flush everything to disk
wrkDefault.BeginTrans
wrkDefault.Committrans
'do the requery
Me.Requery

 

[camidon]

So this will cause the requery to write the data to the table?

 

[vbajock]

wrkDefault.BeginTrans
wrkDefault.Committrans

flushes all pending data transactions to disk

.requery forces the form to read the data from the cache , which should now be in sync with the server.

This seems to be a problem with DAO on some networks. I don't know if this is the source of your problem, but from my experience I would guess that it is. Essentially, the changed data is residing in your local computer's cache files and has not made it to the server's disk yet, because it is waiting for some kind of signal telling it its time to write to the server disk, and the signal is one hop too late. Your form is displaying the correct changes because it is getting its data locally from the local swap file, but this is not reflected on the server data because your changes are still pending on the local disk. It seems to be specific to some types of local hardware or network software problem. On one network we were essentially able to turn the bug on and off by switching the network back and forth between half and full duplex, on another we could switch it on and off by changing brands of network cards. Since I'm not a network engineer I really don't have any indepth insight into why it happens, we discovered the above work around and that was the end of it as far as we were concerned. I hope it works for you. ADO doesn't seem to have these kinds of problems, which is why I don't use DAO anymore unless I have to.

 

[younguser]

create a update query and view SQL. From there you'll know how to do.