Solved

Display the active record of a subform on the main form

Posted on 2008-06-22
15
1,292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to get data off Lotus Notes server 4 77
Intermittent OleDbConnection Error 20 54
View SQL Count Records 3 32
Ms access query change into mysql database 3 24
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

710 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