i want to copy a few values from an excel cells into an Edit box, then insert those into a cursor 2

[Ernesto_Paez]

Hi guys,
i already have a txtbox where i type a value an after i press enter, then the keypress event insert each individual value from the txtbox it into a cursor and once is inserted into that cursor, this cursor is the recordsource for grid1, that will show all the values that has been added one by one from the txtbox.

so now actually what i would like to do is, using an Edit box, instead of a txtbox so then i can get from and existing excel file from a column the values from the cells i want and copy those cells values and paste those values to the Edit box, then once in the edit box, press the Enter key and insert then into the cursor "JOBNOS", this cursor is the recordsource for grid1, that should display all the values pasted into the Edit box, i can paste from excel to the Edit box but the keypress event it is not doing it for me, only gets one of the value from the edit box.
here is my code in the Keypress of the Edit1 box

If nKeyCode = 13
	Insert Into jobnos (job_no)Values (Thisform.Edit1.Value)
	thisform.grid1.Refresh 
	this.Value = ''
	Thisform.cmdQuery.Enabled=.T.
	Thisform.cmdReset.Enabled=.T.

Endif

i will get from the excel the job_no values from each cell, then paste them in Edit1 box but this code it is not doing it for me, if worked when i have in instead of the edit box a txtbox.

how can i find the way to insert all the values from the edit box to the cursor "jobnos" after i pressed the Enter key ?
Thanks in advance a lot

 

[Olaf Doschke]

In detail, as the character keypress detects is not yet in the value, it's not inserted into the cursor, and that's fine as you don't want ENTER in your cursor field, but then setting value='' after the keypress your EditBox will have a linefeed, and your cursor is in line2. You need a NoDefault. That would help with the manual entering of values that way. Your reset of the EditBox should be:

If nKeyCode = 13
   Insert Into jobnos (job_no)Values (Thisform.Edit1.Value)
[highlight #EF8989]   Thisform.grid1.Refresh   [/highlight] 
[highlight #FCE94F]   This.Value = ''
   Nodefault      [/highlight]	
   Thisform.cmdQuery.Enabled=.T.
   Thisform.cmdReset.Enabled=.T.
Endif

I'd also not refresh the grid with every new record. SetFocus would be enough, but would also not be okay for the obvious reason you want the focus to stay on the EditBox.

Anyway, this all doesn't help to make it work with pasting from Excel, as that causes no keypress event, not even CTRL+V as a single event. Pasting works different.

The simplest way to split a multiline text is using ALINES(laLines, Thsform.Edit1.Value) and then insert these array elements into the jobnos cursor, or APPEND FROM ARRAY.
The former needs a for loop from 1 to laLines row numbers, the latter needs a 2d array, which ALINES doesn't give, but you can turn a 1d array into a one column 2d array with DIMENSION(laLines,ALEN(laLines),1)

So in short what I would do is adding a data transfer button and in that buttons click move all EditBox lines into records of a cursor this way:

Create Cursor jobnos (iid int autoinc, ijobno int)

ALines(laLines,thisform.Edit1.Value,1+4) && trims and removes empty lines, see ALINES help
Dimension laLines[Alen(laLines),1]
Append From Array laLines Fields ijobno
* Browse && or have that as grid recordsource as before, 
* but then also create the cursor earlier, e.g., in form.load

All this comes with a further note: By pasting from Excel to a VFP EditBox, you do a type conversion, everything becomes text, this can fail with dates, for example. In case of numbers note I manage to append into an ijobno field defined as an integer. VFP does some automatic conversions of, e.g., strings only containing digits into integers, but this isn't always working that graceful. Any text will come over as 0 instead of being skipped.

If in a future case you'd like to paste over dates this way, this will strongly depend on date formats, just to give one typical problematic case. The best way to import Excel data is from an Excel file and ways to do that is most easy using Vilhelm-Ion Praisach's libraries for Excel:

http://praisachion.blogspot.com/2015/06/append-from-xlsx.html

You can use this simple mechanism, but with the strings attached to it to have limited usability because the type conversion to text when pasting data this way is inevitable.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name

 

[Ernesto_Paez]

Hi Olaf,
i added the btntransfer button and it works great(so i copy from a excel sheet just those values that by the way in my cursor is not needed as int or dat just character.
now let say i still want to add one more entry ,manually(after i already transfered) in editbox or just use the Editbox for a single entry, do i need then to add code in the keypress event, or there is way another way for it ? if i did not explain myself correcttly let me know ? Thanks

 

[Olaf Doschke]

Well, to add one more record, empty the Editbox, add that and click the button. Assuming you don't copied my code 1:1 and create the cursor at form load or init and don't recreate it everytime, you append more rows.

Bye, Olaf.

Olaf Doschke Software Engineering

https://www.doschke.name