Solved

Display the active record of a subform on the main form

Posted on 2008-06-22
15
1,242 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:larspanky
  • 4
  • 3
  • 3
  • +2
15 Comments
 
LVL 19

Expert Comment

by:frankytee
ID: 21844108
>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
 
LVL 77

Accepted Solution

by:
peter57r earned 125 total points
ID: 21844212
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
 
LVL 27

Expert Comment

by:MikeToole
ID: 21846122
Use the same physical recordset for both forms:
Private Sub Form_Load()
Set Me.SubForm1.Form.Recordset = Me.Recordset
End Sub
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 13

Assisted Solution

by:wiswalld
wiswalld earned 125 total points
ID: 21849868
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
 

Author Comment

by:larspanky
ID: 21850397
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
 

Author Comment

by:larspanky
ID: 21850668
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
 
LVL 19

Expert Comment

by:frankytee
ID: 21852356
it should be:
Private Sub Form_Current()
Me.Parent.Filter = "Employee_ID = " & Me.Employee_ID
Me.Parent.FilterOn = True
End Sub
0
 
LVL 19

Assisted Solution

by:frankytee
frankytee earned 125 total points
ID: 21852391
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
 

Author Comment

by:larspanky
ID: 21852521
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
 

Author Comment

by:larspanky
ID: 21852756
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
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 125 total points
ID: 21853515
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
 
LVL 13

Expert Comment

by:wiswalld
ID: 21854797
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
 
LVL 13

Expert Comment

by:wiswalld
ID: 21854801
I did see frankytee pointed that out.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question