View Full Version : database search


02-24-2005, 12:44 PM
I have a button on a form that, when pushed, searches my db for a key value based on the current index. Pretty straight forward. If the value I enter is not in my db, ALpha returns with the next up value. What I would really like to do is have Alpha return with a dialoge box or something similar alerting the user that the value they entered was "not found". I know this must be possible but I'm not experienced with xbasic. Can anyone help me with this?

Cheryl Lemire
02-24-2005, 12:54 PM

At the end of my message is a sample script I created which queries a table for a specific account number. If the account number does not exist in the table a message box lets the user know there were no matches.

The part that you would want to add to your code begins with the nrecs = . Hopefully this will give you a starting place.

Good luck

dim tbl as P
dim qry as P
dim nrecs as N

tbl = table.open("t_account")

query.description = "Search"
query.order = "acct_no"
query.filter = "\"" + vcgAcctNo + "\"$ACCT_NO" 'value of vcgAcctNo comes from lookup_acct_no script
query.options = ""
qry = tbl.query_create()

nrecs = qry.records_get()
IF (nrecs = 0) then
ui_msg_box("Null Query","There are no matching records in this search, please try again" ,UI_INFORMATION_SYMBOL)
end if


Finian Lennon
02-25-2005, 10:55 AM
Since you say you are using a "key value" I assume that there is an index on the field on which you are doing the find.

Check out "table".fetch_find() in the help.

If the field is "Id" and the index is on "ID" and you want to see if Id = "000123" exists in the table

idx = t.order("Id")
recnum = t.fetch_find("000123")
if recnum " 0 then ' an exact match to "000123" was found
'do something
else ' # returned was less than zero, no exact match
'do something else
End if

You can also do the find based on table.current() for the current form.

IOW, when an exact match is found, recnum returns a positive number that is the record number.