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!
Microsoft AccessDatabases

Avatar of undefined
Last Comment
matthewlorin7

8/22/2022 - Mon
Rey Obrero (Capricorn1)



<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>  
Rey Obrero (Capricorn1)

see this link for reference

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

Dim rs As Recordset
 Set rs = Parent.RecordsetClone
 rs.FindFirst "eventnumber=" & EventNumber    'change to your fields
 Parent.Bookmark = rs.Bookmark
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
dqmq

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.
wiswalld

If the subform is in datasheet view then use that in the double click event for the form.
ASKER
matthewlorin7

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Rey Obrero (Capricorn1)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
matthewlorin7

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.
Rey Obrero (Capricorn1)

what is the record source of the main form?

you can go to new record with

docmd.gotorecord,,acnewrec
ASKER
matthewlorin7

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
wiswalld

maybe reference the subform as

Forms!mainform.subform.form.filter

Change mainform to mainform name and subform to subform name
Rey Obrero (Capricorn1)

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
ASKER
matthewlorin7

capricorn1:  Record source for the mainform is the main table (tblMain).  Record source for the subform is a query (qryMainSubform).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
matthewlorin7

capricorn1: The actual names of the fields are UniqueID.

wiswalld: I will try referencing the subform as you suggested.  Thanks!
Rey Obrero (Capricorn1)

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?
ASKER
matthewlorin7

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?  
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
matthewlorin7

Rey Obrero (Capricorn1)

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


ASKER
matthewlorin7

I ultimately combined some of each answer, but both got me on the right track.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.