Debugging Gets Stuck on Procedure within a PRG 2

[dantheinfoman]

Hi All,

When I get to a certain point in my PRG, (right when it creates an empty cursor) it jumps goes to a procedure at the bottom and when I press F8, it just hangs there at the top PROCEDURE NewHeader1

Anyone know why or how to deal with this so I can debug my program again?

Thanks

Dan

 

[Scott24x7]

What are you expecting F8 to do? And why are you pressing it?

Best Regards,
Scott
ATS, CDCE, CTIA, CTDC

"Everything should be made as simple as possible, and no simpler."

 

[dantheinfoman]

Hi Scott24x7,

When I press F8 to step into (continue debugging through the code), it's actually still going through the code internally, but visually, it's not letting me see anything or which line it's at. It merely places the yellow arrow at the top of the procedure (which I haven't called yet) and hangs there throughout the code.

I don't know why it won't just let me go through the code in it's proper order and debug through.

Thanks

Dan

 

[vgulielmus]

Please allow me a few silly questions.
Sometimes stupid questions points you in the right direction.
- Have set a breakpoint, maybe accidentally ?
- Do you have an instance of Excel hanged, or your Excel document requires some user intervention ?
- Your VFP program wait for a user intervention, maybe an error message or a WAIT message ?
- If you press F6 instead F8, the same behaviour occurs ?

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[dantheinfoman]

Hi vgulielmus,

You're questions are most welcome and I appreciate them! I am trying to mess with Excel, so maybe something with Excel is getting mad at the moment I'm making the cursor 'QUERY'?! The thing is, Excel hasn't been instantiated yet at the point when cursor QUERY is made.

Here's the full code that I've been messing with, in case that helps. I will try F6 instead. No breakpoints except at the beginning of the procedure I have a deliberate SET STEP ON so I can walk through it. No waits or messages.

Here's the code:

**TKBWHRSE.prg to work with 'Monthly Hours by Job/Employee/Day/Paytype' Excel (called from tk_repor)
SET STEP ON
SELECT PY_EMPLO
SET RELATION TO
SET ORDER TO TAG Employee

SELECT SYJOBCST
SET RELATION TO
SET ORDER TO JOB

**ds date
m.ldstart = D_BEGINDATE
m.ldend = D_ENDDATE

SELECT Job, Employee FROM tktimcrd;
	WHERE WORK_DATE>=m.ldStart AND WORK_DATE<=m.ldEnd AND Pay_Cycle=m.Pay_Cycle;
	GROUP BY Job, Employee;
	ORDER BY Job, Employee;
	INTO CURSOR Job_Emp

SELECT Job, Employee, sum(Hours) as Hours, pay_type, work_date ;
	FROM tktimcrd;
	WHERE WORK_DATE>=m.ldStart AND WORK_DATE<=m.ldEnd AND Pay_Cycle=m.Pay_Cycle;
	GROUP BY Job, Employee, work_date, Pay_type;
	ORDER BY Job, Employee, work_date, pay_type;
	INTO CURSOR TCs READWRITE
INDEX ON Job+Employee TAG Job_Emp


