 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / June 2006YAFSP Form/subform
Yet another form/subform problem. They're not talking to each other properly. Form info: name: fmAEPtest Recordsource: Students subform info: name: sfmTab source object: fmTab09 Link Child: StudNum;Grade Link Master: StudNum;StudGrade fmTab09 info: Recordsource: AEP When a student logs in, their StudNum is placed in a control on fmAEPtest and also on sfmTab. Same with their Grade. But the student's record is not being pulled up from table AEP. Anything obvious that I've done wrong so far? TIA Ric What is the RecordSource for fmTab09? Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * > Yet another form/subform problem. They're not talking to each other > properly. [quoted text clipped - 18 lines] > Anything obvious that I've done wrong so far? > TIA Ric > What is the RecordSource for fmTab09? Hi Crystal, As I indicated: > > fmTab09 info: > > Recordsource: AEP TIA ric You have Link Child: StudNum;Grade Link Master: StudNum;StudGrade Are these the control NAMES on each respective form? You must use the name property, not the controlsource and the controls must be ON the forms -- usually the link controls on the child form have VISIBLE=No Are records going into AEP? Are the link fields filled out or blank? Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * >>What is the RecordSource for fmTab09? > [quoted text clipped - 6 lines] > > TIA ric > You have > [quoted text clipped - 5 lines] > controls must be ON the forms -- usually the link controls > on the child form have VISIBLE=No The control names of the Master form are: txtStudOEN txtStudName txtStudGrad The control names on the Child form are: txtOEN txtGrade There are other controls on each form but these are the key ones to get some linking happening. Every one of the above controls that I've mentioned are *unbound*. I selected the following as links because they were offered to me from the recordsources: > Link Child: StudNum;Grade > Link Master: StudNum;StudGrade Do you see a problem here? > Are records going into AEP? Are the link fields filled out > or blank? Records are going into AEP, the main table for records, that's right. The link fields as currently existing are filled out, displaying the correct values. They are grabbing them from the opening password form. Right now though, I'm not being allowed to use the code with the arrows: If Me!cboStudNum.Column(3) = 11 Then Forms![fmAEPtest]!TabAEP.Pages(2).SetFocus > Forms![fmAEPtest]!sfmTab![Goals].Locked = False > Forms![fmAEPtest]!sfmTab![ActionPlan].Locked = False > Forms![fmAEPtest]!sfmTab![Review].Locked = False End If I'm getting 'Object doesn't support this property or method'. So I need to get past that (this would allow me to edit the record). But I also need to be able to click on the other three tabs and pull up the correct recordset for them. TIA Ric ".. I selected the following as links because they were offered to me from the recordsources..." The countrols should be BOUND if they are in your recordsource "Records are going into AEP, the main table for records, that's right. The link fields as currently existing are filled out, displaying the correct values. They are grabbing them from the opening password form." They still should be bound if you want the values written to your table your syntax is wrong on these lines: Forms![fmAEPtest]!sfmTab![Goals].Locked = False Forms![fmAEPtest]!sfmTab![ActionPlan].Locked = False Forms![fmAEPtest]!sfmTab![Review].Locked = False ---> SHOULD BE ---> Forms![fmAEPtest]!sfmTab.form.[Goals].Locked = False Forms![fmAEPtest]!sfmTab.form.[ActionPlan].Locked = False Forms![fmAEPtest]!sfmTab.form.[Review].Locked = False you can also use DOT . instead of BANG ! The first reference to a subform is to the placeholder -- where you specify height, width, ControlSource, etc. .form after the subfrom reference puts you INTO the form itself that is in the subform placeholder -- THEN you can refer to controls and properties on the subform Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * >>You have >> [quoted text clipped - 50 lines] > > TIA Ric Hi Crystal, Thanks again for your help. I'm a patient guy. Especially with this problem, as I have another db with a slightly different design that is working perfectly. But I'd like to get this working right as I know this method is cleaner (fewer subforms/recordsets etc.) > ".. I selected the following as links because they were > offered to me from the recordsources..." > > The countrols should be BOUND if they are in your recordsource Yes I realise that. The controls that I mentioned (the unbound ones) are all there to either pull up names to be viewed or to ascertain that the correct record is being pulled up (some are temporary controls and will be deleted when this form is working properly). The key controls that hold the data are on subform [sfmTab]. [Goals], [ActionPlan] and [Review]. But I can't create a parent child link with these directly. > "Records are going into AEP, the main table for records, > that's right. The link fields as currently existing are [quoted text clipped - 3 lines] > They still should be bound if you want the values written to > your table As I mentioned above, the unbound controls don't need to write their data to any table. > your syntax is wrong on these lines: > ---> SHOULD BE ---> > > Forms![fmAEPtest]!sfmTab.form.[Goals].Locked = False > Forms![fmAEPtest]!sfmTab.form.[ActionPlan].Locked = False > Forms![fmAEPtest]!sfmTab.form.[Review].Locked = False I changed the syntax but am still getting the same error. So I think the error is coming from somewhere else. There still doesn't appear to be any communication between parent and subform. The three main fields mentioned above. Ric Hi Ric, on this code: Forms![fmAEPtest]!sfmTab.form.[Goals].Locked = False Forms![fmAEPtest]!sfmTab.form.[ActionPlan].Locked = False Forms![fmAEPtest]!sfmTab.form.[Review].Locked = False where are you executing it from? If you are in the code behind, for instance, fmAEPtest, you can do this: me.sfmTab.form.Goals.Locked = False me.sfmTab.form.ActionPlan.Locked = False me.sfmTab.form.Review.Locked = False if you are NOT in the code behind the form, you may need DoEvents after you execute the code to make the changes happen right away also, try removing the brackets Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * > Hi Crystal, > [quoted text clipped - 43 lines] > > Ric > Forms![fmAEPtest]!sfmTab.form.[Goals].Locked = False > etc... > where are you executing it from? If you are in the code > behind, for instance, fmAEPtest, you can do this: I'm executing behind another form (password form). > if you are NOT in the code behind the form, you may need > > DoEvents > also, try removing the brackets Tried those but ithey didn't help. Crystal let me give you more insight as to where I'm coming from (what's working) and where I'd like to go (what's currently not working). My working version has a Password form that opens the Main form (fmAEP). fmAEP has a Tab control with four Pages. Not knowing any better at the time, I placed four separate subforms on each Page of the Tab control. They all look the same and function the same, but have different names for their controls. fmAEP the parent, has Students as its recordsource. Each subform (sfmTab09, 10, 11, 12) has its own recordsource, AEP09qry, 10qry, 11qry, and 12qry respectively. Parent/Child Link is on StudNum, and the forms talk to each other perfectly. ======================================= The version that I'm trying to get to work also has a Password form that opens the Main form (fmAEPtest). fmAEPtest has a Tab control with four Pages. This time I placed *one* subform (sfmTab) on the Tab control, as I learned it would show through on all four Pages. fmAEPtest, the parent, has Students as its recordsource. Subform sfmTab has AEPqry as its recordsource. But because now that AEPqry is pulling up *all* records from AEP table, a second qualifier is needed in the Parent/Child Link to select not just student number, but also student grade. But the Parent/Child recordsources are given to me to choose from. So I chose what was most appropriate: Link Child: StudNum, Grade Link Master: StudNum, StudGrade Not sure why, but now the form/subform are sort of talking to each other. The subform is no longer locked, BUT... instead of pulling up the correct student, the FIRST student in the Students table is being displayed in the subform, no matter which student logs in. There is also a second problem. When I switch tab pages, the record should be locked, but is now unlocked. Would you have any ideas Crystal? TIA Ric "fmAEPtest has a Tab control with four Pages. This time I placed *one* subform (sfmTab) on the Tab control, as I learned it would show through on all four Pages." If this is true, the subform is NOT on a tab page -- it is on the main form. It is just in the same space as the tab control. If you did indeed want it just on one page, you can: 1. cut it 2. click on the page where it blongs 3. paste it '~~~~~~~ now, for your student issue: when the student logs in to the password form, you can fmAEPtest using the WHERE clause of the OpenForm action -- this will filter the form for just that student if StudNum is a number: DoCmd.OpenForm "fmAEPtest", , , _ "StudNum = " & me.StudNum_controlname if StudNum is text: DoCmd.OpenForm "fmAEPtest", , , _ "StudNum = '" & me.StudNum_controlname & "'" '~~~~~~~~~~~` link fields you said that you have: Link Child: StudNum;Grade Link Master: StudNum;StudGrade you also said: The control names of the Master form are: txtStudOEN txtStudName txtStudGrad The control names on the Child form are: txtOEN txtGrade Therefore --> Link Child: StudNum;txtGrade Link Master: txtStudOEN;txtStudGrad And StudNum needs to be ON the subform for the linkChildFields -- substitute it's control NAME for "StudNum" in LinkChildFields '~~~~~~~` "BUT... instead of pulling up the correct student, the FIRST student in the Students table is being displayed in the subform, no matter which student logs in." I believe that is because you do not have a StudNum control on your subform Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * >>Forms![fmAEPtest]!sfmTab.form.[Goals].Locked = False >>etc... [quoted text clipped - 58 lines] > > TIA Ric Well Crystal we are making some progress, slowly but surely. > "fmAEPtest has a Tab control with four Pages. This time I > placed *one* subform (sfmTab) on the Tab control, as I [quoted text clipped - 3 lines] > on the main form. It is just in the same space as the tab > control. Well I don't understand this concept. I put the subform physically on top of the first page of the Tab control. But I had to use the following procedure to make it show through on all four pages: First I placed it on the side, somewhere out of the way, on the form. Then I dragged it onto the first page of the tab control. That was the only way I could get it to show through on all four pages (which is what I want). > '~~~~~~~ > [quoted text clipped - 4 lines] > DoCmd.OpenForm "fmAEPtest", , , _ > "StudNum = '" & me.StudNum_controlname & "'" Yes, it is text in my case. I used your code and it did pull up the correct student! But there are still two issues that need to be addressed. Each student can have four records, depending on their Grade number. > '~~~~~~~~~~~` > > link fields > Therefore --> > [quoted text clipped - 4 lines] > linkChildFields -- substitute it's control NAME for > "StudNum" in LinkChildFields This did not work out well at all. There are many conflicting/confusing issues at play here. With Students as my recordset for the Parent form and AEPqry as my recordset for my Child form, I am GIVEN fields that I can choose from to create the link. Three of the four names that you had suggested are not in the Recordsets. This certainly can't help can it? Some are bound, some are unbound controls. Do all Linked fields need to be bound controls? Also, for the Default value Property, should I now leave it blank for all link controls? Besides getting the Links straightened out I have two more challenges ahead. The correct Links may reduce those challenges to just one. I need to pull up the correct student record. The best way to pull it up is to filter by student number AND grade. Grade ain't working right now. Also when I click on another tab page, say Grade 11 page, I need to pull up that student's grade 11 record. That could be tricky with just one Tab and one recordset. I read a Googled thread where it said clicking another tab was not sufficient to kick into play underlying code. That it was also necessary to click somewhere off the Tab Page for the code to execute. That wouldn't be acceptable for this db. TIA Ric Hi Ric since you want the subform on all the pages, that is fine -- just wanted you to realize that it is really not on ANY of the pages... it is on the form that the tabcontrol is on...therefore it, as anything that is on the mainform, always shows... "Each student can have four records, depending on their Grade number" -- you should not have the SAME student in the Students table more than once -- use a related table for the combination of student and grade if you are keeping historical records. A StudentID (autonumber field in Students table) and should uniquely identify a particular person. "Do all Linked fields need to be bound controls?" no, but it doesn't sound like you have information that needs to be linked with unbound controls. If you are going to use queries to show information from a particular table, use the * field to drag ALL fields from the table -- then, if you want to specify particular fields for criteria, do not show them (since the * gets everything). As a general rule, each form or subform should be based on just one table (FORM --> RecordSource property) "I am GIVEN fields that I can choose from to create the link." If your forms are based on tables, then all the fields in that table should be available to pick from to drag onto your form from the fieldlist (from the menu --> View, Fieldlist) After you drag a field, change the Name property of the control to something logical if it is ambiguous. "...Grade 11 page..." As I said, Students needs to be in one table and StudentGradeInfo needs to be in another. Rather than making a bunch of queries for each grade and a bunch of subforms, do this: make an unbound combo on the mainform --> Name --> PickGrade RowSource --> SELECT DISTINCT Grade from StudentGradeInfo then, LinkMasterFields can use this unbound combo and Grade can be a control on the subform, which is also specified in LinkChildFields -- it will automatically be filtered because of the LinkMasterFields and LinkChildFields Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * > Well Crystal we are making some progress, slowly but surely. > [quoted text clipped - 70 lines] > > TIA Ric Thanks for your help and patience Crystal! One thing is very obvious so far. It is extremely difficult to explain one's db situation in a thread on the internet!!! > "Each student can have four records, depending on > their Grade number" [quoted text clipped - 3 lines] > table for the combination of student and grade if > you are keeping historical records. I have confused you by leaving out some words. It should have read: "Each student can have four records IN THE AEP TABLE, depending on their Grade number". The Students table is clean. One Student record per each student. No auto number. Just an 'OEN' number. > "...Grade 11 page..." > > As I said, Students needs to be in one table and > StudentGradeInfo needs to be in another. Rather > than making a bunch of queries for each grade and > a bunch of subforms, do this: Right. I had a design choice at the beginning and I've placed the grade with the Student Number and Student Name. I could have created a second table with Student Number and Grade, but I don't really see much difference. Students table: Student Number LastName FirstName Grade or Grade table: Student Number Grade =============== Well, the correct record is now being displayed when each student logs in! The correct Tab page opens, and the correct record is displayed. There remains one tough problem however! When each of the other tabs is clicked the student's record for that tab should be displayed. IOW, click on Tab page (Grade11) and display the Gd. 11 record from the AEP table. Click on Tab page (Grade 10) and display the Gd. 10 record from the AEP table. I have this all working in my 'good' db, but it remains a challenge in this new one. Ric Hi Ric, you are welcome, glad we are making progress! What is the structure of the AEP table? Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * > Thanks for your help and patience Crystal! > [quoted text clipped - 55 lines] > > Ric > Hi Ric, > > you are welcome, glad we are making progress! > > What is the structure of the AEP table? AEP table: AEPID, StudNum, Grade, Goals, ActionPlan, Review, LastEdit AEPID is number field LastEdit is Date field The rest are all text fields. Thanks again! Ric Hi Ric, make ONE subform with AEPID, StudNum, Grade, Goals, ActionPlan, Review, LastEdit this subform will be based directly on the AEP table you can either: 1. have an unbound combobox to pick a grade and show that grade in the subform by using the combobox in the LinkMasterFields (along with the control for Studnum) OR 2. use tabs to show each grade (as you are doing now) create calculated (hidden) controls on the main form (assuming Grade is stored as numbers -- if not, then delimit with quotes) Name --> Grade9 ControlSource --> =9 Visible --> no Name --> Grade10 ControlSource --> =10 Visible --> no Name --> Grade11 ControlSource --> =11 Visible --> no Name --> Grade12 ControlSource --> =12 Visible --> no You can use the same subform multiple times -- just change LinkMasterFields to be the appropriate control from Grade9 to Grade12 In both cases, the controlnames for Studnum and Grade will be used in LinkMasterFields and LinkChildFields (on main form and subform, respectively) Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * >>Hi Ric, >> [quoted text clipped - 13 lines] > > Ric Hi Crystal, I know you're on the right track with your idea, but I'm going to need some 'spelling out' before I can implement what you have in mind. Firstly, recall that the 'grade' field in my Students table is named StudGrade. The 'grade' field in my AEP table is named Grade. I don't think you have accounted for this. I created the four 'invisible' controls for the four grades and placed them on the main form. For now I've left them visible so I can see that they are acting correctly. >You can use the same subform multiple times -- just change >LinkMasterFields to be the appropriate control from Grade9 to Grade12 As I mentioned before I am _given choices for my Links. You are saying to ignore those choices and use Grade9, Grade10, Grade11, Grade12? When I do that, use StudNum and Grade9, and then switch to the second Tab page I see the same MasterChild Links. If I change to StudNum and Grade10 then Grade9 disappears. If I go to the third Tab Page and enter MasterChild Links they over-ride what was in there previously. So there are still many things that I don't understand. I also don't see how I'll be able to change Tab pages and have the correct record pulled up. Only when I change to: Link Child: StudNum;Grade Link Master: StudNum;StudGrade do I have correct communication between the form/subform. But...when I change Tab pages, everything is brain-dead. Nothing changes. TIA Ric > Hi Ric, > [quoted text clipped - 70 lines] > > > > Ric Hi Ric, "As I mentioned before I am _given choices for my Links. You are saying to ignore those choices and use Grade9, Grade10, Grade11, Grade12?" turn the wizard off! and just fill the properties manually. YOU know those controls will work! It is a good idea to become comfortable with properties anyway -- any property you are curious about, press F1 and read help for that property. "Firstly, recall that the 'grade' field in my Students table is named StudGrade. The 'grade' field in my AEP table is named Grade. I don't think you have accounted for this." You should keep the same fieldnames if the information is the same. Since, in your Students table, this is really a current grade, you may want to rename the field to "Grade_cur" so you know it is the SAME info as Grade, just qualified. Anyway, you can adjust my examples according to your actual names -- I am just giving you logic. "Link Child: StudNum;Grade Link Master: StudNum;StudGrade do I have correct communication between the form/subform. But...when I change Tab pages, everything is brain-dead. Nothing changes." Since you want to use pages with each grade ... On each page of your tab control, put a subform control based on the ONE subform that you made -- simply change the Grade reference in LinkMasterFields to the appropriate calculated control. If you have 4 pages, you will have 4 subform controls with the same SourceObject. Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * > Hi Crystal, > [quoted text clipped - 111 lines] >>> >>> Ric Hi Crystal, > You should keep the same fieldnames if the information is > the same. Since, in your Students table, this is really a > current grade, you may want to rename the field to > "Grade_cur" so you know it is the SAME info as Grade, just > qualified. I'd like to get this straight. I understand it's important to have the same field name in tables where you are setting up and one-to-one or a one-to-many relationship. In this case the Students table has a StudGrade field. Each student will have either a text value of 9,10,11,12 each year. I chose a different name in the AEP table for the grade because it's not even a one to many relationship. I have a one-to-many relationship wrt student number in the two tables and thus have kept the same student number name between the two tables: StudNum. But AEP can have for any given student; one, two, three or four records, each with different grade levels. I felt using the same field name would potentially confuse the program. And there would be *no* relationship set up wrt the grade. ======= Thank you! We finally have success! Pheuwww. But let's take stock..... Recall I already had a working version. I had a Tab control with four pages. On each page I had placed a subform control. Each of those subforms though looking identical, had different names: fmTab09, fmTab10,fmTab11, fmTab12. And each of the subforms also had their own recordsets: AEP09qry, AEP10qry, AEP11qry and AEP12qry. It was in those queries that I had filtered for the correct grade. What I now have is a Tab control with four pages. On each page I have a subform control. Each of the subforms though looking identical, have different names: fmTab09, fmTab10,fmTab11, fmTab12 (I had to give them different names, the program would not allow one name). And now, each of the subforms has the same source object, fmTab which has AEP as its recordset. So I have eliminated four queries, and four forms (fmTab09, fmTab10, fmTab11, fmTab12) but am now using fmTab as the source object so I have really eliminated three forms in total. I almost have what I was trying to accomplish. I wanted one form placed on the Tab control so that it showed through on all four pages. Not only would this eliminate extra forms, but it would also make the setup of the four pages a snap. I will now have to go into each page and meticulously measure (I did this before, sigh) the distance of every control's location on the page relative to a corner. Thank you for your help and patience! Ric Hi Ric, Grade fieldname in diffeent tables... It represents the same data -- it should have the same name -- or the same name and then a qualifier -- just my opinion I do this because I wrote a database analyzer and one of its reports is a fieldlist of everything in the database -- listed alphabetically -- so fields that are the same are next to each other on the report. I realize that others do not have this, but it doesn seem to make logical sense... Grade ... Grade_current if you saw these two fieldnames, you could intuitively know they represent the same piece of information " I will now have to go into each page and meticulously measure (I did this before, sigh) the distance of every control's location on the page relative to a corner." No you don't!!! Turn on properties and go to the Format tab set the following properties to be the same for all your subforms: Top Left Width Height Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * > Hi Crystal, > [quoted text clipped - 57 lines] > > Ric Hi Crystal, > Grade fieldname in diffeent tables... > > It represents the same data -- it should have the same name > -- or the same name and then a qualifier -- just my opinion Yes, you are right. Grade and Grade_current makes sense. Having them both start with grade (something I hadn't been doing). But I just couldn't see the logic in naming the fields the same in Students and AEP. That seemed to be asking for problems. > No you don't!!! Turn on properties and go to the Format tab > > set the following properties to be the same for all your > subforms: > > Top, Left, Width, Height Yeah, that's what I meant by 'meticulously measure' :) I learned that trick when I was recreating my form on three pages! Anyhow. To speed things up, I decided to go to my latest version and made the changes there, that you recommended. The subforms that I'd been using, had already been centered and everything, so that was painless. Everything is working and I feel better that the db is now more streamlined. Thanks again! Ric you're welcome, Ric ;) happy to help Warm Regards, Crystal Microsoft Access MVP 2006 * Have an awesome day ;) remote programming and training strive4peace2006 at yahoo.com * > Hi Crystal, > [quoted text clipped - 28 lines] > > Thanks again! Ric |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.