Display the active record of a subform on the main form

Hello

I am creating a form that displays a select query in a sub form, and I wish to display the current record of the sub form, matching fields with corresponding text boxes or labels that are easy to read.  Both main and subform data sources are the same query. qry_RateCode_NM

I would like to set the record source to something like
frm_NoRateMatch.[CoCode]=frm_TAB_qry_RateCode_NMSub.[Co Code]
for each field on both forms.  but even if the main form is unbound I still cant get the text boxes or labels to display or update the active record of the subform.  

Thanks
larspankyAsked:
Who is Participating?
 
peter57rConnect With a Mentor Commented:
To display the current record in the subform as the record in the main form you can do something like this in the subform...
Private Sub Form_Current()
Me.Parent.Filter = "cocode= " & Me.cocode
Me.Parent.FilterOn = True
End Sub

You must be sure that you have no linkchild and linkmaster entries in the the subform container control.
0
 
frankyteeCommented:
>Both main and subform data sources are the same query
if both use the same data source then why do you need subform? just one form would do
0
 
MikeTooleCommented:
Use the same physical recordset for both forms:
Private Sub Form_Load()
Set Me.SubForm1.Form.Recordset = Me.Recordset
End Sub
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
wiswalldConnect With a Mentor Commented:
If you are using a datasheet in the subform then this will set the parent form to the current record on double click of a row

Dim rs As Recordset
 Set rs = Parent.RecordsetClone
 rs.FindFirst "eventnumber=" & EventNumber     'the fields to match
 Parent.Bookmark = rs.Bookmark


Why are you not using parent/child relationships?
0
 
larspankyAuthor Commented:
I tried the filter and its prompting me for the filter source each time I change a record.

 "Enter Parameter Value: Me.Employee_ID"

This is the event I used.
Private Sub Form_Current()
Me.Parent.Filter = " Employee_ID & Me.Employee_ID"
Me.Parent.FilterOn = True

End Sub
0
 
larspankyAuthor Commented:
Dim rs As Recordset
 Set rs = Parent.RecordsetClone
 rs.FindFirst "Employee_ID=" & Employee_ID     'the fields to match
 Parent.Bookmark = rs.Bookmark

That works as an OnClick.  

Is there any way to give this behavior to the subform record selector?  It copies the data to the main form, but selects the top record of the subform each time.

0
 
frankyteeCommented:
it should be:
Private Sub Form_Current()
Me.Parent.Filter = "Employee_ID = " & Me.Employee_ID
Me.Parent.FilterOn = True
End Sub
0
 
frankyteeConnect With a Mentor Commented:
you must be running the below code from your subform which looks for the record in the parent (main form)
 Set rs = Parent.RecordsetClone
 rs.FindFirst "Employee_ID=" & Employee_ID     'the fields to match
 Parent.Bookmark = rs.Bookmark

the "standard" way to use main forms and subforms is to filter or look for the record in main form which would then automatically filter the records in your subform through the parent/child link in the form design, so i'm not sure why you want to search for records seperately in the subform.
post the exact main form/subform design and what (and from where) you are trying to do.
0
 
larspankyAuthor Commented:
Ill try that,

All I want to do is display the current record selected in a subform on the main form.  I would prefer to use labels.  There is information in the records that needs to be highlighted.  Next to the display area there are two buttons that pull up the original table records from the subform query for editing.  

MS included something in Access 2007 called a split form.  That is the basic function I am looking for.

Ill try your suggestions.  Thanks

0
 
larspankyAuthor Commented:
On the main form under each individual field control source.....=[frm_Subform].Form![ControlName]

Works so far.  I'm testing it, if this works Ill close this thread and award points.

thanks for everyones help.
0
 
MikeTooleConnect With a Mentor Commented:
larspanky,
Did you not try the solution I suggested? Perhaps it wasn't clearly explained:

The recordset that is associated with one of your forms - the main form or the sub form, it doesn't matter - can be assigned as the recordset of the other one at runtime when the form is loaded. Then there is no need to pick up the values for one form from the other, they both use the same recordset - which also means they have the same current record.

In the attached mdb the main form frmSynchRecordset has record source: SELECT * FROM [Order Details];
This is assigned as the recordset for the Subform in the form's load event:
Private Sub Form_Load()
Set Me.SubForm2.Form.Recordset = Me.Recordset
End Sub

The subform's controls are bound to the main form's recordset in the same way as they would be to the sub-form's own recordset.

The Record Source for the Sub form could be blank and this will still work. In fact I've given it a query that will never return any records, just to have the convenience of having Access prompt field names when setting a Control Source. This record source is ignored at runtime since the subform is assigned the main form's recordset:
SELECT * FROM [Order Details] WHERE True=False;


SharedRecordset.mdb
0
 
wiswalldCommented:
And maybe I did not explain to use this code in the double click of the subform record not the main form

Dim rs As Recordset
 Set rs = Parent.RecordsetClone
 rs.FindFirst "eventnumber=" & EventNumber     'the fields to match
 Parent.Bookmark = rs.Bookmark
0
 
wiswalldCommented:
I did see frankytee pointed that out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.