**new
*WAIT WINDOW 'creating cursor'
CREATE CURSOR QUERY (JOB C(7), Employee C(15), Hrs_1_1 N(6,2), Type_1_1 C(1),Hrs_1_2 N(6,2), Type_1_2 C(1),Hrs_1_3 N(6,2), Type_1_3 C(1),;
		Hrs_1_4 N(6,2), Type_1_4 C(1),Hrs_2_1 N(6,2), Type_2_1 C(1),Hrs_2_2 N(6,2), Type_2_2 C(1),Hrs_2_3 N(6,2), Type_2_3 C(1),Hrs_2_4 N(6,2),;
		 Type_2_4 C(1),Hrs_3_1 N(6,2), Type_3_1 C(1),Hrs_3_2 N(6,2), Type_3_2 C(1),Hrs_3_3 N(6,2), Type_3_3 C(1),Hrs_3_4 N(6,2), Type_3_4 C(1),;
		 Hrs_4_1 N(6,2), Type_4_1 C(1),Hrs_4_2 N(6,2), Type_4_2 C(1),Hrs_4_3 N(6,2), Type_4_3 C(1),Hrs_4_4 N(6,2), Type_4_4 C(1),Hrs_5_1 N(6,2),;
		  Type_5_1 C(1),Hrs_5_2 N(6,2), Type_5_2 C(1),Hrs_5_3 N(6,2), Type_5_3 C(1),Hrs_5_4 N(6,2), Type_5_4 C(1),Hrs_6_1 N(6,2), Type_6_1 C(1),;
		  Hrs_6_2 N(6,2), Type_6_2 C(1),Hrs_6_3 N(6,2), Type_6_3 C(1),Hrs_6_4 N(6,2), Type_6_4 C(1),Hrs_7_1 N(6,2), Type_7_1 C(1),Hrs_7_2 N(6,2),;
		   Type_7_2 C(1),Hrs_7_3 N(6,2), Type_7_3 C(1),Hrs_7_4 N(6,2), Type_7_4 C(1),Hrs_8_1 N(6,2), Type_8_1 C(1),Hrs_8_2 N(6,2), Type_8_2 C(1),;
		   Hrs_8_3 N(6,2), Type_8_3 C(1),Hrs_8_4 N(6,2), Type_8_4 C(1),Hrs_9_1 N(6,2), Type_9_1 C(1),Hrs_9_2 N(6,2), Type_9_2 C(1),Hrs_9_3 N(6,2),;
		    Type_9_3 C(1),Hrs_9_4 N(6,2), Type_9_4 C(1),Hrs_10_1 N(6,2), Type_10_1 C(1),Hrs_10_2 N(6,2), Type_10_2 C(1),Hrs_10_3 N(6,2),;
		     Type_10_3 C(1),Hrs_10_4 N(6,2), Type_10_4 C(1),Hrs_11_1 N(6,2), Type_11_1 C(1),Hrs_11_2 N(6,2), Type_11_2 C(1),Hrs_11_3 N(6,2),;
		      Type_11_3 C(1),Hrs_11_4 N(6,2), Type_11_4 C(1),Hrs_12_1 N(6,2), Type_12_1 C(1),Hrs_12_2 N(6,2), Type_12_2 C(1),Hrs_12_3 N(6,2),;
		       Type_12_3 C(1),Hrs_12_4 N(6,2), Type_12_4 C(1),Hrs_13_1 N(6,2), Type_13_1 C(1),Hrs_13_2 N(6,2), Type_13_2 C(1),Hrs_13_3 N(6,2),;
		        Type_13_3 C(1),Hrs_13_4 N(6,2), Type_13_4 C(1),Hrs_14_1 N(6,2), Type_14_1 C(1),Hrs_14_2 N(6,2), Type_14_2 C(1),Hrs_14_3 N(6,2),;
		         Type_14_3 C(1),Hrs_14_4 N(6,2), Type_14_4 C(1),Hrs_15_1 N(6,2), Type_15_1 C(1),Hrs_15_2 N(6,2), Type_15_2 C(1),Hrs_15_3 N(6,2),;
		          Type_15_3 C(1),Hrs_15_4 N(6,2), Type_15_4 C(1),Hrs_16_1 N(6,2), Type_16_1 C(1),Hrs_16_2 N(6,2), Type_16_2 C(1),Hrs_16_3 N(6,2),;
		           Type_16_3 C(1),Hrs_16_4 N(6,2), Type_16_4 C(1),Hrs_17_1 N(6,2), Type_17_1 C(1),Hrs_17_2 N(6,2), Type_17_2 C(1),Hrs_17_3 N(6,2),;
		            Type_17_3 C(1),Hrs_17_4 N(6,2), Type_17_4 C(1),Hrs_18_1 N(6,2), Type_18_1 C(1),Hrs_18_2 N(6,2), Type_18_2 C(1),Hrs_18_3 N(6,2),;
		             Type_18_3 C(1),Hrs_18_4 N(6,2), Type_18_4 C(1),Hrs_19_1 N(6,2), Type_19_1 C(1),Hrs_19_2 N(6,2), Type_19_2 C(1),Hrs_19_3 N(6,2),;
		              Type_19_3 C(1),Hrs_19_4 N(6,2), Type_19_4 C(1),Hrs_20_1 N(6,2), Type_20_1 C(1),Hrs_20_2 N(6,2), Type_20_2 C(1),;
		              Hrs_20_3 N(6,2), Type_20_3 C(1),Hrs_20_4 N(6,2), Type_20_4 C(1),Hrs_21_1 N(6,2), Type_21_1 C(1),Hrs_21_2 N(6,2),;
		               Type_21_2 C(1),Hrs_21_3 N(6,2), Type_21_3 C(1),Hrs_21_4 N(6,2), Type_21_4 C(1),Hrs_22_1 N(6,2), Type_22_1 C(1),;
		               Hrs_22_2 N(6,2), Type_22_2 C(1),Hrs_22_3 N(6,2), Type_22_3 C(1),Hrs_22_4 N(6,2), Type_22_4 C(1),Hrs_23_1 N(6,2),;
		                Type_23_1 C(1),Hrs_23_2 N(6,2), Type_23_2 C(1),Hrs_23_3 N(6,2), Type_23_3 C(1),Hrs_23_4 N(6,2), Type_23_4 C(1),;
		                Hrs_24_1 N(6,2), Type_24_1 C(1),Hrs_24_2 N(6,2), Type_24_2 C(1),Hrs_24_3 N(6,2), Type_24_3 C(1),Hrs_24_4 N(6,2),;
		                 Type_24_4 C(1),Hrs_25_1 N(6,2), Type_25_1 C(1),Hrs_25_2 N(6,2), Type_25_2 C(1),Hrs_25_3 N(6,2), Type_25_3 C(1),;
		                 Hrs_25_4 N(6,2), Type_25_4 C(1),Hrs_26_1 N(6,2), Type_26_1 C(1),Hrs_26_2 N(6,2), Type_26_2 C(1),Hrs_26_3 N(6,2),;
		                  Type_26_3 C(1),Hrs_26_4 N(6,2), Type_26_4 C(1),Hrs_27_1 N(6,2), Type_27_1 C(1),Hrs_27_2 N(6,2), Type_27_2 C(1),;
		                  Hrs_27_3 N(6,2), Type_27_3 C(1),Hrs_27_4 N(6,2), Type_27_4 C(1),Hrs_28_1 N(6,2), Type_28_1 C(1),Hrs_28_2 N(6,2),;
		                   Type_28_2 C(1),Hrs_28_3 N(6,2), Type_28_3 C(1),Hrs_28_4 N(6,2), Type_28_4 C(1),Hrs_29_1 N(6,2), Type_29_1 C(1),;
		                   Hrs_29_2 N(6,2), Type_29_2 C(1),Hrs_29_3 N(6,2), Type_29_3 C(1),Hrs_29_4 N(6,2), Type_29_4 C(1),Hrs_30_1 N(6,2),;
		                    Type_30_1 C(1),Hrs_30_2 N(6,2), Type_30_2 C(1),Hrs_30_3 N(6,2), Type_30_3 C(1),Hrs_30_4 N(6,2), Type_30_4 C(1),;
		                    Hrs_31_1 N(6,2), Type_31_1 C(1),Hrs_31_2 N(6,2), Type_31_2 C(1),Hrs_31_3 N(6,2), Type_31_3 C(1),Hrs_31_4 N(6,2),;
		                     Type_31_4 C(1), Hours N(6,2))
		                     ************THE CURSOR ABOVE IS WHERE IT MESSES UP!*************
