Friday, December 30, 2011

How, When & Why Grid Reconstructs

This blog is meant to shed some lights why grid reconstruction happens.

Grid is an object in form that can show the records in a table view.  When you do not use .RecordSource property, it will grab whatever is the current active table and use.  When we use RecordSource property, then it will show the records of the source.  While there are four (4) RecordSourceType to choose from, I will focus only on two (2) of those here:

a.  If I am using Safe Select (2 cursors) Approach, then it is .RecordSourceType = 1 - Alias.
b.  If I am using a single cursor approach (the same cursor gets overwritten over and over again), then it is .RecordSourceType = 4 - SQL Statement, for me.

Depending on my need, I switch either with RecordSourceType 1 or 4.

When you use Safe Select (or what I call 2 cursors approach), then you won't have a grid reconstruction because the cursor that is bound to the grid "never" gets destroyed.  Its original records are just ZAPPED away and a new set is APPENDED onto that from cursor#2.  That is why it is called Safe Select.

The reason why I sometimes favor the single cursor approach (letter b above) is because Safe Select, since it involves manipulating records of two cursors, is slower than the single cursor approach.  However, if you are not aware how to manipulate the grid and the cursor using only a single cursor, then a grid reconstruction may occur.

Why does a grid undergo reconstruction anyway? 

I will use single cursor approach for this explanation:

We bound the grid to a cursor/table via its .RecordSourceType and .RecordSource properties.  Here is a sample of single cursor approach, let us say I have this in Form's Init Event:

With This.Grid1 As Grid
      .RecordSourceType = 4
      .RecordSource = "Select * from mytable INTO CURSOR myCursor NOFILTER"

So the grid is now bound to the cursor myCursor.  Any changes to its records will be reflected back to the grid only when you issue .Refresh.  The grid will never reconstruct just because a change in its records occur.  So you can add, edit and delete the contents of a cursor (if it has READWRITE clause) and a simple .Refresh will update the grid with the latest records.

How, when and why then does a grid undergo a reconstruction?

Still working on a single cursor scenario, when you perform another SQL SELECT ...INTO CURSOR < The Same Cursor >, then what happens is the original cursor is  destroyed first before a new one with the same name is built in its place.  During when the original recordsource is destroyed is when the grid gets reconstructed as well because it cannot find anything anymore to bind to that time.  The cursor at that time  is no longer existing.  And after that, the grid automatically disconnects itself to that non-existent cursor, that even after the cursor is rebuilt it won't affect the grid anymore.  Or if it re-detects it later, then it will rebuild itself to show all the fields of that newly created recordsource of the same name.

So if the original cursor gets destroyed first before a new one is built on top of it resulting to grid reconstruction, how then can we avoid that?  

Simple, remove the binding/linking of the grid into cursor first:

Thisform.Grid1.RecordSource = ''   && You release the linking/binding.  It is not bound to anything now

The purpose is so when the cursor gets destroyed and a new one is built on top of the old one, the grid being free of any linking/binding won't be affected by that major change in the cursor.  After the above, you can now perform your new SQL SELECT on the same cursor without any worries.  After the cursor is created, bind it back to the grid (and in some cases issue a grid.refresh).

So if you have a method for updating the values of the grid based on an SQL SELECT with WHERE clause, here is how it should be done:

* Grid update method:
With this.Grid1 as Grid
     .RecordSource = ''  && Remove binding first
     .RecordSourceType = 4
     .RecordSource = "Select * from mytable WHERE < condition > "+;
          "INTO CURSOR myCursor NOFILTER"    && Rebind to new sets

Why do we avoid Grid Reconstruction?

That is because when grid reconstructs itself, it will first destroy the original grid and along with it all the extra objects we created inside as well as some codes within those.  Those will be replaced with fresh default textbox objects that if say you have a checkbox or a datepicker, those will be destroyed too the time the grid reconstructed itself.  And therefore you will be left with a grid that is not based on your original design and intention.

I hope I have cleared the "mystery" over the grid reconstruction and has not confused your more.

1 comment: