Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

URGENT HELP, Grid loses Click Events and format 2

Status
Not open for further replies.
lashwarj

lashwarj

IS-IT--Management
Nov 1, 2000
1,067
US
Below is my code, what I have it doing it taking a grid that has no bound control sources and after it runs the code it sets it. It works as long as you return values, but if you end up getting 0 in the temptable and the message box is prompt it no longer allows you to click and set the value.
I have a field on the form, in the click event of the columns i have it setting that value to = the value of column 4, runs great you can run it as many times as you want as long as you return values, after the first empty temptable is generated, all values appear correctly, but you can no longer have the click event of the columns set the value.



IF Name_Search = 1 AND Approval_Search = 0 AND Tax_Map_Search = 0
Lookup_Last_Name = UPPER(ALLTRIM(Thisform.Requested_Last_Name.Value))
Lookup_First_Name = UPPER(ALLTRIM(Thisform.Requested_First_Name.Value))
LookUp_Full_Name = UPPER(Lookup_First_Name + ' ' + Lookup_Last_Name)
SELECT na_names
REQUERY('na_names')
ENDIF
IF EMPTY(LookUp_Last_Name) AND EMPTY(LookUp_First_Name)
MESSAGEBOX('Please enter a name to search for',16,'Notice')
ELSE
IF !EMPTY(Lookup_Last_Name) AND !EMPTY(LookUp_First_Name)
SELECT pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
FROM na_names INNER JOIN pc_owner ;
ON na_names.na_id = pc_owner.na_id ;
INNER JOIN pc_parcel ;
ON pc_parcel.p_id = pc_owner.p_id ;
INNER JOIN pc_address ;
ON pc_parcel.p_id = pc_address.p_id ;
WHERE UPPER(na_names.free_line_1) = PADR(LookUp_Full_Name,50) AND UPPER(pc_owner.status) = 'O';
INTO CURSOR temptable ORDER BY pc_parcel.prior_pc ASC
Thisform.select_button.Visible = .T.
ELSE
IF !EMPTY(Lookup_Last_Name) AND EMPTY(LookUp_First_Name)
SELECT pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
FROM na_names INNER JOIN pc_owner ;
ON na_names.na_id = pc_owner.na_id ;
INNER JOIN pc_parcel ;
ON pc_parcel.p_id = pc_owner.p_id ;
INNER JOIN pc_address ;
ON pc_parcel.p_id = pc_address.p_id ;
WHERE UPPER(na_names.last_name) = PADR(LookUp_last_Name,50) AND UPPER(pc_owner.status) = 'O' ;
INTO CURSOR temptable ORDER BY pc_parcel.prior_pc ASC
Thisform.select_button.Visible = .T.
ENDIF
ENDIF
ENDIF
IF Name_Search = 1 AND Approval_Search = 0 AND Tax_Map_Search = 0
IF !EMPTY(Lookup_Last_Name) OR !EMPTY(Lookup_First_Name )
Thisform.result_grid.RecordSource = 'temptable'
Thisform.result_grid.column1.ControlSource = 'temptable.prior_pc'
Thisform.result_grid.column1.Alignment = 3
Thisform.result_grid.column1.FontSize = 8
Thisform.result_grid.column1.FontBold = .F.
Thisform.result_grid.column1.ForeColor =0
Thisform.result_grid.column1.Width = 105
Thisform.result_grid.column1.header1.Caption = 'Tax Map #'

Thisform.result_grid.column2.ControlSource = 'temptable.formated_address'
Thisform.result_grid.column2.Alignment = 3
Thisform.result_grid.column2.FontSize = 8
Thisform.result_grid.column2.FontBold = .F.
Thisform.result_grid.column2.ForeColor =0
Thisform.result_grid.column2.Width = 145
Thisform.result_grid.column2.header1.Caption = 'Location'

