findfirst from subform to subform

[Guest]

Hello,

I am having some trouble trying to figure out how to double click a record
from datasheet view in one subform and have it open an unbound form and go to
a record in subform in it.

'FRM_Main_Search' is the unbound form containing the subform I want to find
the record in.

Any help would be fantastic!! thanks



Private Sub Form_DblClick(Cancel As Integer)
stDocName = "FRM_Dws_main_search"
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim frm As Form
Dim strWhere As String


strWhere = Forms![FRM_Dws_main_search].[FRM_DWS_Core_Search].Form.ID = "
& Me.[ID] & "


Set frm = Forms![FRM_Dws_main_search].[FRM_DWS_Core_Search].Form
With frm.RecordsetClone

Debug.Print strWhere

.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Is the form filtered?"
Else
'Selects and stays on record the user has come from

frm.Bookmark = .Bookmark
End If
End With
End Sub

 

[Allen Browne]

Sevaral issues here.

1. Event
If you are trying to double-click the control in the first subform, you will
need to use its DblClick event. In a datasheet, I suspect the form's
DblClick event would normally only occur for the RecordSelector.

2. Criteria
The criteria needs to reference a field name in the target form, and a
value. Something like:
strWhere = "ID = " & Me.ID

3. Delimiters
If the target field is a Number field, use the example above.
If it is a Text field, add quote delimiters:
strWhere = "ID = """ & Me.ID & """"

4. Unbound target
If the form you are opening is unbound, there is no point using criteria on
it. Just use:
DoCmd.OpenForm stDocName

Possible alternative
You might also be able to solve the problem by simplifying the search form
so it does not need a subform. Using a Continuous View form, you can put the
search boxes in the Form Header section, and show the results in the Detail
section (one per row.)

That would let you open the search form showing just the matching result(s)
with:
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Guest]

Thanks Allen, it has worked fantastically. Enjoy the winter over there!!

Sevaral issues here.

1. Event
If you are trying to double-click the control in the first subform, you will
need to use its DblClick event. In a datasheet, I suspect the form's
DblClick event would normally only occur for the RecordSelector.

2. Criteria
The criteria needs to reference a field name in the target form, and a
value. Something like:
strWhere = "ID = " & Me.ID

3. Delimiters
If the target field is a Number field, use the example above.
If it is a Text field, add quote delimiters:
strWhere = "ID = """ & Me.ID & """"

4. Unbound target
If the form you are opening is unbound, there is no point using criteria on
it. Just use:
DoCmd.OpenForm stDocName

Possible alternative
You might also be able to solve the problem by simplifying the search form
so it does not need a subform. Using a Continuous View form, you can put the
search boxes in the Form Header section, and show the results in the Detail
section (one per row.)

That would let you open the search form showing just the matching result(s)
with:
DoCmd.OpenForm stDocName, , , stLinkCriteria

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hello,

I am having some trouble trying to figure out how to double click a record
from datasheet view in one subform and have it open an unbound form and go
to
a record in subform in it.

'FRM_Main_Search' is the unbound form containing the subform I want to
find
the record in.

Any help would be fantastic!! thanks



Private Sub Form_DblClick(Cancel As Integer)
stDocName = "FRM_Dws_main_search"
If Me.Dirty Then Me.Dirty = False
DoCmd.OpenForm stDocName, , , stLinkCriteria
Dim frm As Form
Dim strWhere As String


strWhere = Forms![FRM_Dws_main_search].[FRM_DWS_Core_Search].Form.ID =
"
& Me.[ID] & "


Set frm = Forms![FRM_Dws_main_search].[FRM_DWS_Core_Search].Form
With frm.RecordsetClone

Debug.Print strWhere

.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Is the form filtered?"
Else
'Selects and stays on record the user has come from

frm.Bookmark = .Bookmark
End If
End With
End Sub