*WAIT WINDOW 'just created cursor'
INDEX ON Job+Employee TAG Job_Emp

SELECT Job_Emp
SCAN 
	INSERT INTO Query (Job, Employee) VALUES (Job_Emp.job, Job_Emp.employee)
	SELECT TCs
	SCAN FOR TCs.Job = Job_Emp.job AND TCs.employee = Job_Emp.employee
		m.Whc_day = TCs.work_date - m.ldStart + 1
*!*			IF EVALUATE('QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_1') = 0    && first pay type for this date
*!*				m.Whc_Hrs = 'QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_1'
*!*				m.Whc_Type = 'QUERY.Type_'+ALLT(STR(m.Whc_day,2))+'_1'
*!*			ELSE                                                      && already entered 1 pay type for this date
*!*				m.Whc_Hrs = 'QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_2'
*!*				m.Whc_Type = 'QUERY.Type_'+ALLT(STR(m.Whc_day,2))+'_2'
*!*			ENDIF
		DO case
		case EVALUATE('QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_1') = 0    && first pay type for this date
			m.Whc_Hrs = 'QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_1'
			m.Whc_Type = 'QUERY.Type_'+ALLT(STR(m.Whc_day,2))+'_1'
		case EVALUATE('QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_2') = 0                                                      && already entered 1 pay type for this date
			m.Whc_Hrs = 'QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_2'
			m.Whc_Type = 'QUERY.Type_'+ALLT(STR(m.Whc_day,2))+'_2'
		case EVALUATE('QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_3') = 0                                                      && already entered 1 pay type for this date
			m.Whc_Hrs = 'QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_3'
			m.Whc_Type = 'QUERY.Type_'+ALLT(STR(m.Whc_day,2))+'_3'
		case EVALUATE('QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_4') = 0                                                      && already entered 1 pay type for this date
			m.Whc_Hrs = 'QUERY.Hrs_'+ALLT(STR(m.Whc_day,2))+'_4'
			m.Whc_Type = 'QUERY.Type_'+ALLT(STR(m.Whc_day,2))+'_4'
		ENDCASE
		REPLACE &Whc_Hrs WITH TCs.Hours
		REPLACE &Whc_Type WITH ALLTRIM(TCs.pay_type)
	ENDSCAN
	SELECT Job_Emp