Thisform.result_grid.column3.ControlSource = 'temptable.free_line_1'
Thisform.result_grid.column3.Alignment = 3
Thisform.result_grid.column3.FontSize = 8
Thisform.result_grid.column3.FontBold = .F.
Thisform.result_grid.column3.ForeColor =0
Thisform.result_grid.column3.Width = 170
Thisform.result_grid.column3.header1.Caption = 'Owner'

Thisform.result_grid.column4.ControlSource = 'temptable.p_id'
Thisform.result_grid.column4.Alignment = 3
Thisform.result_grid.column4.FontSize = 8
Thisform.result_grid.column4.FontBold = .F.
Thisform.result_grid.column4.ForeColor =0
Thisform.result_grid.column4.Width = 1
Thisform.result_grid.column4.header1.Caption = ''

Thisform.result_Grid.Refresh()
Thisform.Refresh()
counter = RECCOUNT('temptable')
IF counter > 0
Thisform.Height = 400
ELSE
MESSAGEBOX(Lookup_Full_Name + ' is not in the system',16,'Notice')
ENDIF
ENDIF
ENDIF
 
Sort by date Sort by votes
Try not setting the control source for the grid, until the dataset returns a result:

Code: 
IF Name_Search = 1 AND Approval_Search = 0 AND Tax_Map_Search = 0
    Lookup_Last_Name = UPPER(ALLTRIM(Thisform.Requested_Last_Name.Value))
    Lookup_First_Name = UPPER(ALLTRIM(Thisform.Requested_First_Name.Value))
    LookUp_Full_Name = UPPER(Lookup_First_Name + ' ' + Lookup_Last_Name)
    SELECT na_names
    REQUERY('na_names')
ENDIF
IF EMPTY(LookUp_Last_Name) AND EMPTY(LookUp_First_Name)
        MESSAGEBOX('Please enter a name to search for',16,'Notice')
    ELSE
    IF !EMPTY(Lookup_Last_Name) AND !EMPTY(LookUp_First_Name)
        SELECT pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
        FROM na_names INNER JOIN pc_owner ;
        ON na_names.na_id = pc_owner.na_id ;
        INNER JOIN pc_parcel ;
        ON pc_parcel.p_id = pc_owner.p_id ;
        INNER JOIN pc_address ;
        ON pc_parcel.p_id = pc_address.p_id ;
        WHERE UPPER(na_names.free_line_1) = PADR(LookUp_Full_Name,50) AND UPPER(pc_owner.status) = 'O';
        INTO CURSOR temptable ORDER BY pc_parcel.prior_pc ASC
        Thisform.select_button.Visible = .T. 
    ELSE
    IF !EMPTY(Lookup_Last_Name) AND EMPTY(LookUp_First_Name)
        SELECT pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
        FROM na_names INNER JOIN pc_owner ;
        ON na_names.na_id = pc_owner.na_id ;
        INNER JOIN pc_parcel ;
        ON pc_parcel.p_id = pc_owner.p_id ;
        INNER JOIN pc_address ;
        ON pc_parcel.p_id = pc_address.p_id ;
        WHERE UPPER(na_names.last_name) = PADR(LookUp_last_Name,50) AND UPPER(pc_owner.status) = 'O' ;
        INTO CURSOR temptable ORDER BY pc_parcel.prior_pc ASC
        Thisform.select_button.Visible = .T. 
