USING SUBFORMS

Subforms add a VERY powerful feature to abcDB.  Every form you create can now contain up to 6 different datasources … the datasource your form is bound to, and 5 others that are referred to as ‘subforms’.  Every control on your form can be ‘bound’ to any of these 6 datasources.

What does this mean?   Well, prior to subforms, you could display data from ONE table at a time.  For example, you could create a form that contained data from your ‘CUSTOMERS’ table. 

But now, with the power of ‘subforms’, you can add information from your ‘Accounts Receivable’ table, your ‘Inventory’ table, etc..!  All on the same form!

So the big question is … HOW DO I DO IT?

Let’s look at a very simple example:

Note:  The following screenshots are taken from the Realtor application

First, let’s start by making a new form in our REALTOR database.  Let’s create this form basing it on the table ‘tblCities’.  When prompted for a name, please use the name SUBFORMS.
So let’s quickly add the following controls to the form.  (2 Labels, and 2 textboxes) Bind the CITY textbox to the CITY field.  Bind the ID textbox to the ID field. Make sure that the textbox bound to the CITY field is given a TAG value of CITY. This will obviously give us a VERY simple form that only displays a city name and it’s associated ID.  Not very fancy! But let’s open the FORM properties, and go to the next step. We’re about to experience the power of ‘subforms’!
Let’s add an SQL statement for our Subform1 property.  Please view the FORM's properties and tap the ‘…’ button beside SUBFORM1 and proceed to the next step.
Now, choose the tblListings table, and then select ALL the fields (ie. The *) When done, tap the SAVE button and proceed to next step.
OK! Now we’ve got a valid SQL statement entered for our SubForm1 datasource. So basically, our SubForm1 will contain ALL the records from our tblListings table.  Not too useful yet … but we’re not done yet!
Ok ... Now, let's add 4 more textboxes and 4 more labels to our form. Notice what we find when we tap the dropdown list in the Bound_Field property of these textboxes ... we have a whole bunch of new ones! Notice that some of them are prefixed with :1: For example, notice the :1:City field.  This indicates that the field is contained in the Subform#1 datasource.  If we had added SQL statements for the other 4 SubForms, we’d have fields prefixed by :2:, :3:, :4:, :5: as well. So let’s bind our 4 new textboxes to the following fields: :1:Address :1:StreetAve :1:City :1:State When you’re done, proceed to the next step.
Ok, now your screen will likely look similar to this. What have we basically got? We’ve got the top two textboxes displaying data from our tblCities table. And the other 4 textboxes will display data from our tblListings table. Let’s save the form and test it out to make sure.
HOORAY!  It works! But it sure isn’t very useful though is it? Notice that when you move from record-to-record using the menu bar, you end up scrolling through the tblCities data.  The textboxes that are bound to the tblListings table do not change. Why? Because the menu-bar controls ONLY affect the form’s MAIN datasource.  They do not affect any of the other 5 subforms. Let’s edit our form again, and we’ll make it possible to ‘browse’ the listings as well. Proceed to next step.
Let’s add two buttons to our form, as well as a textbox.  Give the textbox a TAG value of RECORDNO. Add the following code in the MACRO events of each: PREVIOUS BUTTON MOVEPREV(1) SETVALUE(~RECORDNO~//POSITION(1)) NEXT BUTTON MOVENEXT(1) SETVALUE(~RECORDNO~//POSITION(1)) Can you guess what these two commands do?  They move backward and forward through SubForm#1 which in this case is our tblListings table. Let’s save the form, and test it out…
Cool! It works! Now we can traverse either our tblCities OR our tblListings tables.  Are you starting to imagine all the possibilities? By now this should be enough to get you started with your own powerful ‘subforms’. But perhaps we can add one more layer of complexity to this simple example that will further demonstrate what subforms can do. As it stands, this application really isn’t too useful.  Wouldn’t it be neat though if we could scroll through the tblCIties and then have only those records from tblListings that are found in the currently displayed city. Well, this is very possible.  Move to the next step, and you’ll see how.
Actually, all we need to do to implement this change is to add TWO lines of code!  That’s IT! So, edit your form again, and open your FORM properties. Then, in the ONCURRENT event, add the following two lines of code: SUBFORMREFRESH(1//"SELECT * FROM tblListings WHERE city = '"~city~"'") SETVALUE(~RECORDNO~//POSITION(1)) When you’re done, save your changes, and try running the form again.
EXCELLENT! We can now move through the tblCities table, and view ALL the related records from our tblListings. There’s no doubt about it … subforms are a VERY powerful tool.

NEXT >>>