ENDSCAN
SELECT Query 
SCAN
*!*		REPLACE Hours WITH Hrs_1_1+Hrs_1_2+Hrs_2_1+Hrs_2_2+Hrs_3_1+Hrs_3_2+Hrs_4_1+Hrs_4_2+Hrs_5_1+Hrs_5_2+Hrs_6_1+Hrs_6_2+;
*!*			Hrs_7_1+Hrs_7_2+Hrs_8_1+Hrs_8_2+Hrs_9_1+Hrs_9_2+Hrs_10_1+Hrs_10_2+Hrs_11_1+Hrs_11_2+Hrs_12_1+Hrs_12_2+;
*!*			Hrs_13_1+Hrs_13_2+Hrs_14_1+Hrs_14_2
		
		
		REPLACE Hours WITH Hrs_1_1+ Hrs_1_2+ Hrs_1_3+ Hrs_1_4+ Hrs_2_1+ Hrs_2_2+ Hrs_2_3+ Hrs_2_4+ Hrs_3_1+ Hrs_3_2+ Hrs_3_3+ Hrs_3_4+ Hrs_4_1+;
		 	Hrs_4_2+ Hrs_4_3+ Hrs_4_4+ Hrs_5_1+ Hrs_5_2+ Hrs_5_3+ Hrs_5_4+ Hrs_6_1+ Hrs_6_2+ Hrs_6_3+ Hrs_6_4+ Hrs_7_1+ Hrs_7_2+ Hrs_7_3+;
		 	 Hrs_7_4+ Hrs_8_1+ Hrs_8_2+ Hrs_8_3+ Hrs_8_4+ Hrs_9_1+ Hrs_9_2+ Hrs_9_3+ Hrs_9_4+ Hrs_10_1+ Hrs_10_2+ Hrs_10_3+ Hrs_10_4+;
		 	  Hrs_11_1+ Hrs_11_2+ Hrs_11_3+ Hrs_11_4+ Hrs_12_1+ Hrs_12_2+ Hrs_12_3+ Hrs_12_4+ Hrs_13_1+ Hrs_13_2+ Hrs_13_3+ Hrs_13_4+;
		 	   Hrs_14_1+ Hrs_14_2+ Hrs_14_3+ Hrs_14_4+ Hrs_15_1+ Hrs_15_2+ Hrs_15_3+ Hrs_15_4+ Hrs_16_1+ Hrs_16_2+ Hrs_16_3+ Hrs_16_4+;
		 	    Hrs_17_1+ Hrs_17_2+ Hrs_17_3+ Hrs_17_4+ Hrs_18_1+ Hrs_18_2+ Hrs_18_3+ Hrs_18_4+ Hrs_19_1+ Hrs_19_2+ Hrs_19_3+ Hrs_19_4+;
		 	     Hrs_20_1+ Hrs_20_2+ Hrs_20_3+ Hrs_20_4+ Hrs_21_1+ Hrs_21_2+ Hrs_21_3+ Hrs_21_4+ Hrs_22_1+ Hrs_22_2+ Hrs_22_3+ Hrs_22_4+;
		 	      Hrs_23_1+ Hrs_23_2+ Hrs_23_3+ Hrs_23_4+ Hrs_24_1+ Hrs_24_2+ Hrs_24_3+ Hrs_24_4+ Hrs_25_1+ Hrs_25_2+ Hrs_25_3+ Hrs_25_4+;
		 	       Hrs_26_1+ Hrs_26_2+ Hrs_26_3+ Hrs_26_4+ Hrs_27_1+ Hrs_27_2+ Hrs_27_3+ Hrs_27_4+ Hrs_28_1+ Hrs_28_2+ Hrs_28_3+ Hrs_28_4+;
		 	        Hrs_29_1+ Hrs_29_2+ Hrs_29_3+ Hrs_29_4+ Hrs_30_1+ Hrs_30_2+ Hrs_30_3+ Hrs_30_4+ Hrs_31_1+ Hrs_31_2+ Hrs_31_3+ Hrs_31_4


ENDSCAN

*BROWSE norm
SET RELATION TO Employee INTO PY_EMPLO ADDI
SET RELATION TO JOB INTO SYJOBCST ADDI

USE IN Job_Emp
USE IN TCs




************EXCEL TIME!!*************
SET STEP ON
#define xlLastCell 11
#define xlMaximized -4137
#define xlRangeAutoformatClassic2 2
#define xlPortrait 1
#define xlCenter -4108

*open excel in background, the program, not a workbook.
oExcel = CreateObject("Excel.Application")
if vartype(oExcel) != "O"
  * could not instantiate Excel object
  * show an error message here
  return .F.
ENDIF