ENDIF
ENDIF
ENDIF
IF Name_Search = 1 AND Approval_Search = 0 AND Tax_Map_Search = 0
IF !EMPTY(Lookup_Last_Name) OR !EMPTY(Lookup_First_Name )
  SELECT TEMPTABLE
  GO TOP
  IF !EOF()
    Thisform.result_grid.RecordSource = 'temptable'
    Thisform.result_grid.column1.ControlSource = 'temptable.prior_pc'
    Thisform.result_grid.column1.Alignment = 3 
    Thisform.result_grid.column1.FontSize = 8
    Thisform.result_grid.column1.FontBold = .F. 
    Thisform.result_grid.column1.ForeColor =0
    Thisform.result_grid.column1.Width = 105
    Thisform.result_grid.column1.header1.Caption = 'Tax Map #'

    Thisform.result_grid.column2.ControlSource = 'temptable.formated_address'
    Thisform.result_grid.column2.Alignment = 3 
    Thisform.result_grid.column2.FontSize = 8
    Thisform.result_grid.column2.FontBold = .F. 
    Thisform.result_grid.column2.ForeColor =0
    Thisform.result_grid.column2.Width = 145
    Thisform.result_grid.column2.header1.Caption = 'Location'

    Thisform.result_grid.column3.ControlSource = 'temptable.free_line_1'
    Thisform.result_grid.column3.Alignment = 3 
    Thisform.result_grid.column3.FontSize = 8
    Thisform.result_grid.column3.FontBold = .F. 
    Thisform.result_grid.column3.ForeColor =0
    Thisform.result_grid.column3.Width = 170
    Thisform.result_grid.column3.header1.Caption = 'Owner'

    Thisform.result_grid.column4.ControlSource = 'temptable.p_id'
    Thisform.result_grid.column4.Alignment = 3 
    Thisform.result_grid.column4.FontSize = 8
    Thisform.result_grid.column4.FontBold = .F. 
    Thisform.result_grid.column4.ForeColor =0
    Thisform.result_grid.column4.Width = 1
    Thisform.result_grid.column4.header1.Caption = ''

    Thisform.result_Grid.Refresh()
    Thisform.Refresh()
    Thisform.Height = 400
  ELSE
    MESSAGEBOX(Lookup_Full_Name + ' is not in the system',16,'Notice')
  ENDIF
ENDIF
ENDIF
ENDIF

Regards

Griff
Keep [Smile]ing
 
Upvote 0 Downvote
YOU ARE A GOD!!!!!!!!!!!!!!!!!!!! Thank you so much, I have been trying to solve this forever. Thank You Thank You Thank You Thank You Thank You Thank You Thank You Thank You !!!!!!!!!!!!
 
Upvote 0 Downvote
Darn it Darn it Darn it, I totally jumped the gun on that one, still no go, thought it worked but no, sorry but hey it got you a star
 
Upvote 0 Downvote
What happens now....

Could you add a 'reset' button to put the grid back - or perhaps add a blank record to help?





Regards

Griff
Keep [Smile]ing
 
Upvote 0 Downvote
how would i insert a blank record into my cursor with the same field names as the table
 
Upvote 0 Downvote
P.S. Once it happens the only way I can get it to work 100% is to close the application, then go back in.
 
Upvote 0 Downvote
I hate to keep repeating myself, but 99 percent of all grid problems go away if you use a view as the data source.

Create a paramterized view to search on name or whatever and just requery it as appropriate.
 
Upvote 0 Downvote
I'm with you, if the select returns nothing then you have no structure to work from!

Either run it once with criteria that would extract at least one record - then use that structure to create a tempory cursor using 'Create Table'

or (probably better)

Remove the recordsource from the grid, and deactivate it - even hide it if you like - and enable a reset button after the message:

Code: 
IF Name_Search = 1 AND Approval_Search = 0 AND Tax_Map_Search = 0
    Lookup_Last_Name = UPPER(ALLTRIM(Thisform.Requested_Last_Name.Value))
    Lookup_First_Name = UPPER(ALLTRIM(Thisform.Requested_First_Name.Value))
    LookUp_Full_Name = UPPER(Lookup_First_Name + ' ' + Lookup_Last_Name)
    SELECT na_names
    REQUERY('na_names')
