Solved

Display the active record of a subform on the main form

Posted on 2008-06-22
15
1,259 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

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.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

829 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