*Get paytype descriptions for KeyCode at headers
myF = IIF(seek('F','pypaytyp'), PYPAYTYP.descriptn,'')
myJ = IIF(seek('J','pypaytyp'), PYPAYTYP.descriptn,'')
my1 = IIF(seek('1','pypaytyp'), PYPAYTYP.descriptn,'')
myW = IIF(seek('W','pypaytyp'), PYPAYTYP.descriptn,'')
myP = IIF(seek('P','pypaytyp'), PYPAYTYP.descriptn,'')
my3 = IIF(seek('3','pypaytyp'), PYPAYTYP.descriptn,'')
myS = IIF(seek('S','pypaytyp'), PYPAYTYP.descriptn,'')
myV = IIF(seek('V','pypaytyp'), PYPAYTYP.descriptn,'')
my4 = IIF(seek('4','pypaytyp'), PYPAYTYP.descriptn,'')
myH = IIF(seek('H','pypaytyp'), PYPAYTYP.descriptn,'')
myT = IIF(seek('T','pypaytyp'), PYPAYTYP.descriptn,'')
my5 = IIF(seek('5','pypaytyp'), PYPAYTYP.descriptn,'')

numDays = m.ldEnd - m.ldStart + 1  &&how many full days in the month?

*create a blank workbook
oWorkbook = oExcel.Application.Workbooks.Add()

*make excel invisible for a bit
oExcel.visible = .f.
y = 1 &&current row in excel
lastJob=''
lastEmp = ''

SELECT query
SCAN
	curJob = query.job
	curEmp = query.employee
	IF curJob = lastJob then
		*same job, keep going
		IF curEmp = lastEmp then
			*same emp, keep going
		ELSE
			*new emp, new line
			y=y+1
		endif
	ELSE
		lastJob = curJob
		*different job, page break, new headers
		curLoc = IIF(seek(curJob,'syjobcst'), syjobcst.location,'')
		DO NewHeader1
	endif

	**ds test, add A1 = 'hello', a2 = 'there'
	


	*oExcel.Range("f2").Value = m.c_reportname 
	*oExcel.cells(1,3).Value = "Tset 12312313213"



	
	
ENDSCAN













*make excel visible now
oExcel.visible = .t.


RETURN



**********************************************************
PROCEDURE NewHeader1
*Creates a new header after the pagebreak in Excel
WITH oExcel.range("f"+TRANSFORM(y))
		.Value = m.c_company
		.HorizontalAlignment = xlCenter
		.font.bold = .t.
ENDWITH
WITH oExcel.range("f"+TRANSFORM(y+1))
		.Value = m.c_reportname
		.HorizontalAlignment = xlCenter
		.font.bold = .t.
ENDWITH
oExcel.range("o"+TRANSFORM(y)).Value = "Key Code"
oExcel.range("m"+TRANSFORM(y+1)).Value = "F"
oExcel.range("n"+TRANSFORM(y+1)).Value = myF
oExcel.range("o"+TRANSFORM(y+1)).Value = "J"
oExcel.range("p"+TRANSFORM(y+1)).Value = myJ
oExcel.range("q"+TRANSFORM(y+1)).Value = "1"
oExcel.range("r"+TRANSFORM(y+1)).Value = my1

oExcel.range("m"+TRANSFORM(y+2)).Value = "W"
oExcel.range("n"+TRANSFORM(y+2)).Value = myW
oExcel.range("o"+TRANSFORM(y+2)).Value = "P"
oExcel.range("p"+TRANSFORM(y+2)).Value = myP
oExcel.range("q"+TRANSFORM(y+2)).Value = "3"
oExcel.range("r"+TRANSFORM(y+2)).Value = my3

**DATE RANGE AND JOB**
oExcel.range("a"+TRANSFORM(y+2)).Value = "Date Range Selected:"
oExcel.range("c"+TRANSFORM(y+2)).Value = m.ldStart
oExcel.range("d"+TRANSFORM(y+2)).Value = "to"
oExcel.range("e"+TRANSFORM(y+2)).Value = m.ldEnd
oExcel.range("a"+TRANSFORM(y+4)).Value = "Job:"  &&Job
oExcel.range("b"+TRANSFORM(y+4)).Value = curJob
oExcel.range("a"+TRANSFORM(y+5)).Value = "Location:"  &&Loc
oExcel.range("b"+TRANSFORM(y+5)).Value = curLoc

oExcel.range("m"+TRANSFORM(y+3)).Value = "S"
oExcel.range("n"+TRANSFORM(y+3)).Value = myS
oExcel.range("o"+TRANSFORM(y+3)).Value = "V"
oExcel.range("p"+TRANSFORM(y+3)).Value = myV
oExcel.range("q"+TRANSFORM(y+3)).Value = "4"
oExcel.range("r"+TRANSFORM(y+3)).Value = my4