ENDIF
IF EMPTY(LookUp_Last_Name) AND EMPTY(LookUp_First_Name)
        MESSAGEBOX('Please enter a name to search for',16,'Notice')
    ELSE
    IF !EMPTY(Lookup_Last_Name) AND !EMPTY(LookUp_First_Name)
        SELECT pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
        FROM na_names INNER JOIN pc_owner ;
        ON na_names.na_id = pc_owner.na_id ;
        INNER JOIN pc_parcel ;
        ON pc_parcel.p_id = pc_owner.p_id ;
        INNER JOIN pc_address ;
        ON pc_parcel.p_id = pc_address.p_id ;
        WHERE UPPER(na_names.free_line_1) = PADR(LookUp_Full_Name,50) AND UPPER(pc_owner.status) = 'O';
        INTO CURSOR temptable ORDER BY pc_parcel.prior_pc ASC
        Thisform.select_button.Visible = .T. 
    ELSE
    IF !EMPTY(Lookup_Last_Name) AND EMPTY(LookUp_First_Name)
        SELECT pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
        FROM na_names INNER JOIN pc_owner ;
        ON na_names.na_id = pc_owner.na_id ;
        INNER JOIN pc_parcel ;
        ON pc_parcel.p_id = pc_owner.p_id ;
        INNER JOIN pc_address ;
        ON pc_parcel.p_id = pc_address.p_id ;
        WHERE UPPER(na_names.last_name) = PADR(LookUp_last_Name,50) AND UPPER(pc_owner.status) = 'O' ;
        INTO CURSOR temptable ORDER BY pc_parcel.prior_pc ASC
        Thisform.select_button.Visible = .T. 
ENDIF
ENDIF
ENDIF
IF Name_Search = 1 AND Approval_Search = 0 AND Tax_Map_Search = 0
IF !EMPTY(Lookup_Last_Name) OR !EMPTY(Lookup_First_Name )
  SELECT TEMPTABLE
  GO TOP
  IF !EOF()
    Thisform.Result_Grid.Visible = .T.
    Thisform.ResetButton.Visible = .F.
    Thisform.result_grid.RecordSource = 'temptable'
    Thisform.result_grid.column1.ControlSource = 'temptable.prior_pc'
    Thisform.result_grid.column1.Alignment = 3 
    Thisform.result_grid.column1.FontSize = 8
    Thisform.result_grid.column1.FontBold = .F. 
    Thisform.result_grid.column1.ForeColor =0
    Thisform.result_grid.column1.Width = 105
    Thisform.result_grid.column1.header1.Caption = 'Tax Map #'

    Thisform.result_grid.column2.ControlSource = 'temptable.formated_address'
    Thisform.result_grid.column2.Alignment = 3 
    Thisform.result_grid.column2.FontSize = 8
    Thisform.result_grid.column2.FontBold = .F. 
    Thisform.result_grid.column2.ForeColor =0
    Thisform.result_grid.column2.Width = 145
    Thisform.result_grid.column2.header1.Caption = 'Location'

    Thisform.result_grid.column3.ControlSource = 'temptable.free_line_1'
    Thisform.result_grid.column3.Alignment = 3 
    Thisform.result_grid.column3.FontSize = 8
    Thisform.result_grid.column3.FontBold = .F. 
    Thisform.result_grid.column3.ForeColor =0
    Thisform.result_grid.column3.Width = 170
    Thisform.result_grid.column3.header1.Caption = 'Owner'

    Thisform.result_grid.column4.ControlSource = 'temptable.p_id'
    Thisform.result_grid.column4.Alignment = 3 
    Thisform.result_grid.column4.FontSize = 8
    Thisform.result_grid.column4.FontBold = .F. 
    Thisform.result_grid.column4.ForeColor =0
    Thisform.result_grid.column4.Width = 1
    Thisform.result_grid.column4.header1.Caption = ''

    Thisform.result_Grid.Refresh()
    Thisform.Refresh()
    Thisform.Height = 400
  ELSE
    Thisform.Result_Grid.RecordSource = ''
    Thisform.Result_Grid.Visible = .f.
    Thisform.ResetButton.Visible = .t.

    MESSAGEBOX(Lookup_Full_Name + ' is not in the system',16,'Notice')
    Thisform.ResetButton.Setfocus
  ENDIF
ENDIF
ENDIF
ENDIF

Regards

Griff
Keep [Smile]ing
 
Upvote 0 Downvote
lashwarj

