DoCmd.SearchForRecord in subform

[dwrobins]

I have a main form called scanningdetails and a subform called labortrackingtablesubform.

When scanning a field in the main form I pass a value to a field [text12] in the subform. I then run this code on the field text12.


the code works when I open the subform by itself but not when it is embedded in the scanningdetails form.


Private Sub Text12_AfterUpdate()
DoCmd.SearchForRecord acDataForm, "LaborTrackingTablesubform", acFirst, "[ID]=" & Me.[Text12]
End Sub

I recied Run-Time error'2489'. The object 'labortrackingtablessubform' isn't open.

Any suggestions?

 

[MarkK]

1) When I need a form to be able to navigate to a record by ID, I add a method like this to that form . . .

Public Sub GoToID(ID As Long)
   With Me.RecordsetClone
      .FindFirst "ID = " & ID    [COLOR="Green"] 'use your field and variable names here[/COLOR]
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

. . . and then other code can call that method, passing in the ID to navigate to.

2) If the form is a subform, then it is exposed by the .Form property of a subform control, so if code is running on the main form, and the subform control is called "sfm," then you could do . . .

   Me.SFM.Form.GoToID Me.ID

. . . to cause the subform to navigate to the record in question.
hope that helps,

 

[dwrobins]

Thank you for the quick response. Newer to VBA so not really following.


I have attached the screen shot above. I have a text field called open labor which retrieves the record ID I want the subform to navigate to .

I can get the value passed down to text 12(103 in this example).

I can't get the subform labor tracking to search for record ID= open labor from the main form.

Nor could I get it to work searching record id= text 12.(103 in this example)

It works when searching for text 12 when I have only the subform open.

 

[MarkK]

Subforms are members of other forms, and are therefore not directly addressable by name in Access, or, if addressable by name, must be prefixed by the parent form name, and subform control name that host them. As a result, this code . . .

DoCmd.SearchForRecord acDataForm, "LaborTrackingTablesubform", acFirst, "[ID]=" & Me.[Text12]

. . . doesn't work if the object "LaborTrackingTablesubform" is only open a subform. So you need to find a workaround if you want to navigate in a subform.

One option is that you research the LinkMasterFields and LinkChildFields properties of the subform control. Using these fields, you can link a subform to a parent form and have the subform filtered automatically if the parent form and subform share a field. Changing the value of the field on the parent, either by moving to a new record or updating the value, applies a filter to the subform to only show matching records.

That, or look at my post #2, which provides a way to pass a value to a form, and have it find and navigate to the record on it's own.