Access 2007 Requery Recordset

[Jeremiah32]

Code syntax updates field (intNoUnits) based on primary (strProviderName) and foreign (strSubsidy) keys. I noticed that when the value has been changed, the field value remains the same unless I exit and reenter the form.

I tried including Docmd.requery and me.requery at end of the "End With" statement but with no luck.

I know that I can close and reopen the connection but If I did that then user will lose the record position. What would be the best approach. I've never requery before so I not sure how to proceed.

RecordSource is tblEligibilityCharacteristics
Control source for Me.txtintNoUnits is unbound (blank).



Private Sub cmdUpdate_Click()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strMsg As String
Dim iResponse As Integer

' Specify the message to display.
strMsg = "Do you wish to make changes to record?" & Chr(10)
strMsg = strMsg & "Click Yes to Save or No to Discard changes."
' Display the message box.
iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")
'Use the ADO connection that Access uses
' Check the user's response.
If iResponse = vbYes Then

Set conn = CurrentProject.AccessConnection

Set rst = New ADODB.Recordset

With rst
.Open "SELECT * FROM tblEligibilityCharacteristics Where [strProviderName] = " & Chr(34) & Me.cbostrProviderName & Chr(34) & " And [strSubsidy] = " & Chr(34) & Me.cbostrSubsidy & Chr(34), conn, adOpenDynamic, adLockOptimistic

.Fields("txtintNoUnits").Value = Me.intNoUnits
.Update
.Close
End With

Me.Requery

Else
'Do Nothing
End If
Set rst = Nothing
Set conn = Nothing
End Sub

 

[MajP]

Any reason for not simply using a bound form to do this? This seems like a lot of work for a work around to do what Access does natively.