Solved

Display the active record of a subform on the main form

Posted on 2008-06-22
15
1,218 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now