ADO Update fails after Form Refresh

[EddieN1]

I'm running Access 2007 and SQL Server 2008 R2. I have a simple, datasheet view form in which I want to populate a number of fields based on the entry of one field. In the AfterChange event, I do a Me.Refresh, then I call an ADO subroutine. The subroutine first gets the values to use to populate the other fields, then gets the row to be updated. I use a simple "SELECT * FROM tbl_Data WHERE DetialID = intDetailID". (The calling function passes the detail ID to the subroutine.) Then I update the row as desired. This works perfectly if the row was previously created. However, if I'm inserting a new row, the SELECT statement returns with rsData.EOF as true. If I put in a delay of 4-5 seconds, it usually finds and updates the row. It's like control continues after the Refresh without waiting for the Refresh to finish. I always thought Access was synchronous like this, but I'm beginning to wonder. Any suggestions as to how I can ensure the Refresh has finished before going on? Thanks, Eddie

[Toyman]

I'm running Access 2007 and SQL Server 2008 R2. I have a simple, datasheet view form in which I want to populate a number of fields based on the entry of one field. In the AfterChange event, I do a Me.Refresh, then I call an ADO subroutine. The subroutine first gets the values to use to populate the other fields, then gets the row to be updated. I use a simple "SELECT * FROM tbl_Data WHERE DetialID = intDetailID". (The calling function passes the detail ID to the subroutine.) Then I update the row as desired. This works perfectly if the row was previously created. However, if I'm inserting a new row, the SELECT statement returns with rsData.EOF as true. If I put in a delay of 4-5 seconds, it usually finds and updates the row. It's like control continues after the Refresh without waiting for the Refresh to finish. I always thought Access was synchronous like this, but I'm beginning to wonder. Any suggestions as to how I can ensure the Refresh has finished before going on? Thanks, Eddie

Do you mean "After Update" or "On Change". The code should be fired in the "After Update" Event.

You might post your code here so we can take a look

[EddieN1]

You're right... I meant AfterUpdate...

Here's the code I'm using...

Private Sub SubclassDescription_AfterUpdate()
If IsNull(Me.SubclassDescription) Then
Exit Sub
End If

Me.Refresh

Call UpdateSubclassDescriptionFields(Me.Assortment_ID, SubclassDescription)

Me.Recalc

End Sub

In the sub routine I have the following code snippet...

Public Sub UpdateSubclassDescriptionFields(intAssortmentID As Long, strSubclassDescription As String)

Dim WaitCount As Integer
WaitCount = 0

Debug.Print intAssortmentID

Dim Cnxn As ADODB.Connection
Dim rsDetail As ADODB.Recordset

Set Cnxn = OpenADODetailsConnection
Set rsDetail = New ADODB.Recordset

Dim strSQL As String
strSQL = "SELECT * FROM Tbl_Boom_Detail "
strSQL = strSQL & "WHERE [Assortment ID] = " & intAssortmentID

Debug.Print "open " & Now()
ReadDetail:
rsDetail.Open strSQL, Cnxn, adOpenDynamic, adLockPessimistic


If rsDetail.EOF Then
Debug.Print "eof1"
If WaitCount < 5 Then
WaitCount = WaitCount + 1
Call Wait(1)
Debug.Print "wait " & WaitCount
rsDetail.Close
GoTo ReadDetail
Else
Debug.Print "eof2"
GoTo CloseRecordSet
End If
End If

....

End Sub

The Wait() subroutine is a loop until the second in Now() function changes. Obviously, putting the CPU at 100% usage isn't my idea of good coding. When I test this, sometimes the SQL result is there immediately, sometimes the wait/loop is at 4 or 5 before it succeeds. Meanwhile the operator is looking at the hourglass.

Thanks for your help, Eddie

[Toyman]

You're right... I meant AfterUpdate...

Here's the code I'm using...

Private Sub SubclassDescription_AfterUpdate()
If IsNull(Me.SubclassDescription) Then
Exit Sub
End If

Me.Refresh

Call UpdateSubclassDescriptionFields(Me.Assortment_ID, SubclassDescription)

Me.Recalc

End Sub

In the sub routine I have the following code snippet...

Public Sub UpdateSubclassDescriptionFields(intAssortmentID As Long, strSubclassDescription As String)

Dim WaitCount As Integer
WaitCount = 0

Debug.Print intAssortmentID

Dim Cnxn As ADODB.Connection
Dim rsDetail As ADODB.Recordset

Set Cnxn = OpenADODetailsConnection
Set rsDetail = New ADODB.Recordset

Dim strSQL As String
strSQL = "SELECT * FROM Tbl_Boom_Detail "
strSQL = strSQL & "WHERE [Assortment ID] = " & intAssortmentID

Debug.Print "open " & Now()
ReadDetail:
rsDetail.Open strSQL, Cnxn, adOpenDynamic, adLockPessimistic


If rsDetail.EOF Then
Debug.Print "eof1"
If WaitCount < 5 Then
WaitCount = WaitCount + 1
Call Wait(1)
Debug.Print "wait " & WaitCount
rsDetail.Close
GoTo ReadDetail
Else
Debug.Print "eof2"
GoTo CloseRecordSet
End If
End If

....

End Sub

The Wait() subroutine is a loop until the second in Now() function changes. Obviously, putting the CPU at 100% usage isn't my idea of good coding. When I test this, sometimes the SQL result is there immediately, sometimes the wait/loop is at 4 or 5 before it succeeds. Meanwhile the operator is looking at the hourglass.

Thanks for your help, Eddie

Try this and see if it works better:
Private Sub SubclassDescription_AfterUpdate()
If IsNull(Me.SubclassDescription) Then
Exit Sub
End If
You don't need the if/then statement above because if you caused the after update event of this field, it would not be a null value

Me.Refresh

I am assuming "SubclassDescription" is a field, so you need to refer to it as either "Me." or "Forms!YourForm!". I would pass the value to a veriable and then put it in the function.

Dim AssID as Long
Dim sClassDesc as String

AssID = Me.Assortment_ID
sClassDesc = Me.SubclassDescription
UpdateSubclassDescriptionFields(AssID , sClassDesc)

Me.Recalc

End Sub

[EddieN1]

Thanks, Toyman,

However, that doesn't solve the basic issue. Part of the issue is that I want to update a field that is in the Table but isn't in the Form. Therefore, I don't have a Me.xxx to update... and I would like to avoid adding a dummy, invisible control to the form to accomplish this.

If I'm working with a new row, I can handle it. If a field is populated and I use the AfterUpdate event to do a Refresh, then call an ADO routine using the Autonumber primary key to update another field, if I get a recordset.eof, then I know the update hasn't finished yet. So, I do a call wait(1), then try again. I set the limit at 5 retries, but I always get a hit after no more that 3 retries. However, if the row already exists, then the recordset.eof is false, but the data elements are all the values before the Refresh takes effect. If I put in a call wait (2), before I open the ADO recordset, then I get the newly updated field values. However, I don't want to wait 2 seconds if the Refresh is done after 1 second. So, I'm back to the basic question... if I do a Me.Refresh, then call an ADO routine that reads the table, how can I be sure the Refresh is finished before my ADO routine reads the table row?

By the way, this first showed up when the backend was a SQL Server database. However, the same thing occurs if the backend is an Access 2007 database. Apparently, the problem is that when the front end issues a Refresh, it can't determine, or doesn't care, if the Refresh is completed before it proceeds to the next instruction.

Any help would be greatly appreciated.

Thanks, Eddie