Problem in a Grid (New Post as suggested so others can see it )

[titoneon]

Hi,
I just created a new Post cause i think the subject is different, hope you can point me as you always do.
I did this in VFP 5.0
I am having a little issues with the grid displaying the data i want, actually when i typed in my main form text search box and click on the search button this will open another form with a grid to display all the records that match whatever i typed in the textbox and until this point everything is ok but once i select on that record or row with a mouse click to Edit on that grid then it fills up the grid with more records(the pointer does not move from that record wich is fine) that are similar of the one i typed but not exactly the same but the begining characters of the records displayed are close to the one i found, and that makes sense cause the table is ordered by Vendpartno and some of them are similar but not exactly the same and i don't need them to be displayed, what it does not make sense why will display more than the records i asked for and why not just simply the records that just matched exactly the Vendpartno as the one i just inputed into the textbox, i will insert here some code and the properties, any suggestions will be very appreciated to resolve this problem.

In My startup prg file i have "SET MULTILOCK ON" and this form is called via a menu mpr

A form named fvpart_2.scx
in the form activate event i have
thisForm.txtinput.Setfocus() && this where i will type the Vendpartno
thisForm.refresh

in the data environment, i have a free table named potran04.dbf
datasesion = default datasession

in the form load event i have
PUBLIC p_art_2, emp_ty

the textbox property controlsource = p_art_2
the " " " " Gotfocus the code is as below
p_art_2=""
emp_ty=.f.

in the textbox property interactiveChange
PUBLIC nLenp_art_2
IF NOT EMPTY(THIS.VALUE)
emp_ty=.f.
p_art_2 = ALLTRIM(thisform.txtinput.value)
nLenp_art_2= len(ALLTRIM(p_art_2)) && to know how long is the string new 03/08/2013
ENDIF

textbox property LostFocus
if empty(this.value)
emp_ty=.t.
wait window [You need to enter a Part No to search, TRY AGAIN] NOWAIT
thisform.txtinput.value=""
THISFORM.REFRESH
endif

 

[titoneon]

Sorry guys, i don't have the choice to delete the Post and did not paste the whole thing so i am dong it here
Hi,
I did this in VFP 5.0
I am having a little issues with the grid displaying the data i want, actually when i typed in my main form text search box and click on the search button this will open another form with a grid to display all the records that match whatever i typed in the textbox and until this point everything is ok but once i select on that record or row with a mouse click to Edit on that grid then it fills up the grid with more records(the pointer does not move from that record wich is fine) that are similar of the one i typed but not exactly the same but the begining characters of the records displayed are close to the one i found, and that makes sense cause the table is ordered by Vendpartno and some of them are similar but not exactly the same and i don't need them to be displayed, what it does not make sense why will display more than the records i asked for and why not just simply the records that just matched exactly the Vendpartno as the one i just inputed into the textbox, i will insert here some code and the properties, any suggestions will be very appreciated to resolve this problem.

In My startup prg file i have "SET MULTILOCK ON" and this form is called via a menu mpr

A form named fvpart_2.scx
in the form activate event i have
thisForm.txtinput.Setfocus() && this where i will type the Vendpartno
thisForm.refresh

in the data environment, i have a free table named potran04.dbf
datasesion = default datasession

in the form load event i have
PUBLIC p_art_2, emp_ty

the textbox property controlsource = p_art_2
the " " " " Gotfocus the code is as below
p_art_2=""
emp_ty=.f.

in the textbox property interactiveChange
PUBLIC nLenp_art_2
IF NOT EMPTY(THIS.VALUE)
emp_ty=.f.
p_art_2 = ALLTRIM(thisform.txtinput.value)
nLenp_art_2= len(ALLTRIM(p_art_2)) && to know how long is the string new 03/08/2013
ENDIF

textbox property LostFocus
if empty(this.value)
emp_ty=.t.
wait window [You need to enter a Part No to search, TRY AGAIN] NOWAIT
thisform.txtinput.value=""
THISFORM.REFRESH
endif

