G'day Steve, At 02:37 20/11/02 -0500, you wrote:
I have created a simple application that stores permit data in one table. Other tables exist as look-up tables to fill in repetitive alpha data in the input form using numerical codes. The input form has an autonumber column that generates a sequential id number. On completion of the last field of the form, pressing the "enter" key obviously brings up a new blank form with a incremented id number. Works great, especially when one enters 50 to 100 forms in a single session. The only problem is that if you entered the form by mistake, the autonumber column increments. Even though you exit with "discard row" the autonumber remains incremented as expected. The work around is to remember to exit the last instance of completing the form with "esc" and "save". Not very elegant, but it works.Here is some code I used from a post by Bill Perry.
Now I am going to guess that it is bad form to bring up a new form without asking if you want to enter another form each time. I am sure there is a better way to control autonumber more effectively. And I want to minimize the additional keystrokes needed on a repetitive basis to prevent instances of an unwanted incremented autonumber column. Suggestions?
I later appended some code I have not yet needed to
implement that would use a cache table of numbers.
Like Ben, I have a unique PK col in TranHeader,
HeaderID, populated with the code below as well as
another column that stores the ID for each
transaction type, called, strangely enough,
TranTypeID. It is text 13 characters and can
accommodate all the variations thrown at it to
date, like invoice numbers in reverse date order
with the first three characters of the client name
eg. JFY20021121.
$COMMAND
SetRowID
-- Gets next row number to add row to table and
-- increments the next row number.
-- Called by:
-- inserting rows into tables in NextRowNumber
*( Requires var
vDBTableName
Returns var
vNextID
)
-- NOTE This code adapted from e-mail posting by Bill Perry
-- Created by Tom Grimshaw 26-12-1997
-- Modification history
-- 09-06-1998 Altered code to accept passed table name and reflect change
-- of column names
-- 25-08-1998 Removed to GlobBlok.apx file
--
SET ERROR VARIABLE vError
SET VARIABLE vNextID INTEGER = NULL
WHILE #TIME IS NOT NULL THEN
SELECT NextRowNumb INTO +
vNextID IND vi1 +
FROM NextRowNumber +
WHERE DBTableName = .vDBTableName
UPDATE NextRowNumber SET +
NextRowNumb = (NextRowNumb + 1) +
WHERE DBTableName = .vDBTableName +
AND NextRowNumb = .vNextID
IF vError = 0 THEN
BREAK
ENDIF
ENDWHILE
CLEAR VAR vError
*( Alternative code to use if no missing numbers required
WHILE #PI IS NOT NULL THEN
SELECT MIN(ID) INTO vID FROM cachetable WHERE ID > 0
IF SQLCODE <> 0 OR vID = 0 THEN -- NOTHING IN CACHE
SELECT ID INTO vID FROM tablename -- grab the current value
UPDATE tablename SET ID = (ID+1) WHERE ID = .vID -- attempt to update
IF SQLCODE = 0 THEN -- id in table not changed by another user
SET VAR vID = (.vID + 1) -- update variable to match new value
BREAK -- exit inner while with new value
ENDIF
ELSE -- NUMBERS IN CACHE
UPDATE cachetable SET vID = 0 WHERE ID = .vID
IF SQLCODE = 0 THEN -- No one else got number
DEL ROWS FROM cachetable where ID = 0 -- DROP USED NUMBER
BREAK -- exit outer while with value from cache
ENDIF
ENDIF
ENDW
)
RETURN
Warmest regards,
Tom Grimshaw
coy: Just For You Software
tel: 612 9552 3311
fax: 612 9566 2164
mobile: 0414 675 903
post: PO Box 470 Glebe NSW 2037 Australia
street: 3/66 Wentworth Park Rd Glebe NSW 2037
email: [EMAIL PROTECTED]
web: www.just4usoftware.com.au
"... the control of impulse -- is the first principle of civilization."-- Will Durant,
Pulitzer Prize winning philosopher, writer and historian
the most needed product in the world can be found at
www.thewaytohappiness.org
This email and any files transmitted with it are confidential to the intended recipient and may be privileged. If you have received this email inadvertently or you are not the intended recipient, you may not disseminate, distribute, copy or in any way rely on it. Further, you should notify the sender immediately and delete the email from your computer. Whilst we have taken precautions to alert us to the presence of computer viruses, we cannot guarantee that this email and any files transmitted with it are free from such viruses.
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
