In addition to what Frdric said... If I understand the
purpose of your _~s, it really should be two things, a join
in both tables and a criteria in one table, so:
:ALIAS:Z99A.DBF | caseid | afn | aln |
| _cid ~s | _fn | _ln |
:ALIAS:Z99B.DBF | caseid | bfn |
| _cid | BLANK, changeto _fn | BLANK,
changeto _ln |
Where _cid joins the tables and ~s specified the CaseID
value of interest. (I agree with the alias bit, though I
don't think that should be required, but it's certainly
Also, the keyword to test for an 'empty' field is BLANK. It
may be that the comma between BLANK and changeto isn't
needed - I can never remember, so try it out and if it
doesn't work, remove the comma and see what happens...
(Other possibilities are that the table is locked such that
the query can't execute, or that its file attributes aren't
allowing it to be edited by you - check rights and
Now, on to another question: Do you store _all_ old
addresses? If you do, then I would change this from a query
to some code. Said code would do the following before
allowing you to specify a new address:
1) check the old address table for CaseID
A) if found, check whether the remaining fields are blank
i) if they are blank, fill in the fields
ii) if some are blank, I would display the record to
the user (in a msg box) and ask if this was a valid
record or if they wanted to replace existing values.
a) if they wanted to replace, overwrite the fields
with the old address and then allow the new one
to be entered in the other table.
b) if they didn't want to replace, see step B)
B) if not found, insert a record and fill in the fields,
then allow the new address to be added to the other
Of course, your key in the second table (old addresses)
would have to be something like CaseID _and_
AddressSequenceNo (an integer storing whether this is their
first address, second address, etc. - should be easy enough
to write code for this).
Anywho, those are my thoughts. Let us know if you need more
Get the lead out before you reply
> I'm trying to write a script for an address handling form
> and not having much luck with it. I hope someone out there
> may have already dealt with this problem.
> We have a table of people with case ID #s and their current addresses.
> We also have a table set up to store old addresses as the people
> move to new ones. When someone moves, we put her formerly new
> address into the corresponding fields in the OldAddr table.
> I have a form with Table A (new) as the master, linked to Table B (old)
> via the caseID number. I've tried to set up a button that will
> take the current ID value, and run a query to check if the B fields
> are empty and if so change the B fields to the contents of that
> person's A table fields. I've tried the following method:
> method pushbutton(var eventInfo Event)
> r Report
> q Query
> s String
> s = caseid.value
> q = Query
> TYPE: DBASE
> ANSWER: :PRIV:ANSWER.DBF
> P:\\P9238\\Z99A.DBF | caseid | afn | aln |
> | _~s | _fn | _ln |
> P:\\P9238\\Z99B.DBF | caseid | bfn | bln |
> | _~s | empty, changeto _fn | empty, changeto_ln|
> ; for simplicity, only first & last names are involved here.
> This passes the syntax and compile checks, but when run it gives
> this error:
> "an error was triggered in the executeQBE method
> by an object of Query type
> Operating system network error
> table: p:\\p9238\\z99a.dbf
> file: p:\\p9238\\z99a.dbf"
> Any suggestions on what I'm missing? I don't know where
> to look to fix an OS network error. I'm not well acquainted
> with any OPAL beyond the very basics.
> Thanks very much.
> (Oh, the setup is Win95, Paradox 8, Novell network.)
> Star Trek: SF for people with good intentions
> Babylon 5: SF for people with good attention spans.