The command button named "SEARCH"
In the click event the code
public vp
set order to vpartno
set filter to
go top
if emp_ty
thisForm.txtinput.Setfocus()
thisform.refresh
thisform.txtinput.value=""
retur 0
endif
SEEK ALLTRIM(p_art_2)
if found()
store vpartno to vp
vp=alltrim(vp)
rlock()
WAit window [Please wait while searching .....] nowait
SET KEY TO LEFT(ALLTRIM(VP),nLenp_art_2)
do form mgri_2
thisform.refresh
set key to
unlock all
thisform.txtinput.value=""
THISFORM.REFRESH
go top
ELSE
wait window [VPARTNO NO FOUND TRY AGAIN] NOWAIT
thisform.refresh
thisform.txtinput.value=""
GO TOP
ENDIF
THISFORM.REFRESH

An Exit button, in the click Event code
emp_ty=.f.
THISFORM.REFRESH
THISFORM.release
select potran04
set order to vpartno
go top

the second form that has the grid on it is named mgri_2.scx
data env. for the form is the same table POTRAN04.DBF
Datasessinon: default data session
Bufermode= 2 Optimistic

on the Grid1 the RecordSource=POTRAN04
" " " " " " RecordSource Type= Alias
on the Grid1 MouseMove event i have
LPARAMETERS nButton, nShift, nXCoord, nYCoord
PUBLIC rec
store recno() to rec && i did this with the purpose when i move the mouse to that record to record the; recno() to rec, then in case i need to press the Cancel Button , i can issue the tablerevert()

only text1 in column5 and text1 in column6 are Readonly =.f., cause are the only cells i want to edit in this case, the others one i have then as readonly=.t.
in the valid event of column5 text1, i have this code
V_re=.T.
IF this.Value<=DATE()
Messagebox("Date must be more than today's date")
RETURN 0
ENDIF
nAnswer = MESSAGEBOX("IS THIS DATE CORRECT ? Press Yes to Continue or NO to Reenter it " , 4)

DO CASE
CASE nAnswer = 6 && for Yes answer
V_ret=.F.
CASE nAnswer = 7 && for No answer
V_ret=.T.
retu 0
endcase

Ok now the SAVE, CANCEL AND EXIT Commands Button

The Save command is not cause i added a record, it is just to save the information i added into to two specific fields from an existing record i selected

Save command Click code
PUBLIC rec
sele potran04
set order to vpartno
IF RLOCK()
store vpartno to vpart
store recno() to rec
unlock all
goto rec
endif
tableupdate()
THISFORM.REFRESH
wait window [THIS RECORD HAS BEEN UPDATED...] TIMEOUT 4

Cancel Command, just for the case after i typed and before i press the Save i can revert the entries on those two fields/Colums in that grid
Code
goto rec
tablerevert()
THISFORM.REFRESH
wait window [THIS RECORD HAS NOT BEEN UPDATED...] TIMEOUT 4
i am able to go to that record to revert cause i declared a Public rec in the MouseMove event

Exit command Click Code
V_ret=.F.
go top
THISFORM.REFRESH
THISFORM.RELEASE

 

[Olaf Doschke]

SET KEY TO LEFT(ALLTRIM(VP),nLenp_art_2)
do form mgri_2
thisform.refresh
set key to

If the form mgri_2 is not modal you return back to the refresh and SET KEY TO immediately, which means your filter is gone right at the start of the search result display, so all records are shown, you just position on a record found, hen show all near that one.

Bye, Olaf.

 

[titoneon]

Olaf,
So the key here is to make the form mgri_2 modal ? that is the reason why i am getting more records displayed that actually what i need ?
Thanks
Ernesto

 

[Olaf Doschke]

That would solve that problem most probably, ye. But you may not want your result display form modal. Then that would introduce another problem.

Bye, Olaf.

 

[titoneon]

Olaf,
That solved the problem.
why you said will introduce another problem ?
will be great to know about it.
Thanks

 

[Olaf Doschke]

Well, modal mean, you can only operate that form. That's a disadvantage at least, isn't it?
A way to overcome that would be to not SET KEY TO.

That then has another disadvantage, the search form would also be filtered itself.

And to solve all that, you rather would query the result data into a seperate cursor and not use SET KEY or SET FILTER at all.

Bye, Olaf.

 

[titoneon]

