Microsoft Access A to Z: Everything you need to know about subforms

[@cha1868]

forgot the exact place to click to see the Link Master Fields and Link Child Fields parameters. Thanks to this video I found it again. Thanks Sensei :)

[@lfriedrichsen]

Thanks! You can find a listing of all of my KZbins at learncs.w3spaces.com

[@satyabanukil779]

Respected Madam, your earlier advice on report subtotals was quite helpful to me. This time have a simple query. Suppose i have a main form field called "score", a numeric. In the subform have another similar field with a different name. Now i want to update the subform field "if" the main form field value is greater than 25 (example) which means the subform updates based on the condition of the field in the main form. I tried but not working as I'm probably giving an incorrect syntax. Shall be grateful if advised. Regards

[@lfriedrichsen]

You'll need to use the IIF function to do the "if" test. As for the syntax of how to build an expression using fields from both the main form and subform, I recommend using the Expression Builder to fill in the Control Source property for the different field in the subform. In the Expression Builder, pick through the Forms in the first pane of the dialog of the Expression Builder dialog box and double-click the main form to provide the correct synatx. It will look something like this: =IIf([Forms]![NameOfMainForm]![score]>25, true, false)

[@satyabanukil779]

@@lfriedrichsen Thnx Madam for your prompt and kind advice. I tried the way as advised. But it is showing an error message " ...cannot find the object Table2 (my form name from which the data to be brought to the other form). So I think I am making some mistake in putting the code correctly. In fact the first tabular form I have two fields (Pair No and Score, both numeric fields). In the first form in the first Row the data against Pair No is 1 and the score against 1 is 10. In the same form, in the second row Pair No is 2 and Score is 20. In the second tabular form I have a field SCR1 and SCR2. I want to update the second form SCR fields based on the score corresponding to the "Pair No' of the first form. For example, SCR 1 will be 10 (taking it from the first row of the first form corresponding to the Pair No). Regards.

[@lfriedrichsen]

I "think" your trouble is that you're trying to pull data from two different records from the main form (which I "think" you've named Table2, a confusing name for a form given the word "table" means something else in Access) into the subform. I "think" you need to work with a query to gather all of the data you want to examine at one time into a single record on the main form. Not sure, but this is the best I can do on KZbin. If you were a paying client we'd be talking further. Good luck.

[@jim27105]

Hi Lisa, I'm curious if there's a setting that would allow the subform to display the count of records, but not allow the user to click the left/right arrows to advance. It seems redundant in my situation because there's a vertical scrollbar on the right. I don't want to give up the record count though. :-) For example at around 6:50 your subform shows 3 records. I'd still like to see 1 of 3 but hide or disable the left right arrows.

[@lfriedrichsen]

A form's Navigation Buttons property (Format tab) shows the navigation bar (Yes) or hides it (No), but as you probably already know, changing that property value to No affects the entire navigation bar. I don't believe there is a property that disables only the left/right arrows on the navigation bar. But note that the navigation bar not only provides left/right arrows, it also has buttons for "First record," "Last record," and "New (blank) record" which are probably faster and more precise than using the scroll bar to move to these locations. In addition, the navigation bar provides a button to apply/remove a filter as well as a box to search for specific data.

[@jim27105]

@@lfriedrichsen thanks I got around it by using Dcount in a text box. I appreciate the reply.

[@Supernyv]

Nice! Thank you very much

[@lfriedrichsen]

You're welcome! All of my KZbins are organized at learncs.w3spaces.com. Take care, Lisa

[@bevansmith9108]

Hi Lisa, I find your tutorials very helpful. Regarding subforms, the Form Wizard doesn't create Combos. I have Students in the main form as Combo box. If I change a text field (Classes) on the subform to a combo box it breaks the connection and I don't seem to be able to get it back. Any ideas appreciated.

[@lfriedrichsen]

First off, the Form Wizard WILL create a combo box for a field that has Lookup properties in Table Design View. It sounds like you have a relationship problem between your Students and Classes table. Typically, that takes an intermediate Enrollment table given one student can take many classes and one class can have many students. If that's the case, you'd need to add foreign key fields to the Enrollment table to connect to both the Students and Classes tables. Once your relationships are correct, the Form Wizard will create solid forms and subforms.

[@bevansmith9108]

@@lfriedrichsen Thank you so much for replying - amazing! It took me a long time but I worked out having enrollment table to resolve the Many to Many. On Student table have StudentID and Student Name fields (plus others). On Enrolment table have EnrolmentID and StudentID and ClassID which is a foreign field from Class table. Which table fields need to be Lookup that your refer to. This might be a good video for you to produce as I am sure others battle this one.

[@lfriedrichsen]

Good job. Another thought -- if your classes are repeated, you may also need a Sections table between the Classes and Enrollments tables. One class can have many sections and one section can have many enrollments.

[@lfriedrichsen]

Once the tables and relationships are properly designed, then the Form Wizard will create proper forms/subforms to show the data that you select in the wizard.

[@lfriedrichsen]

As far as Lookup fields, those are always the foreign key fields. So the StudentID field in the Enrollments table would be a good candidate for Lookup properties. I do already have many KZbins on these subjects. Please see all of my KZbins organized at... learncs.w3spaces.com

[@sharmaneaurora6332]

Hi, can you please show me a code that will set focus on the first control in the subform when you tab out of the last control in the main form without clearing the data entered on the main form? I tried to set focus on the subcontrol in lastmainfform_lost focus event but it won't work☹️

[@lfriedrichsen]

Me.Parent!MainFormName.SetFocus

[@alializadeh8195]

Thanks

[@lfriedrichsen]

You're welcome! All of my KZbins are organized at learncs.w3spaces.com. Take care, Lisa

[@joselopes6698]

Amazing!...

[@lfriedrichsen]

You're welcome! You can find all of my KZbins organized at learncs.w3spaces.com/

[@helenarozane3653]

es jus updatoshu vai es dabusu sekmigi programmesana #pray for me