snipett help

[John Burke]

Goodday everybody, I saw a way of checking for duplicate files, on the learingafpha site, which I liked. When I go to enter a name that is already in the system, it shows me the other name(s), which is great. When I choose NO, to not accept the name, del the name from the field, and go to close the form, I get a can't write field line 13, ix=t.query_crate() (variable type mismatch), here is the code used.

dim t as p
CONST crlf = chr(13) + chr(10) ' carriage return/linefeed
dim ix as p
dim recs as n
dim x as c
dim allow as l
dim ans as n
dim cnt as n
t=table.open(table.current().filename_get())
query.filter = "lastname = var-"a_field_value"
query.order=""
query.options=""
ix=t.query_create()
t.fetch_first()
recs=ix.records_get()
x=""
if recs " 0 then
show_recs = iif(recs " 11,recs,10) 'only show 10 duplicates
x = ""
crlf = chr(13)+chr(10)
for cnt = 1 to show_recs
x = x+alltrim(str(cnt))+". "+\
"Record "+alltrim(str(t.recno()))+": "+alltrim(t.lastname)+", "+\
alltrim(t.firstname)+" - "+alltrim(t.street)+", "+\
alltrim(t.city)+" "+alltrim(t.prov)+" "+\
alltrim(t.postal)+crlf
t.fetch_next()
next cnt
end if
ix=t.index_primary_put("")
ix.drop()
t.close()
if recs " 0 then
ans= ui_msg_box("Warning! "+alltrim(str(recs))+" duplicates found",\
x + crlf + "Allow record ?",\
UI_ATTENTION_SYMBOL + UI_YES_NO + UI_SECOND_BUTTON_DEFAULT)
if ans = UI_NO_SELECTED then
cancel()
end if
end if
end

any sugg, tk jb

[martinwcole]

RE: snipett help

looking at the code, it should be in field rules for the table in the canwritefield event. it looks okay to me - plus unless you misspelled the error, there is no code that says ix=t.query_crate()

if that is the case, then there is code somewhere else.
it sounds like you are saying all is well until I close the form, which would indicate code in the close button or form's canexit event.

[martinwcole]

RE: snipett help

I just checked a form where I use more or less the same code. Since this is firing on the lastname field, and since the lastname is usually a required field, on mine I make the lastname a required field, and if I "delete" the entry that would have made a potential duplicate I get a message saying it is required. So whats happening is that you are trying to save a record with a blank lastname field. Usually would either hits the escape key to cancel the change/entry and/or enter a different lastname.

Although it still doesn't make sense that you get the error, unless a_field_value at that point is a null value and is returned as other than a character value, which the query requires.

Try making the lastname and firstname a required field.

[John Burke]

RE: snipett help

Hi Martin, thanks for the comeback, I changed the FN/LN to required, didn't make a diff. To me it looks like the code assums you want to enter a new record, and if it is a duplicate, it shows you the list, but doesn't allow you to escape the field, just put in another name and continue. Can't see where it considers not putting in a record at all. Could I use the UI_yes_no_cancel, leaving the default as NO, then put an action on the cancel. ? tk jb

[martinwcole]

RE: snipett help

if you are storing the code in field rules in field events in canwritefield, try opening the default form for the table/set, and try entering a duplicate, and then entering a different last name and saving the record, and then once just hitting the escape key (maybe twice) to cancel the enter/change - the code should fire at both enter as well as change.

cancel() used in the code below does not cancel the record - it only prevents the user from continuing with that value, i.e., it cancels the field entry.

if all goes well with this test, then the problem is with the form you are using; if it doesn't, then the problem is with your code.


here is code I am using in "customers" in field rules in field events in last_name in canwritefield:

on error goto errors
dim t as p
dim ix as p
dim recs as n
dim x as c
dim allow as l
dim ans as n
dim cnt as n
dim tname as c

'NOTE*****
'var-"tname is a global variable that is set when the user writes the first_name field in field rules(when entering) and/or at the form's canarrive event for the last_name field object(when editing) - so that the variable always reflects the first_name of the current record.

'note*** this code goes back to 4.01 circa 1999
'it could be rewritten for Version 5, but since it works okay I have just left it.

t=table.open("bell customers")
query.filter = "last_name = var-"a_field_value .and. first_name=var-"tname"
query.order=""
query.options=""
ix=t.query_create()
t.fetch_first()
recs=ix.records_get()
x=""
if recs " 0 then
show_recs = iif(recs " 11,recs,10) 'only show 10 duplicates
x = ""
crlf = chr(13)+chr(10)
for cnt = 1 to show_recs
x = x + "Customer ("+alltrim(str(t.customer_number))+") "\
+alltrim(t.first_name)+" "+alltrim(t.last_name)+" - "\
+alltrim(t.address)+crlf ''x becomes a very long field, but the
''crlf forces it to appear as separate lines
t.fetch_next()
next cnt
end if
ix=t.index_primary_put("")
ix.drop()
t.close()
if recs " 0 then
ans= ui_msg_box("Warning! "+alltrim(str(recs))\
+" duplicates found", x + crlf + "Allow record ?",\
UI_ATTENTION_SYMBOL + UI_YES_NO + UI_SECOND_BUTTON_DEFAULT)
if ans = UI_NO_SELECTED then
cancel()
end if
end if
end
errors:
err_msg = error_text_get(error_code_get())
line = error_line_number_get()
script = error_script_get()
ui_msg_box("Error", err_msg+" Error occurred at line "+alltrim(str(line,4,0))+ " in script: "+script)
end