by Dr. Peter Wayne
| Paradox® calls them Multirecord Objects and Access® calls them Continuous Forms. Whatever you call them, they are a useful alternative to browse view, and you can imitate them in Alpha Five. |
Alpha Five's Report Editor has an option, Repeating form, that lets you display multiple records from the same table in form style, as opposed to browse or tabular style. But in the Form Designer Alpha Five limits you to either showing a single record in form view or multiple records in browse view. There is no option to display multiple records in form style.
There are times when browse view just doesn't seem proper, as when the browse is too wide by far to display properly on the screen. One way around this limitation is to create a narrow browse to identify the records, and then a form or subform to display the detail of the record. That is the approach I have generally used, until I decided to tackle the issue of multirecord forms in Alpha Five.
Indeed, what I was able to create is a form that looks like this:
Figure 1. A multirecord form.
Figure 1 is based on a one-many set of invoice header==>invoice_items. For each invoice, 3 items can be displayed at a time. The active or editing record is the middle record; the other 2, which represent the preceding and the subsequent items, are for display only. If I press the PgDn key to move to the next record, the active record in position 2 will move to position 1, the record in 3 will move to 2, and the next record in the invoice_items table will move to position 3:
Figure 2. The result of scrolling down while the focus is on the child tables.
You can scroll back and forth in the multirecord form just as you can in a browse.
Before setting up this form, I created a new set in the Invoice.adb that comes in your Samples subfolder with Alpha Five. This set, invoice2.set, is simply a one to many link from invoice_header to invoice_items:
Figure 3. The invoice2.set
The principles I am illustrating here can also be illustrated on the invoice.set that comes with the sample Invoice application, as well as on a form based on a single table. There is nothing about what we are going to discuss that is specific to this set design.
Then I created a subform to hold the child table records. The subform has 3 parts to it: a central portion which has fields from the table and 2 other frames that contain variables which will be filled with the values from the preceding and succeeding records. Each of the sections is exactly 1.000 inch tall, so the entire subform is exactly 3.000 inches in height:
Figure 4. There are 3 sections to this subform.
All the variables on this subform are layout-level variables, and their types correspond to the matching types of the fields in invoice_items.dbf:
Figure 5. All the variables are layout-level variables.
I saved the subform as "Items Subform." Next, I created a form for the set that contained a few fields from the parent table, along with space for the subform:
Figure 4. The form design for the set.
I adjusted the size of the subform to be exactly 5 inches with by 3 inches tall. I made the form 3 inches tall because that's the size that would fit on my notebook computer's screen! If I have a 3 inch tall subform, then I can squeeze 3 records into the subform if each record occupies only 1 inch of vertical space.
We'll need to fill in the fields in the "preceding" and "succeeding" frames each time we move from one record to another. Moving from one record to another sounds suspiciously like a form's OnFetch event. Each time we get a new record, we have to seek for the previous record, get its values and plug them into the first frame, then seek for the following record, get its values and plug them into the third frame. Here is the code:
dim items as p
items=table.get("invoice_items")
if items.mode_get()>0 then
end
end if
on error goto wrong_mode
items.fetch_prev()
on error goto 0
if items.fetch_eof() then
prod_id1.text=""
description1.text=""
price1.text=""
quantity1.text=""
extension1.text=""
key1.text=""
else
prod_id1.text=items.product_id
description1.text=items.description
price1.value=items.price
quantity1.value=items.quantity
extension1.value=items.extension
key1.text=items.key
end if
items.fetch_next() ' back to where we were
items.fetch_next() ' to next record
if items.fetch_eof() then
prod_id2.text=""
description2.text=""
price2.text=""
quantity2.text=""
extension2.text=""
key2.text=""
else
prod_id2.text=items.product_id
description2.text=items.description
price2.value=items.price
quantity2.value=items.quantity
extension2.value=items.extension
key2.text=items.key
items.fetch_prev()
end if
end
wrong_mode:
end
Script 1. The OnFetch event script for the Items Subform
Note that this script is attached to the subform, not the parent form. In this set, the fetching of interest is occurring at the subform level!
Script 1 is fine as far as it goes, but what happens if we add a new invoice_items record? No fetching takes place, so the multirecord object we have created does not get properly resynchronized. A new record triggers the form's OnSave event, however, so we can write a synchronizing script for that event. We have to remember, though, that the OnSave event is triggered by any save, including a change, and we don't need to resynchronize our multirecord object after a simple change. We can set a variable in the form's OnEnter event to let us know whether a new entry has taken place and then resynchronize only when necessary.
Here is the OnEnter event script:dim shared entering as l entering=.t.
Script 2. The OnEnter event script for the Items Subform.
We then need to modify the OnFetch script to set the variable entering to .f. when a record is fetched:
dim shared entering as l
entering=.f.
dim items as p
items=table.get("invoice_items")
if items.mode_get()>0 then
end
end if
on error goto wrong_mode
items.fetch_prev()
on error goto 0
if items.fetch_eof() then
prod_id1.text=""
description1.text=""
price1.text=""
quantity1.text=""
extension1.text=""
key1.text=""
else
prod_id1.text=items.product_id
description1.text=items.description
price1.value=items.price
quantity1.value=items.quantity
extension1.value=items.extension
key1.text=items.key
end if
items.fetch_next() ' back to where we were
items.fetch_next() ' to next record
if items.fetch_eof() then
prod_id2.text=""
description2.text=""
price2.text=""
quantity2.text=""
extension2.text=""
key2.text=""
else
prod_id2.text=items.product_id
description2.text=items.description
price2.value=items.price
quantity2.value=items.quantity
extension2.value=items.extension
key2.text=items.key
items.fetch_prev()
end if
end
wrong_mode:
end
Script 3. The modified OnFetch script.
Finally, we need to add a script to the OnSave event:
dim shared entering as l
if entering=.f. then
' it was a change, not an enter
end
end if
dim items as p
items=table.get("invoice_items")
if items.mode_get()>0 then
end
end if
on error goto wrong_mode
items.fetch_prev()
on error goto 0
if items.fetch_eof() then
prod_id1.text=""
description1.text=""
price1.text=""
quantity1.text=""
extension1.text=""
key1.text=""
else
prod_id1.text=items.product_id
description1.text=items.description
price1.value=items.price
quantity1.value=items.quantity
extension1.value=items.extension
key1.text=items.key
end if
items.fetch_next() ' back to where we were
items.fetch_next() ' to next record
if items.fetch_eof() then
prod_id2.text=""
description2.text=""
price2.text=""
quantity2.text=""
extension2.text=""
key2.text=""
else
prod_id2.text=items.product_id
description2.text=items.description
price2.value=items.price
quantity2.value=items.quantity
extension2.value=items.extension
key2.text=items.key
items.fetch_prev()
end if
extension.activate()
sys_send_keys("{tab}")
end
wrong_mode:
end
Script 4. The OnSave event script for the Items Subform.
Script 4 bears an obvious similarity to Script 3.
You can see how the Multirecord object works by downloading a small database that contains the files and the forms. Feel free to extend and modify these ideas any way you like!
8/30/99
Don't forget, we need your feedback to make this site better!