Full Version: Search Form AND Sub Form
UtterAccess Forums > Microsoft® Access > Access Forms
howardl
Hello,
I am trying to write some code that will enable me to jump to the desired record on a Main Form and also to the desired record on its associated Sub Form.
The operation is started from an on_click event in another (3rd form), hence use of absolute references eg
Forms.frm_projects.control_name as opposed to
Me.control_name or
Parent.control_name etc
.........
See Below under #################
Possible Problem Line Marked +++++++++
I can get the first part to work fine - doing a search on the main Form
Problem appears to be referencing the connected Sub Form and doing a search on that
(Main and Sub Form are both Single Forms - ie both show 1 Record Only)
Any help very much appreciated
...
Howard L'
####### Code Start ##########
Dim Proj_No As Integer
Dim Proj_WS_Next_New As String
'These 2 variables generated on a 3rd form - a test in a Msg Box returns correct Value so these are OK.
'*************** MAIN FORM *************************************************************
'Main Form Search Works Fine
'Open Main Form
DoCmd.OpenForm "frm_projects", acNormal
'Search Main Form
'Find the record that matches the control eg a cbo.
Dim rs_main As Object

'Recordsets - Tables / Queries you want to search
Set rs_main = Forms.frm_projects.Recordset.Clone

'Field (Control) you want to search in eg info from a cbo
rs_main.FindFirst "[txt_proj_no] = " & Proj_No

'What happens if not found - go back to original record
If Not rs_main.EOF Then Forms.frm_projects.Bookmark = rs_main.Bookmark



''******** SUB FORM ****************************************************************
'Sub Form Search - Doesn't Work - Syntax ???

'Sub Form Set Focus - Any Control - This Line appears to Works OK
Forms.frm_projects.fsub_workstages.Form!txt_proj_ws_no.SetFocus


'Find the record that matches the control eg a cbo.
Dim rs_sub As Object

' +++ Recordsets - Tables / Queries you want to search - Poss Problem here on this Line Syntax ???? +++
Set rs_sub = Forms.frm_projects.fsub_workstages.Recordset.Clone

'Field (Control) you want to search in
rs_sub.FindFirst "[txt_proj_ws_no] = " & Proj_WS_Next_New

'What happens if not found - go back to original record
If Not rs_sub.EOF Then Forms.frm_projects.fsub_workstages.Bookmark = rs_sub.Bookmark
'End of Code
'****************************************
Also tried - doesn't work
' +++ Recordsets - Tables / Queries you want to search - Poss Problem here on this Line +++
Set rs_sub = Forms.frm_projects.fsub_workstages.Form.Recordset.Clone
Also tried - doesn't work
' +++ Recordsets - Tables / Queries you want to search - Poss Problem here on this Line +++
Set rs_sub = Forms.frm_projects.fsub_workstages.Form!Recordset.Clone
Also tried Filtering - but I'd prefer Search so I don't have to Unfilter Later:
'Go to Last (New) Record - Doesn't work for fsub
'DoCmd.GoToRecord acDataForm, "Forms.frm_projects.fsub_workstages.Form", acLast


'Go to Last (New) Record - Doesn't work for fsub
DoCmd.GoToRecord acDataForm, "Forms.frm_projects.fsub_workstages.Form!", acLast
howardl
Another simpler way to ask the same question:
When a Main Form / Sub Form Combination is opened eg by selecting a cbo and clicking a cmd button on another form...
How do you make the Main Form show a particular Record
AND ALSO
Make the SubForm show a particular Record
.........
Any help gratefully received
.........
Regards
Howard L'
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.