docmd.gotorecord on a sub form

  • Thread starter Thread starter Geoffrey
  • Start date Start date
G

Geoffrey

Does anyone know how to position on a record on a sub form.

Sub form is called EasyBookerSelectFlight

reccount = [EasyBookerSelectFlight].form.currentrecord
me.[EasyBookerSelectFlight].requery
docmd.gotorecord, acdataform, "EasyBookerSelectFlight", reccount

gets an error message
 
Geoffrey said:
Does anyone know how to position on a record on a sub form.

Sub form is called EasyBookerSelectFlight

reccount = [EasyBookerSelectFlight].form.currentrecord
me.[EasyBookerSelectFlight].requery
docmd.gotorecord, acdataform, "EasyBookerSelectFlight", reccount

gets an error message
Click to expand...

Syntactically, you can do it like this:

With Me!EasyBookerSelectFlight
reccount = .Form.CurrentRecord
.Requery
.SetFocus
End With
DoCmd.GoToRecord acActiveDataObject, , acGoTo, currentrecord

Thinking about this logically, though, this method *must* be unreliable,
since you cannot know that the same record will be in the same position
in the recordset -- or even present at all -- after you requery the
subform. A better approach is to capture the value of the current
record's primary key field, then go back to that record by finding the
primary key. Like this:


Dim RecID As Variant

With Me!EasyBookerSelectFlight.Form
RecID = !IDField.Value
.Requery
.Recordset.FindFirst "IDField = " & RecID
End With

Note that, if IDField is a text field, you have to change the FindFirst
criteria so that the value of RecID is enclosed in quotes; e.g.,

.Recordset.FindFirst _
"IDField = " & Chr(34) & RecID & Chr(34)
 
I wanted to thank Dirk for pointing me in the right direction. However, Dirk's solution didn't work for me because I'm trying to go to a specific record on a subform.

Here's the solution that worked for me where SPU_ID is the primary key to record set.

With Forms!MyMainForm!MySubForm.Form
.Recordset.FindFirst "SPU_ID = " & lngSPU_ID_Cur
End With

Thank you very much, Curt
 
Back
Top