oExcel.range("m"+TRANSFORM(y+4)).Value = "H"
oExcel.range("n"+TRANSFORM(y+4)).Value = myH
oExcel.range("o"+TRANSFORM(y+4)).Value = "T"
oExcel.range("p"+TRANSFORM(y+4)).Value = myT
oExcel.range("q"+TRANSFORM(y+4)).Value = "5"
oExcel.range("r"+TRANSFORM(y+4)).Value = my5

oExcel.range("a"+TRANSFORM(y+7)).Value = "File #"
oExcel.range("b"+TRANSFORM(y+7)).Value = "Name"

y=y+6
curDate = m.ldStart
FOR z = 1 TO numDays	
	oExcel.cells(y,z+2).value = left(DTOC(curDate),5)
	oExcel.cells(y+1,z+2).value = left(CDOW(curDate),3)
	curDate = curDate+1
ENDFOR


ENDPROC
**********************************************************

I'm wondering if the #DEFINE stuff should be at the very top though. Would that mess things up?!

Thanks

Dan

 

[vgulielmus]

I believe the program only apparently hanged up.
You're in a Scan loop, and the procedure is called many times.
I pressed patiently many times the F8 key, and the program finally reached the end.
The same result when I pressed F6 instead of F8.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[vgulielmus]

The second problem : you have two SET STEP ON.
Comment the second one
************EXCEL TIME!!*************
SET STEP ON && this one

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

After CREATE CURSOR executes the debugger points to PROCEDURE NewHeader1 as the next executing line?
Did I understsand that correctly?

Not that it would certainly help, but have you tried beautify on your code?
And the CREATE CURSOR does not error? Are you defining more than 255 fields? It looks like 32 lines each 8 fields plus 2 more, which would be 258, but I did not count each line, only the first.

I don't see the cursor as good data schema design, even if you create it with the target to transport data into an excel sheet 1:1 in this form.
If you need something wider, why not use an array?

Bye, Olaf.

 

[vgulielmus]

Finally I understood what you're saying, sorry
Very interesting.
Your query has about 4000 characters. I made some test and around 3600th characters begins that peculiar behavior.
I erased the spaces, I reduced the number of rows, with no use.
I can only propose a workaround. Instead of directly execute the query, use an execscript()

