Link to home
Start Free TrialLog in
Avatar of matthewlorin7
matthewlorin7

asked on

Choose Record on Main Form From Button on Subform

Hello-I am building a database in MS Access 2003.  I have a main form, "frmMain", and a subform, "frmSubDash".  The subform is in the main form and displays two items, "txtReportName" and "txtReportNumber".  I then have the UniqueID hidden.  The subform, which is set to continuous view, filters the records of the main table based on status (draft, ready for review, cleared), which I have filtered from a combobox.  Again, only the report name and number are displayed.  What I want to have is a button to the right of the filtered list of reports on the subform  that will populate the information of the reports (there are about 20 fields) in the main form.  If not a button, then simply clicking on the report name itself.  Any ideas?  Seems like it should be simple, but I can't figure it out.  I've played around with stLinkCriteria and have used this is the past to open up a new form based on criteria in another form, but can't seem to get it to work between a subform and form.  Thank you very much in advance for any help!
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image



<but can't seem to get it to work between a subform and form>

what exactly is your problem?

you can refer to the main form from the sub form by

    me.parent.<property>  
Dim rs As Recordset
 Set rs = Parent.RecordsetClone
 rs.FindFirst "eventnumber=" & EventNumber    'change to your fields
 Parent.Bookmark = rs.Bookmark
For starters, I  put the list and the filters in the mainform.  Put the detail of the selected record in the subform.  You can then link the forms so that most navigation is coordinated automatically.
If the subform is in datasheet view then use that in the double click event for the form.
Avatar of matthewlorin7
matthewlorin7

ASKER

Thank you everyone for the help!  Attached is a screenshot that may help explain what I want to do.  

To capricorn1:  My problem is I need to have the report number (or the hidden UniqueID) in the subform link up with the report number (or hidden Unique ID) in the main form, so that the expanded info is displayed in the main form.  Though, I need to have the subform in "continuous view" mode.  How do I use the stlinkcriteria to link the uniqueIDs when I press the command button on the control form?

To wiswalld: I've played with the code you provided, but cannot make it work.  Do I attach it to the command button (on click) on the subform?

To dqmq:  Thank you for the suggestion, but I need to have a list of the reports that I can filter, and I can't have the main form in "continous view" if I have a subform.  There's probably another way, but it is beyond me.

Thanks again for the help.

Database-Pic.jpg
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
capricorn1:  Thanks for the post.  The filter idea is brilliant, but I can't seem to seal the deal.  Here's the code I have:

Private Sub FilterMain_Click()
Me.frmMain.Filter = "[UniqueID]=" & Me.[UniqueID] 'if IDfield is number type of data
Me.frmMain.FilterOn = True
End Sub

I'm trying to link the unique ID from the subform to that of the main (or filter, rather).  Can you see the problem?  Also, I would like to be able to use the main form to enter data for new records.  Do you think the filter will present problems with this in the future?  I anticipate I will simply have to shut off the filter from an "add new record button" and then access a new record.  Thanks for the continued help.
what is the record source of the main form?

you can go to new record with

docmd.gotorecord,,acnewrec
I forgot to add that I am receiving a "compile error" message that states "method or data member not found" and the UniqueID in Me.UniqueID (2nd line) is highlighted.
maybe reference the subform as

Forms!mainform.subform.form.filter

Change mainform to mainform name and subform to subform name
you have to change that to the actual name of the field.


in design view of the form, select the control and hit F4, see the name of the control from the property sheet
capricorn1:  Record source for the mainform is the main table (tblMain).  Record source for the subform is a query (qryMainSubform).
capricorn1: The actual names of the fields are UniqueID.

wiswalld: I will try referencing the subform as you suggested.  Thanks!
can you attach your db? remove sensitive data.

also did you check the link i posted above?

see this link for reference

http://www.mvps.org/access/forms/frm0031.htm


using the table from the link, how will describe your set up?
How about this: is it possible to use the docmd "gotorecord" from a subform, referencing the mainform?  Basically, I want to click on my subform command button and find that corresponsing ID/record on the mainform.  How can I do this?  
do you want to find the record with that id?

the filter code that i posted should do that.

or the codes posted by wiswalld


I ultimately combined some of each answer, but both got me on the right track.