Timing issues with Access & SQL Server again

[Rocky Road]

I'm using Access 2002 as a front end to a SQL Server 2000 database. I Update a status field in a SQL Server table using a parameter based stored proc. Works great. Then in the same sub, I open a popup form to view the record I have just updated.

If I open the Popup Form automatically in code, the status field is not updated. If I open it from a button on the form, it works great.

I have tried every trick I know to force the SQL Server table to be updated before I open the Popup Form, but nothing works. I have tried (1) Doevents, (2) Requerying the Popup Form, (3) Requerying the updated recordset (4) Opening the updated recordset in code.

Any other Suggestions?

[MMcCarthy]

I'm using Access 2002 as a front end to a SQL Server 2000 database. I Update a status field in a SQL Server table using a parameter based stored proc. Works great. Then in the same sub, I open a popup form to view the record I have just updated.

If I open the Popup Form automatically in code, the status field is not updated. If I open it from a button on the form, it works great.

I have tried every trick I know to force the SQL Server table to be updated before I open the Popup Form, but nothing works. I have tried (1) Doevents, (2) Requerying the Popup Form, (3) Requerying the updated recordset (4) Opening the updated recordset in code.

Any other Suggestions?

Don't know the solution offhand Rocky will point some experts in your direction who might.

Mary

[NeoPa]

As a last resort (because you've already considered most answers I could think of - especially the DoEvents one) can you try using the OnTimer routine.
Open the popup form in the Timer event and trigger the event after, say, a quarter of a second.
A bit of a clumsy work-around, but should reproduce the manual method which is working quite closely.
HTH.

[willakawill]

I'm using Access 2002 as a front end to a SQL Server 2000 database. I Update a status field in a SQL Server table using a parameter based stored proc. Works great. Then in the same sub, I open a popup form to view the record I have just updated.

If I open the Popup Form automatically in code, the status field is not updated. If I open it from a button on the form, it works great.

I have tried every trick I know to force the SQL Server table to be updated before I open the Popup Form, but nothing works. I have tried (1) Doevents, (2) Requerying the Popup Form, (3) Requerying the updated recordset (4) Opening the updated recordset in code.

Any other Suggestions?

Is it possible to see the offending snippet of code?

[missinglinq]

I know absolutely nothing about writing to a SQL Server backend, but by your description of the problem, it sounds like it's not writing the update to the backend until the focus moves off of wherever it is to the button to open the popup. You might try in VBA moving the focus elsewhere then opening the form from VBA. Won't take but a second to try!

[Andrew Thackray]

The issue probably is with the type of cursor you opened in the recordset.

I've had this problem myself & found if I replace the recordset.update command with the Recordset.updatebatch coimmand the database is updated immediately. Otherwise the command is not committed unti lyou leave the procedure or close the connection

[willakawill]

The issue probably is with the type of cursor you opened in the recordset.

I've had this problem myself & found if I replace the recordset.update command with the Recordset.updatebatch coimmand the database is updated immediately. Otherwise the command is not committed unti lyou leave the procedure or close the connection

Far too many answers without seeing code.
Don't know if it is dao, ado, odbc, recordset, command object, connection object, server side cursor blah blah blah.
Post the code

[NeoPa]

Long time no see Andrew.
This sounds like the proper answer to me.
You won't need the timer trick if this works.

[Rocky Road]

Far too many answers without seeing code.
Don't know if it is dao, ado, odbc, recordset, command object, connection object, server side cursor blah blah blah.
Post the code

Here's the code. Remarked out lines are some of the code I have tried to use to resolve this issue.

   '*******
   ' StoredProc set PositionStatus = Previous and PositionEndDate = @PositionEndDate in EmployeePosition
   ' WHERE PositionStatus = Current and EmployeeID = @EmployeeID
 
   cnn.BeginTrans
   Set cmd = MakeStoredProc("sp_EmployeePosition_UpdatePositionEndDateNStatus_ForEmployeeID")
   Set prmEmployeeID = cmd.CreateParameter("EmployeeID", adInteger, adParamInput, , lngEmployeeID)
   Set prmPositionEndDate = cmd.CreateParameter("PositionEndDate", adDate, adParamInput, , datTermination)
   Set prmModName = cmd.CreateParameter("ModName", adVarChar, adParamInput, 6, gbStrUserCode)
   cmd.Parameters.Append prmEmployeeID
   cmd.Parameters.Append prmPositionEndDate
   cmd.Parameters.Append prmModName
   cmd.Execute
   cnn.CommitTrans
   Set cmd = Nothing
   DoEvents
 
'   Set qryd1 = gbDbsUser.QueryDefs("EmployeePosition_Update1PositionEndDate2Status_ForEmplID_UpdQry")
'   qryd1.Parameters("prmEmployeeID") = lngEmployeeID
'   qryd1.Parameters("prmPositionEndDate") = datTermination
'   qryd1.Parameters("prmModName") = gbStrUserCode
'   qryd1.Execute
'   DoEvents
'   qryd1.Close: Set qryd1 = Nothing
 
'   Set tdfEmployeePosition = gbDbsUser.TableDefs("EmployeePosition")
'   var = RefreshMyLink(tdfEmployeePosition, tdfEmployeePosition.Connect)
 
'   Set cnn = gbCnnDataDB
'   DoEvents
 
   '-- Open VacantPosition_Popup
   MyW = "EmployeeID = " & lngEmployeeID
   var = OpenMyForm("EmployeePosition_TerminateEmployee_Popup", "Popup", "Employee_Entry", MyW)
 
   '-- Confirm End
   myM = "CURRENT EMPLOYEE POSITIONS HAVE BEEN SET TO PREVIOUS. Open the Employee Position Terminate " _
         & "Popup to set the Position to Vacant. "
   Alert

[willakawill]

Here's the code. Remarked out lines are some of the code I have tried to use to resolve this issue.

As I suspected. There is no recordset object. Now would you please post the code for the declarations of the cnn and cmd objects. Thanks

[Rocky Road]

As I suspected. There is no recordset object. Now would you please post the code for the declarations of the cnn and cmd objects. Thanks

Thanks for your help. I found the problem. There was an error in my OpenMyForm Function.

[MMcCarthy]

Thanks for your help. I found the problem. There was an error in my OpenMyForm Function.

Glad you got it worked out.

Mary