TEXT TO lcQuery NOSHOW PRETEXT 1+2+4
CREATE CURSOR QUERY (JOB C(7),Employee C(15),Hrs_1_1 N(6,2),Type_1_1 C(1),Hrs_1_2 N(6,2),Type_1_2 C(1),Hrs_1_3 N(6,2), Type_1_3 C(1) ,;
Hrs_1_4 N(6,2), Type_1_4 C(1),Hrs_2_1 N(6,2), Type_2_1 C(1),Hrs_2_2 N(6,2), Type_2_2 C(1),Hrs_2_3 N(6,2), Type_2_3 C(1),Hrs_2_4 N(6,2),;
 Type_2_4 C(1),Hrs_3_1 N(6,2), Type_3_1 C(1),Hrs_3_2 N(6,2), Type_3_2 C(1),Hrs_3_3 N(6,2), Type_3_3 C(1),Hrs_3_4 N(6,2), Type_3_4 C(1),;
 Hrs_4_1 N(6,2), Type_4_1 C(1),Hrs_4_2 N(6,2), Type_4_2 C(1),Hrs_4_3 N(6,2), Type_4_3 C(1),Hrs_4_4 N(6,2), Type_4_4 C(1),Hrs_5_1 N(6,2),;
Type_5_1 C(1),Hrs_5_2 N(6,2), Type_5_2 C(1),Hrs_5_3 N(6,2), Type_5_3 C(1),Hrs_5_4 N(6,2), Type_5_4 C(1),Hrs_6_1 N(6,2), Type_6_1 C(1),;
Hrs_6_2 N(6,2), Type_6_2 C(1),Hrs_6_3 N(6,2), Type_6_3 C(1),Hrs_6_4 N(6,2), Type_6_4 C(1),Hrs_7_1 N(6,2), Type_7_1 C(1),Hrs_7_2 N(6,2),;
 Type_7_2 C(1),Hrs_7_3 N(6,2), Type_7_3 C(1),Hrs_7_4 N(6,2), Type_7_4 C(1),Hrs_8_1 N(6,2), Type_8_1 C(1),Hrs_8_2 N(6,2), Type_8_2 C(1),;
 Hrs_8_3 N(6,2), Type_8_3 C(1),Hrs_8_4 N(6,2), Type_8_4 C(1),Hrs_9_1 N(6,2), Type_9_1 C(1),Hrs_9_2 N(6,2), Type_9_2 C(1),Hrs_9_3 N(6,2),;
Type_9_3 C(1),Hrs_9_4 N(6,2), Type_9_4 C(1),Hrs_10_1 N(6,2), Type_10_1 C(1),Hrs_10_2 N(6,2), Type_10_2 C(1),Hrs_10_3 N(6,2),;
 Type_10_3 C(1),Hrs_10_4 N(6,2), Type_10_4 C(1),Hrs_11_1 N(6,2), Type_11_1 C(1),Hrs_11_2 N(6,2), Type_11_2 C(1),Hrs_11_3 N(6,2),;
Type_11_3 C(1),Hrs_11_4 N(6,2), Type_11_4 C(1),Hrs_12_1 N(6,2), Type_12_1 C(1),Hrs_12_2 N(6,2), Type_12_2 C(1),Hrs_12_3 N(6,2),;
 Type_12_3 C(1),Hrs_12_4 N(6,2), Type_12_4 C(1),Hrs_13_1 N(6,2), Type_13_1 C(1),Hrs_13_2 N(6,2), Type_13_2 C(1),Hrs_13_3 N(6,2),;
Type_13_3 C(1),Hrs_13_4 N(6,2), Type_13_4 C(1),Hrs_14_1 N(6,2), Type_14_1 C(1),Hrs_14_2 N(6,2), Type_14_2 C(1),Hrs_14_3 N(6,2),;
 Type_14_3 C(1),Hrs_14_4 N(6,2), Type_14_4 C(1),Hrs_15_1 N(6,2), Type_15_1 C(1),Hrs_15_2 N(6,2), Type_15_2 C(1),Hrs_15_3 N(6,2),;
Type_15_3 C(1),Hrs_15_4 N(6,2), Type_15_4 C(1),Hrs_16_1 N(6,2), Type_16_1 C(1),Hrs_16_2 N(6,2), Type_16_2 C(1),Hrs_16_3 N(6,2),;
 Type_16_3 C(1),Hrs_16_4 N(6,2), Type_16_4 C(1),Hrs_17_1 N(6,2), Type_17_1 C(1),Hrs_17_2 N(6,2), Type_17_2 C(1),Hrs_17_3 N(6,2),;
Type_17_3 C(1),Hrs_17_4 N(6,2), Type_17_4 C(1),Hrs_18_1 N(6,2), Type_18_1 C(1),Hrs_18_2 N(6,2), Type_18_2 C(1),Hrs_18_3 N(6,2),;
 Type_18_3 C(1),Hrs_18_4 N(6,2), Type_18_4 C(1),Hrs_19_1 N(6,2), Type_19_1 C(1),Hrs_19_2 N(6,2), Type_19_2 C(1),Hrs_19_3 N(6,2) ,;
Type_19_3 C(1),Hrs_19_4 N(6,2), Type_19_4 C(1),Hrs_20_1 N(6,2), Type_20_1 C(1),Hrs_20_2 N(6,2), Type_20_2 C(1),Hrs_20_3 N(6,2), Type_20_3 C(1),Hrs_20_4 N(6,2), Type_20_4 C(1),Hrs_21_1 N(6,2), Type_21_1 C(1),Hrs_21_2 N(6,2),;
 Type_21_2 C(1),Hrs_21_3 N(6,2), Type_21_3 C(1),Hrs_21_4 N(6,2), Type_21_4 C(1),Hrs_22_1 N(6,2), Type_22_1 C(1),Hrs_22_2 N(6,2), Type_22_2 C(1),Hrs_22_3 N(6,2), Type_22_3 C(1),Hrs_22_4 N(6,2), Type_22_4 C(1),Hrs_23_1 N(6,2),;
Type_23_1 C(1),Hrs_23_2 N(6,2), Type_23_2 C(1),Hrs_23_3 N(6,2), Type_23_3 C(1),Hrs_23_4 N(6,2), Type_23_4 C(1),Hrs_24_1 N(6,2), Type_24_1 C(1),Hrs_24_2 N(6,2), Type_24_2 C(1),Hrs_24_3 N(6,2), Type_24_3 C(1),Hrs_24_4 N(6,2),;
 Type_24_4 C(1),Hrs_25_1 N(6,2), Type_25_1 C(1),Hrs_25_2 N(6,2), Type_25_2 C(1),Hrs_25_3 N(6,2), Type_25_3 C(1),Hrs_25_4 N(6,2), Type_25_4 C(1),Hrs_26_1 N(6,2), Type_26_1 C(1),Hrs_26_2 N(6,2), Type_26_2 C(1),Hrs_26_3 N(6,2),;
Type_26_3 C(1),Hrs_26_4 N(6,2), Type_26_4 C(1),Hrs_27_1 N(6,2), Type_27_1 C(1),Hrs_27_2 N(6,2), Type_27_2 C(1),Hrs_27_3 N(6,2), Type_27_3 C(1),Hrs_27_4 N(6,2), Type_27_4 C(1),Hrs_28_1 N(6,2), Type_28_1 C(1),Hrs_28_2 N(6,2),;
 Type_28_2 C(1),Hrs_28_3 N(6,2), Type_28_3 C(1),Hrs_28_4 N(6,2), Type_28_4 C(1),Hrs_29_1 N(6,2) , Type_29_1 C(1),;
 Hrs_29_2 N(6,2), Type_29_2 C(1),Hrs_29_3 N(6,2), Type_29_3 C(1),Hrs_29_4 N(6,2), Type_29_4 C(1),Hrs_30_1 N(6,2),;
Type_30_1 C(1),Hrs_30_2 N(6,2), Type_30_2 C(1),Hrs_30_3 N(6,2), Type_30_3 C(1),Hrs_30_4 N(6,2), Type_30_4 C(1),;
Hrs_31_1 N(6,2), Type_31_1 C(1),Hrs_31_2 N(6,2), Type_31_2 C(1),Hrs_31_3 N(6,2), Type_31_3 C(1),Hrs_31_4 N(6,2),Type_31_4 C(1), Hours N(6,2))
ENDTEXT
EXECSCRIPT(lcQuery)

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[dantheinfoman]

