Setting focus to a control in continous form after update

[sross81]

Hello,

I am trying to set the focus to a particular control for the current record after a form is updated. It always just goes back up to the top of the form to the first field no matter how I write the code. Can anyone see what I am doing wrong. I am using the me.currentrecord and I stepped through and it is pointing to the correct record.

Me.Refresh
Dim db As Database
Dim strEncounterUpdateUnitSQLString As String
Dim qdf As QueryDef
Dim myrecnum As Long
myrecnum = Me.CurrentRecord
' Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("sp_UpdateIRRMeasure")

'Build SQL string to pass into sp_GetWorklistAMI based on user input parameters.
strEncounterUpdateUnitSQLString = "EXEC sp_UpdateIRRMeasure"
qdf.ReturnsRecords = False
'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strEncounterUpdateUnitSQLString

'Execute the stored procedure.
qdf.Execute
qdf.Close

Me.Requery
Forms!frm_VarianceTracking!frm_IRR!cboAbstractor.SetFocus
'DoCmd.GoToRecord , , , myrecnum (commented out right now but tried both ways)
Me.Recordset.Move myrecnum
Me.RecordLocks = False

 

[pbaldy]

Does this work for you?

http://www.baldyweb.com/Requery.htm

 

[sross81]

Thank you for the reply. I tried the code and I even opened the sample database and looked at it and I feel like I am doing the same thing, but mine doesn't refresh to the record I was working on. It may be some of the other stuff I have going on in the form. I need that stored procedure to execute before the requery though.

Private Sub DateInitiated_AfterUpdate()
Me.Refresh
Dim db As Database
Dim strEncounterUpdateUnitSQLString As String
Dim qdf As QueryDef
Dim rs As Object
Dim lngBookmark As Long

lngBookmark = Me.IRRID
' Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("sp_UpdateIRRMeasure")

'Build SQL string to pass into sp_GetWorklistAMI based on user input parameters.
strEncounterUpdateUnitSQLString = "EXEC sp_UpdateIRRMeasure"
qdf.ReturnsRecords = False
'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strEncounterUpdateUnitSQLString

'Execute the stored procedure.
qdf.Execute
qdf.Close

Me.Requery

Set rs = Me.RecordsetClone
rs.FindFirst "IRRID = " & lngBookmark
Me.Bookmark = rs.Bookmark
Set rs = Nothing

Me.RecordLocks = False

End Sub

 

[sross81]

I tried commenting out all of the code that runs the stored procedure and it still refreshing to the top record.

 

[sross81]

Okay so I realized the only thing I had left out that was in the example was the error handling code so I added that and it solved all my problems. Here is an example for anyone else in need:


'Save the new record that has been started so that it can be updated by a stored procedure with the correct measure below.
Me.Refresh

Dim db As Database
Dim strEncounterUpdateUnitSQLString As String
Dim qdf As QueryDef
Dim rs As Object
Dim lngBookmark As Long

On Error GoTo Err_cmdRequery_Click

'Set database variable to current database.
Set db = CurrentDb
'Open QueryDef object.
Set qdf = db.QueryDefs("sp_UpdateIRRMeasure")

'Build SQL string to pass into sp_GetWorklistAMI based on user input parameters.
strEncounterUpdateUnitSQLString = "EXEC sp_UpdateIRRMeasure"
qdf.ReturnsRecords = False
'Assign query def sql command to the sql string built form user selections.
qdf.SQL = strEncounterUpdateUnitSQLString

'Execute the stored procedure.
qdf.Execute
qdf.Close


'Bookmark the current record before it is requeried to pull in the measure.
lngBookmark = Me.IRRID
'Requery and move to the record that was being edited
Me.Requery
Set rs = Me.RecordsetClone
rs.FindFirst "IRRID = " & lngBookmark
Me.Bookmark = rs.Bookmark

Exit_cmdRequery_Click:
Set rs = Nothing
Exit Sub
Err_cmdRequery_Click:
MsgBox Err.Description
Resume Exit_cmdRequery_Click
'Keep the records unlocked so that it can continue to be edited.
Me.RecordLocks = False

 

[pbaldy]

Glad you got it sorted out.

 

[gemma-the-husky]

another way, if the recordset is the same, and you are justr refreshing the data

dim pos as long

pos = me.recordset.absoluteposition
me.requery
me.recordset.absoluteposition = pos