Hi Olaf,
I tried to follow the directions you gave me about using a query so, i delete the SET KEY TO and The SET FILTER so then what i did was i put this SQL code into the grid
RecordSource Property: Select vpartno ,purno,vendno, rec_date , confi_by From potran04 Where vpartno = lcValueF order by 1,4
and
RecordSource type:SQL statement

I Want to be able to edit the columns that have the fields rec_date and confi_by, those are readonly =.F. i got then setup in the Grid.text1 for each then as .F. but when i run this grid whith this sql code at run time those columns are not editable, now they are shown as Read only, before when i was using the Table as RecordSource and the alias as RecordSource Type, i did not have that problem, i am trying follow your suggestions cause i know it will be better for me to learn a better approaches than using old command,
What do think the problem could be ?

This was what i have originaly

The command button named "SEARCH"
In the click event the code
public vp
set order to vpartno
set filter to
go top
if emp_ty
thisForm.txtinput.Setfocus()
thisform.refresh
thisform.txtinput.value=""
retur 0
endif
SEEK ALLTRIM(p_art_2)
if found()
store vpartno to vp
vp=alltrim(vp)
rlock()
WAit window [Please wait while searching .....] nowait
SET KEY TO LEFT(ALLTRIM(VP),nLenp_art_2)
do form mgri_2
thisform.refresh
set key to
unlock all
thisform.txtinput.value=""
THISFORM.REFRESH
go top
ELSE
wait window [VPARTNO NO FOUND TRY AGAIN] NOWAIT
thisform.refresh
thisform.txtinput.value=""
GO TOP
ENDIF
THISFORM.REFRESH

Now i just eliminated from there
SET FILTER TO
SET KEY TO LEFT(ALLTRIM(VP),nLenp_art_2)
SET KEY TO

and added this public variable "lcValueF"
lcValueF=LEFT(ALLTRIM(VP),nLenp_art_2)
and then when running the SQL as above vpart_no=lcValueF display everything fast and right in the grid, but i am not able to edit the Columns as i mentioned above.
Thanks a lot in advance
Ernesto

 

[Olaf Doschke]

No,

I did say you rather should query data. But that should not be done with recordsourcetype SQL-Statement. That should be done in code, at the same placxe you had SET KEY TO. But as you want to edit records of the orignal table, than you're far better off with SET FILTER or SET KEY.

Now I rode you into deeper trouble, sorry, but I didn't know about these needed features. I thought your grid form would merely show the result records for purposes of reading and perhaps selecting one or more records.

You can edit data queried INTO CURSOR curRESULTGRID READWRITE, but then that doesn't go back into the original DBF. It is more scalable though and one big advantage is the scrollbar at the grid will reflect the number of result records correctly, while that is not the case with SET FILTER or SET KEY. That's because you still show the table you don't query from it and so RECCOUNT() still is the full count of records of the DBF.

Bye, Olaf.

 

[titoneon]

Hey Olaf,
it is not your fault, it is mine cause most likely i never told you i need to edit those columns, even i said i need to create a edit button to select the record i want to edit, but you know i thou that when you meant to query just to put the SQl into the RecordSource, wich is not bad idea i guess but it won't update the table right ?, so then i should keep the Set key to, of course i did this on VFP 5.0 , what about is i use the same concept of writing this sql into the RecordSource OF THE GRID UNDER VFP 9.0 with the READWRITE, IF i do this can i update the table while editing the grid with the ReadWrite ?

second i wrote this while ago with VFP 5.0 , I guess converting from VFP 5.0 to VFP 9.0 shoud not be a problem ?
THANKS a lot always for your help
Ernesto

 

[Olaf Doschke]

READWRITE clause just makes the result cursor writable, but you write into that cursor, not into the original DBF. A cursor has a copy of the result records, not the original ones. It's not like an updatable view. That's the problem with that. You can also have writable cursors in VFP5, if you USE DBF("cursorname") IN 0 AGAIN ALIAS newalias you can write to newalias. But that also won't write to the DBF. You would need to go as far as creating an updatable view to be able to have the best of both, the query result with a partial copy of records, which are connected back to the DBF file.

Bye, Olaf.