GREAT SCOTT! Thanks vgulielmus, the execscript method works!! I can debug again!
Thanks all for such beautiful insight prior to this discovery.

Olaf, I know you're right about arrays, I was just expanding from one of my predecessor's smaller reports and integrating it with excel.

Thanks!!!!!!!

Dan

 

[Dan Freeman]

And I'll be the schoolmarm and point out that QUERY is a reserved word in FoxPro and probably not a good name to use in a production app.

One other thing you might do to make this code a little more readable, particularly if you're going the route of dynamically generating code to feed to Execscript().

Create Cursor QUERY (JOB C(7),Employee C(15))
For lnCount = 1 to 31
   For lnCount2 = 1 to 3
       lcField1 = 'Type_' + Transform(lnCount) + "_" + Transform(lnCount2)
       lcField2 = 'Hrs_' + Transform(lnCount) + "_" + Transform(lnCount2)
       Alter Table QUERY ;
          Add Column (lcField1) C(1) ;
          Add Column (lcField2) N(6,2)
   Endfor
Endfor

This takes ExecScript() out of the picture, but lands in the same place.

Alter the variable names to anything that's meaningful to you, but if you're doing something repeatedly don't TYPE it. That's what computers are good at.

I actually did something like this when revising some legacy code where the cursor structure changes every time the code runs. It's surprisingly quick on an empty in-memory cursor and it's way more maintainable than always doctoring an out of control CREATE statement.

 

[Olaf Doschke]

Dan, I think a better approach is just adding to the CREATE CURSOR statement and finally executing it. While it's an empty cursor you alter 31 times, you write out a new DBF as many times. I use such a composing of a create statement in a form showing data transposed to columns. If you wouldn't need full control about the field names you could also use the cross tab wizard. And of course you could also store a cursor once created empty via COPY TO and then when needing it again SELECT * FROM TABLE WHERE .F.

And yes, query is a reseved word, it's an alias name of the result cursor of sql queries you do without specifying a result cursor name in native SQL without INTO clause. SQLExec does it somewhat similar with the default alias of SQLResult. So it's not a big thing to use this or that word as cursor name. For sake of naming things self-explanatory the cursor name could be better, but you don't easily change inherited code globally without causing side effects.

Bye, Olaf.

 

[NasibKalsi]

And you can continue using Danfreeman's logic for calculating TotalHours

lnTotalHours = 0
For lnCount = 1 to 31
   For lnCount2 = 1 to 4
       lcField2 = 'Hrs_' + Transform(lnCount) + "_" + Transform(lnCount2)
       lnTotalHours = lnTotalHours + (lcField2)
   Endfor
Endfor 

replace HOURS with lnTotalHours

 

[Dan Freeman]

Olaf, I've done the string building too. It all depends on the app's requirements.

I do tend to steer clear of genxtab, though, unless it's CLEARLY a small data set and an unchanging set of requirements. The very process where I built my cursor as described above used to call genxtab FOUR TIMES. Overall, the process took about four hours when it completed -- the code was pretty fragile and broke often.

Now it takes two minutes and it's rock solid.

Since the process does some calculations inside the loops, as nasib correctly surmised, I'm unlikely to revisit because a) it's already a major improvement, b) it only runs once per month, and c) it's stable. There's other stuff to be done around here.

 

[dantheinfoman]

You guys are amazing!

Note: My predecessors use QUERY, hard-coded into every report SCX that processes reports into the report writer. It's worked forever. Thanks, Olaf for that validation. Again, not my choice, but I can't change it now or they will murder me.

Thanks again for your wonderful comments!!

Dan