If you use VFP7 or later, you can add ReadWrite as parameter clause in Select statement
Select * from MyTable into cursor temptable ReadWrite
Then you can use Append Blank if temptable is empty.

Another suggestion is to use 2 temptable. Will that work for you ?
Code: 
If Name_Search = 1 And Approval_Search = 0 And Tax_Map_Search = 0
   Lookup_Last_Name = Upper(Alltrim(Thisform.Requested_Last_Name.Value))
   Lookup_First_Name = Upper(Alltrim(Thisform.Requested_First_Name.Value))
   LookUp_Full_Name = Upper(Lookup_First_Name + ' ' + Lookup_Last_Name)
   Select na_names
   Requery('na_names')
Endif

If Empty(Lookup_Last_Name) And Empty(Lookup_First_Name)
   Messagebox('Please enter a name to search for',16,'Notice')
Else
   If !Empty(Lookup_Last_Name) And !Empty(Lookup_First_Name)
      Select pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
         FROM na_names INNER Join pc_owner ;
         ON na_names.na_id = pc_owner.na_id ;
         INNER Join pc_parcel ;
         ON pc_parcel.p_id = pc_owner.p_id ;
         INNER Join pc_address ;
         ON pc_parcel.p_id = pc_address.p_id ;
         WHERE Upper(na_names.free_line_1) = Padr(LookUp_Full_Name,50) And Upper(pc_owner.Status) = 'O';
         INTO Cursor [b]temp1[/b] Order By pc_parcel.prior_pc Asc
      Thisform.select_button.Visible = .T.
   Else
      If !Empty(Lookup_Last_Name) And Empty(Lookup_First_Name)
         Select pc_parcel.p_id, pc_parcel.prior_pc, pc_address.formated_address, na_names.free_line_1 ;
            FROM na_names INNER Join pc_owner ;
            ON na_names.na_id = pc_owner.na_id ;
            INNER Join pc_parcel ;
            ON pc_parcel.p_id = pc_owner.p_id ;
            INNER Join pc_address ;
            ON pc_parcel.p_id = pc_address.p_id ;
            WHERE Upper(na_names.last_name) = Padr(Lookup_Last_Name,50) And Upper(pc_owner.Status) = 'O' ;
            INTO Cursor [b]temp1[/b] Order By pc_parcel.prior_pc Asc
         Thisform.select_button.Visible = .T.
      Endif
   Endif
Endif

If Name_Search = 1 And Approval_Search = 0 And Tax_Map_Search = 0
   If !Empty(Lookup_Last_Name) Or !Empty(Lookup_First_Name)
      counter = Reccount('Temp1')
      If counter > 0
         Thisform.Height = 400
         Select temp1
         If used('temptable')
            Thisform.result_grid.RecordSource = ''
            Use in temptable
         endif
         Copy to temptable
         Thisform.result_grid.RecordSource = 'temptable'
         Thisform.result_grid.column1.ControlSource = 'temptable.prior_pc'
         Thisform.result_grid.column1.Alignment = 3
         Thisform.result_grid.column1.FontSize = 8
         Thisform.result_grid.column1.FontBold = .F.
         Thisform.result_grid.column1.ForeColor =0
         Thisform.result_grid.column1.Width = 105
         Thisform.result_grid.column1.header1.Caption = 'Tax Map #'

         Thisform.result_grid.column2.ControlSource = 'temptable.formated_address'
         Thisform.result_grid.column2.Alignment = 3
         Thisform.result_grid.column2.FontSize = 8
         Thisform.result_grid.column2.FontBold = .F.
         Thisform.result_grid.column2.ForeColor =0
         Thisform.result_grid.column2.Width = 145
         Thisform.result_grid.column2.header1.Caption = 'Location'

         Thisform.result_grid.column3.ControlSource = 'temptable.free_line_1'
         Thisform.result_grid.column3.Alignment = 3
         Thisform.result_grid.column3.FontSize = 8
         Thisform.result_grid.column3.FontBold = .F.
         Thisform.result_grid.column3.ForeColor =0
         Thisform.result_grid.column3.Width = 170
         Thisform.result_grid.column3.header1.Caption = 'Owner'

         Thisform.result_grid.column4.ControlSource = 'temptable.p_id'
         Thisform.result_grid.column4.Alignment = 3
         Thisform.result_grid.column4.FontSize = 8
         Thisform.result_grid.column4.FontBold = .F.
         Thisform.result_grid.column4.ForeColor =0
         Thisform.result_grid.column4.Width = 1
         Thisform.result_grid.column4.header1.Caption = ''

         Thisform.result_grid.Refresh()
         Thisform.Refresh()
      Else
         Messagebox(LookUp_Full_Name + ' is not in the system',16,'Notice')
      endif
   Endif
