Wednesday, April 2, 2014

Combobox using RowSourceType = 3, How to update content?

Well I use this to answer inside foxite forum earlier but then I just thought maybe some does not know yet how to use a cursor from an SQL SELECT as RowSource of a combobox, or if some do, they are maybe wondering how to update such when there is a change from the source table/cursor, in result keeping them away over its usage on a combobox; so I transferred it over here as part of my simple tutorials.

You see, I prefer to work on cursors on my end as that makes my app works faster, less prone to corruption (possibility of corruption may happen only during transferring of data from cursor to actual table), and easier to maintain.

So I use cursors on grids, listbox, combobox, etc.  However, some may have been wondering that if they use a cursor for a combobox RowSource, how to update said combobox then when there is a change in data from the actual table it is coming from?  Or in other words, how to show the changes back to the combobox when it is already set to run on a cursor that was created earlier than when the change on the actual source table/cursor happened?

The answer to that is a simple .Requery() event.  No additional codes needed, just plain 'ol .Requery().  See the images below:

And here are the sample codes I used on that:

loTest = Createobject("frmCombo")

Define Class frmCombo As Form
      AutoCenter = .T.

      Add Object text1 As TextBox With ;
            top = 50,;
            left = 10,;
            width = 60

      Add Object text2 As TextBox With ;
            top = 50,;
            left = 90,;
            width = 200

      Add Object grid1 As Grid With;
            top = 90,;
            left = 10,;
            width = 300,;
            Height = 100,;
            RecordSource = 'MyTable'

      Add Object command1 As CommandButton With ;
            top = 210,;
            left = 10,;
            Caption = '\<Update Combobox',;
            AutoSize = .T.
      ADD OBJECT label1 as label WITH;
            top = 210,;
            left = 150,;
            Width = 200,;
            Caption = 'Change content of grid and click Update Combobox button',;
            Wordwrap = .T.,;
            Height = 60

        * This time I am using RowSourceType 3 or an SQL SELECT statement.  I am 
          really creating a cursor now out of the source table/cursor
      Add Object combo1 As ComboBox With ;
            RowSourceType= 3,;
            RowSource = 'Select * from MyTable order by 1 into cursor MyCursor NOFILTER',;
            Style = 2,;
            top = 10,;
            left = 10,;
            Width = 200,;
            ColumnCount = 2,;
            ColumnWidths = '30'

      Procedure Load
            * I use cursor here so a physical table won't be created on your
              end. Pretend this is a table instead of cursor
            Create Cursor MyTable (xCurrency c(3), xCountry c(30))
            Insert Into MyTable Values ('PHP','Philippines')
            Insert Into MyTable Values ('PGK','Papua New Guinea')
            Insert Into MyTable Values ('AUD','Australia')
            Go Top

      Procedure combo1.InteractiveChange
            Thisform.text1.Value = MyTable.xCurrency
            Thisform.text2.Value = MyTable.xCountry

      Procedure command1.Click


*********** End

And there you are!  Cheers!

No comments:

Post a Comment