reciept numbers adding +1 to textbox

Creating and using forms

reciept numbers adding +1 to textbox

Postby ijulie » Sat Jan 12, 2008 10:01 pm

Hi, Newbie here
I need a textbox in my form that will add 1 to a number when I add it to a table. I'm working in design view. Thanks.
ijulie
 
Posts: 7
Joined: Wed Jan 09, 2008 4:30 pm

Re: reciept numbers adding +1 to textbox

Postby DrewJensen » Sat Jan 12, 2008 11:26 pm

Do you need something more then auto_increment integer field? If that is sufficient then:

Assuming the table name Table and the field is named RecieptNum one you do this:
Open the SQL window ( TOOLS > SQL )
Enter the command to create a sequence;
Code: Select all   Expand viewCollapse view
CREATE SEQUENCE SEQ_INV_NUM

That starts at 0 and increments by way by defualt

Now enter this command
Code: Select all   Expand viewCollapse view
ALTER TABLE "Table1" ALTER COLUMN "RerceiptNum" DEFAULT NEXT VALUE FOR SEQ_INV_NUM


That is assuming that you have no records and that you want it to start at 0 - otherwise you can look up the create sequence command in the HSQLdb documentation at http://hsqldb.org

When you do this the value will not be set until after the record is written to the table - so when you use 'insert new record' on your form, or in design view, the UI will simply display "<auto>" and when you save the record then the value is set.

Otherwise:

If you need something more, say you want to have a receipt number of type "R2008-0001" where the number cycles for year you would use a CALL statement in a query and execute this in a script, from a form preferably when you insert a new record again.

Anyway, I won't put up a full example unless you need to use it - but it will be in the next example database that I put up on the board for generating invoice numbers.

Here is an example of this type of function:


Then I created a query in SQL direct mode:
Code: Select all   Expand viewCollapse view
CALL  'INV-' ||  TO_CHAR(  CURRENT_DATE, 'YYYYMMDD' )  || '-' || 10000 + NEXT VALUE FOR SEQ_INV_NUM

If I call that just now I get the string:
INV-20080112-10025

That is the string INV-<year><mon><day>-<auto incremented number value>

It would take a few more lines to include things like cycling for new year, etc.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
User avatar
DrewJensen
Volunteer
 
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: reciept numbers adding +1 to textbox

Postby ijulie » Sun Jan 13, 2008 7:14 pm

Wow, Thanks alot you solved my problem. :D
ijulie
 
Posts: 7
Joined: Wed Jan 09, 2008 4:30 pm

Re: reciept numbers adding +1 to textbox (SOLVED)

Postby ijulie » Sun Jan 13, 2008 7:15 pm

Thanks again
ijulie
 
Posts: 7
Joined: Wed Jan 09, 2008 4:30 pm


Return to Forms

Who is online

Users browsing this forum: No registered users and 3 guests