script on CanRecordDelete event gives error

[acceptthis]

When I run this code through the interactive window:

	site = prospect->site_num             'makes the variable 'site' equal to the site number of the appointment
	jsite = table.open("job_sites")
	query.filter="site_num = var->site"
	jsite.query_create()
	jsite.change_begin()
	jsite.Schd = .f.
	jsite.change_end(.t.)
	jsite.close()

it does just what I want.
But, when I put it in the CanDeleteRecord event, I get this error:

Script:Table (Prospect)CanDeleteRecord line 13:
jsite.query_create()
no such field

Anyone know why?
I assume it's a problem with not recognizing the query.filter variables, but why does it work in the interactive window and not on the CanDeleteRecord event in field rules?


P.S. As an expanation, I have jobsites for which I schedule appointments. When I schedule an appointment (prospect table), the jobsite is marked scheduled (schd = .t.). If I delete the appointment, I want the jobsite to be marked as not scheduled (schd = .f.), so I can re-schedule it another time.

[Tom Cone Jr]

What data type is the site_num field?

-- tom

[Scott Emerick]

Try:

query.filter="site_num = '"+var->site+"'"
or
query.filter="site_num = '"+site+"'"


Expanded view for readability (do not use in code):
' " + var->site + " ' "

[acceptthis]

It's all character type.

[acceptthis]

I tried both of these and neither worked.
maybe this will help. . .
In the interactive window, before the query_create(),

query.filter = "site_num = '"+site+"'"
? query.filter
= "site_num = '0689 '"
after a successful query,
= "site_num = '0689 '"
? jsite.site_num
= "0689"

while,
query.filter = "site_num = site"
? query.filter
= "site_num = site"

after a successful query,
= site_num = "0689 "
? jsite.site_num
= "0689"

Both work, and give me the same result, but when using it in the candeleterecord event, it gives me the "no such field" error.

I ran the debugger, and before a successful query,
query.filter = site_num = '0689 '

when it hits the query create() line, I get the error.

[Tom Cone Jr]

Brad, I'll need to see the database to help on this. What you're reporting in the interactive editor doesn't make sense. ( I believe you, just think there's another explanation. ) You're not showing us the entire script that's running in the CanDeleteRecord event.

-- tom

[Al Buchholz]

Brad

Try using job_sites->site_num instead of site_num.

If there is a set involved, you may need to provide the table name with the field name.

I also find it helpful to open a default browse for the table/set that the query is based on and generate the query until it works the way I want. Then I write the filter using the query generated filter as a guide.

[acceptthis]

I tried job_sites->site_num and the difference is the description of the error. This time, the error reads, "Field is not recognized".
I did try creating the query through "query by expression" and I used it, but it just gave me what I'm working with right now.
I'll have to pack up the tables and attach them to this thread.
Thanks for your help.

[acceptthis]

I've uploaded the database (only problem is I don't know what I'm doing when I backup, so it's much bigger than it needs to be). Sorry.

To produce the problem, Open the appointment form, pick a record, and delete it.

[acceptthis]

I've attached file. Thanks for your interest.

[Tom Cone Jr]

'Date Created: 17-Mar-2006 07:12:47 AM
'Last Updated: 17-Mar-2006 11:33:27 PM
'Created By  : Brad
'Updated By  : TEC

'Brad, a few general comments first.
'
'Your database name contains blank spaces.  This is NOT recommended.
'You have sets with names that are identical to tables.  This is NOT recommended.
'
'I renamed your "Job_Sites" set as "Job_Sites_Set"
'I renamed your "Prospect" set as "Prospect_set"

'I revised your CanDeleteRecord event script for the Prospect table as shown below.
'Here are the highlights:
'   1) I prefer to use tbl pointer to the current instance of the Prospect table
'   2) You should always set ALL query parameters before running a query.  Alpha Five
'          will not re-run a query if the query list already exists.
'   3) I changed the way the filter expression is written.  It's dangerous to
'          include the target table name, since the alias may be different.  Here
'          the Job_sites table is open already, so this script will open another
'          instance and its alias will be "job_sites1", not "job_sites"
'   4) I included an error trap if the query fails
'   5) I blocked the delete if there is no matching record in the Job_sites table.  You may
'          not want to do this.
'   6) Since you're comparing the site_num in Prospect table with site_num field values in
'          the Job_Sites table it's vital that this field have the same characteristics
'          in each table.  I increased the field width for site_num in Job_sites table
'          so that it will match the width of the site_num field in Prospect table.  A
'          trailing space can cause a mismatch sometimes.
'
' Ok, here's the revised script:

