Oracle Forms 6i: Tabbed Canvas

I am having trouble with tabbed forms.  This is what I want to do.  I have a table with about 60 fields.  I have created a tab control that holds all these fields.  It works great.  However, I cannot seem to figure out a way to insert a combo box outside of the tabbed control that lists the unique information for each record.  This way the user can select a record from the combo box and then the form will display all the fields for that selected record on the tabbed control.

So let's us the Vendor table.  I want a combo box listing the vendor name.  The user picks the vendor name whose data they want to look at.  Once the vendor name has been choosen, then the fields will be filled in on the tabbed control.
LVL 1
DCCoolBreezeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FBIAGENTCommented:
DCCoolBreeze,

You could do the followings,

1. Create a control (non-based-table) block CONTROL_BLK with one control item VENDOR_LIST(type NUMBER), which is the combo box listing all the vendor name.
2. Create a record-group RG_VENDOR_LIST with the following query, assume that table Vendor will have at least two(2) columns vendor_name (VARCHAR2) (name of the vendor) and vendor_id (NUMBER)(unique id of the vendor, also the primary key),

select vendor_name label, vendor_id value
  from vendor
 order by vendor_name

3. Add the following logic in the form-level WHEN-NEW-FORM-INSTANCE

declare
...
   return_value number;
...
begin
   ...
   -- populate the combo box
   return_value := populate_group('rg_vendor_list');
   populate_list('control_blk.vendor_list', 'rg_vendor_list');

   -- set the combo box to the first vendor
   :control_blk.vendor_list := get_group_char_cell('rg_vendor_list.value', 1);

   ...
end;

4. Create the item-level trigger WHEN-LIST-CHANGED for the VENDOR_LIST item in control block CONTROL_BLK with the following logic

declare
   default_where_clause varchar2(2000);
begin
   -- post any changes made by the user to the currently displayed vendor
   -- or prompt the user to save changes made by the user to the currently displayed vendor
   ...

   -- change the default where clause for the data block containing the vendor info (on your tab canvas)
   default_where_clause := 'vendor_id = '||to_char(:control_blk.vendor_id);
   set_block_property('VENDOR_BLK', default_where, default_where_clause);

   -- query the vendor information (on your tab canvas)
   :system.message_level := 5;
   go_block('VENDOR_BLK');
   execute_query;
   :system.message_level := 0;

end;
0
DCCoolBreezeAuthor Commented:
I added the code as requested but when I run the form, I get the following error.  I have tested the query and it returns records...

FRM-30351: No list elements defined for list item.

There are lots of records if I run the query stand-alone
0
DCCoolBreezeAuthor Commented:
OK.  I solved the error problem.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

FBIAGENTCommented:
FRM-30351 is a warning, but you should still be able to run the form.
To correct the problem, you just have to define a dummy element in the control item VENDOR_LIST, by specifying 'x' in the List Elements and 1 in the List Item Value for the first element of the combo box control item VENDOR_LIST

Also correction in the WHEN-LIST-CHANGED trigger

   -- change the default where clause for the data block containing the vendor info (on your tab canvas)
   default_where_clause := 'vendor_id = '||to_char(:control_blk.VENDOR_LIST); <<<< NOT VENDOR_ID
   set_block_property('VENDOR_BLK', default_where, default_where_clause);
 
good luck
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DCCoolBreezeAuthor Commented:
OK.  The final item is how can I cause this combo box to appear with the tabbed control.  I do not want it on the tabbed control but I would like to to show.  Then the user can select records from the combox box and the tabbed fields will be filled...but the combo box will remain.  Fror example:

 Form 1
 |--------------------------------------------------------------------------------------------|
 |                                                 TITLE                                                          |
 |  Vendor: name (combo box here on the form)                                                |
 |                                                                                                                   |
 |  Data for vendor                                                                                           |
 |                                                                                                                   |
 |  tab1  tab2 tab3 ... (tabbed control here)                                                        |
 |                                                                                                                   |
 |                                                                                                                   |
 |--------------------------------------------------------------------------------------------|
0
DCCoolBreezeAuthor Commented:
I got the following error: FRM-41336: Populate_list: invlaid column type for column 2.  I was able to get rid of that error by changing the second column in my SQL statement from NUMBER to charcter using TO_CHAR().
0
FBIAGENTCommented:
Sorry but your request is not clearly understood.
You could have a document canvas MAIN_CANVAS, on which the tab canvas TAB_CANVAS1 will overlay.
Then you can put the combo box on the MAIN_CANVAS; e.g., see rough sketch below

+------------------------------------+
|combo box here                       |
|                                              |
|+-------+                                 |
||         +-------------------------+|
||       (this is the tab canvas)  | |
||                                          | |
|+---------------------------------+ |
+------------------------------------+
0
DCCoolBreezeAuthor Commented:
You do understand what I am trying to do.  Let me try that.  Currently I have a stacked canvas and a tabbed canvas.  The window property primary canvas calls the stacked canvas
0
DCCoolBreezeAuthor Commented:
How do I overlay one canvas with the next??
0
FBIAGENTCommented:
Since the MAIN_CANVAS is the sole document canvas on the form, it will always be shown regardless.
Your tab canvas TAB_CANVAS1 can overlay the MAIN_CANVAS via

show_view('tab_canvas1');  -- place this logic in the WHEN-NEW-FORM-INSTANCE, near the end
0
DCCoolBreezeAuthor Commented:
hmmm.  I tried this and it did not work.  I am new to Oracle Forms so I am sure it is something I do not have setup correctly.  So I have one window defined as a document.  I have one canvas defined as a tab and one canvas defined as a content.  I removed the stacked canvas.  I could not see any place where you define a canvas as document.  When I run the app, I get VENDOR_NAME (name of canvas) but no VENDOR_DATA tab canvas.
0
DCCoolBreezeAuthor Commented:
Alright cool.  I got the tabbed canvas to display on the main canvas.  I still have a few more bugs to work out for example, when I load the form I get a ORA-41084: Error getting group cell and then when I select a vendor name I get ORA-40505: unable to perform query.
0
FBIAGENTCommented:
My mistake when I said the MAIN_CANVAS be defined as document canvas; it should be Content, not Document.
What you may need to specify the  Display X Position and Display Y Position properties of the tab convas to values other than zero and zero.  Make sure that the Display Y Position is below that of the combo box.
0
DCCoolBreezeAuthor Commented:
You were exacly right.  They were overlaying each other. I just fixed the first error (ora-41084).  forgot to correct the statement to point to the field in question...working on the last error
0
FBIAGENTCommented:
Did you make the correction in previous post

Also correction in the WHEN-LIST-CHANGED trigger

   -- change the default where clause for the data block containing the vendor info (on your tab canvas)
   default_where_clause := 'vendor_id = '||to_char(:control_blk.VENDOR_LIST); <<<< NOT VENDOR_ID
   set_block_property('VENDOR_BLK', default_where, default_where_clause);

0
DCCoolBreezeAuthor Commented:
perfect!  oustanding help!  Thanks FBIAGENT!
0
FBIAGENTCommented:
glad it helped.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.