Endif


-- AirCon --
 
Upvote 0 Downvote
Hi Lashwarj

Whenever a REQUERY() is issued, the way VFP operates is like it is making a fresh SQL select. That means to say, the cursor is closed and then reopened. Once you know this, then the Grid based on this needs a safe coding..

ThisGrid.RecordSource=''
REQUERY()
IF EOF()
APPEND BLANK
ENDIF
ThisGrid.RecordSource = whatver

If it is SQL, make the cursor READWRITE so that you can append a record.

I am postiong this without reading and understanding the full long code you posted. Sorry.. I am in a hurry.


Now the next question.. down the thread.. how to add the same values..
SCATTER MEMVAR MEMO
INSERT FROM MEMVAR or APPEND BLANK GHATHER MEMVAR MEMO

:)


ramani :)
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 
Upvote 0 Downvote
ok, i have created a remote view , how would i set a filter like this

SELECT * ;
FROM name_search_view ;
WHERE UPPER(name_search_view.last_name) = PADR(LookUp_last_Name,50) AND UPPER(name_search_view.status) = 'O' ;
INTO CURSOR mycursor
 
Upvote 0 Downvote
Are you using something other than VFP dbfs to store the data? If not why did you create a remote view? Use a local view.

Also you seem to have totally missed the point of views. You don't select from a view into a cursor, the view IS your cursor.

There is also no reason for the padr. If you want only exact matches use == rather than =.

What is the code for the view you have created?



 
Upvote 0 Downvote
SELECT * ;
FROM name_search_view ;
WHERE UPPER(name_search_view.last_name) = PADR(LookUp_last_Name,50) AND UPPER(name_search_view.status) = 'O' ;
INTO CURSOR mycursor


It is a remoteview of an odbc connection to my sql server. Wouldnt it just work if i applied a filter to the data on click event
 
Upvote 0 Downvote
Again you are missing the entire point.

If you have a remote view to your sql server you do not want to use the view to pull over the entire table unless it is an extremly small table. You only want to pull over the record the user wants to see.

All you need to do is create the remote iew and add a parameter on name, you can skip the entire upper and pad thing since they don't apply to sql server unless you have it set up as case sensitive with is not the default setting.

Then to query you set the paramerter variable and
requery('myview')

Look up parameterized views in the help file for furhter info.
 
Upvote 0 Downvote
But in my code I hit that table more then one, in the first code i provided, the user have 3 choices to filter the data by, where they pass 1,2 or 3 parameters, so with this i would have to create 3 seperate remote views correct ?
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bs6600
  • Question
Grid - control source being ignored.
Replies
12
Views
537
SoftArt
SoftArt
Bondjames
  • Question
Report format allignment
Replies
7
Views
356
Bondjames
Bondjames
bharons
  • Locked
  • Question
selected value from grid to another grid 2
Replies
4
Views
642
bharons
bharons
Scott24x7
  • Locked
  • Question
Dynamic Color in Grid Issue 1
Replies
13
Views
1K
Scott24x7
Scott24x7
herbstgy
  • Question
FoxCharts help needed 1
Replies
6
Views
425
tomk3
tomk3

Part and Inventory Search

Sponsor

Back
Top