tbl = table.current()  'get pointer to Prospect table

' if the appointment was created as a t-on/off then unschedule the jobsite
IF (left(tbl.Type_Of_Work,7)="Premium").or.(left(tbl.Type_Of_Work,7)="Regular")\
	.or.(left(tbl.Type_Of_Work,7)="Blow-ou") THEN
'debug(1)

	site = alltrim(tbl.site_num)	'assign site_num to variable
	'trace.writeln("Selected site_num is: "+site)
	jsite = table.open("job_sites.dbf")	'open new instance of job_sites table
	'query.filter="job_sites->site_num = '"+site+"'"
	
	'set all query parameters
	query.filter="site_num = "+s_quote(site)
	query.options = "MT"
	query.order = ""
	'trace.writeln("query_filter: "+query.filter)

	on error goto cancel_delete
	qry = jsite.query_create()
	on error goto 0
		
	if qry.records_get() > 0 then	'query found a job_sites record with same site_num
		msgbox("Hoorah!","The query ran fine")
		jsite.change_begin()
		jsite.Schd = .f.
		jsite.change_end(.t.)
	else
		msgbox("Oops","Query list is empty")
		cancel()  'cancel the delete, you may prefer to let the delete occur.
	end if
	qry.drop()	'cleanup on the way out
	jsite.close()
	
	xbasic_wait_for_idle()	'let the query and change finish before deleting prospect table record
	end	
end if

cancel_delete:
 jsite.close()	'query failed
 cancel()  		'cancel the delete
 err = error_code_get()
 msg = error_text_get(err)
 ui_msg_box("Error", msg + crlf()+crlf()+"Delete was cancelled")
 end

-- tom

[CALocklin]

I haven't looked at your script or the sample database but I'm wondering if it would work better in the OnDelete rather than the CanDelete event.

The primary purpose of the CanDelete is to decide whether or not the parameters are acceptable for deleting the record - if not, a "Cancel()" will stop the deletion.

The primary purpose of the OnDelete is to do something after a record has been deleted. In this case it may be necessary (but I don't think so) to save the value of the site number in a shared or "temporary" global variable so the OnDelete can use it. (By "temporary" I just mean it could be DELETEd when the OnDelete closes.)

[Tom Cone Jr]

Cal, that's a good point. The CanDeleteRecord event could populate the variable for use when OnDeleteRecord fires. Otherwise, there's a risk the CanDeleteRecord script will modify other table values even when the user elects to cancel a delete.

Here, there were more fundamental problems relating to the query sequence that were causing the problem Brad was seeing.

-- tom

[acceptthis]

Tom,
Thanks for looking into this and all the helpful suggestions. I haven't had time to go through them yet, but I wanted to thank you first.
The two biggest problem with my xbasic programing is I don't always understand sound fundamental basics. Second, I am working on an a5 app that has been played with for about 12-13 years, so there's some old, stupid mistakes still lingering that I haven't fixed.
I'm like the neighbor down the street that keeps making additions to his house with the crap that's he finds laying around. I see some cool stuff and want it, so I get out my hammer and nails and start pounding.
One of these days, I should re-structure it all over again from scratch and just piece in the sound working features. I'd probably learn alot by doing that.

Cal,
I see your point, and that was exactly why I chose the CanDelete event rather than OnDelete. I was trying to avoid referring to a deleted 'site_num'.
Once I get it working, I'll put it in the OnDelete to see if it's getting the right number or not. If it doesn't, I guess the best thing to do is assign the varable in the CanDelete event.

[acceptthis]

Tom, Cal, and all who are following this thread,

The script worked! Thanks so much. I'll try to remember to use a pointer and then declare all options of my query. good lesson!
It also works on the OnDeleteRecord event. It was able to collect the correct data for my query from the deleted record. I will assume that it runs the script just prior to completely deleting the record.