reciept numbers adding +1 to textbox

[ijulie]

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.

[DrewJensen]

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

CREATE SEQUENCE SEQ_INV_NUM

That starts at 0 and increments by way by defualt

Now enter this command

Code: Select all

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

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.

[ijulie]

Wow, Thanks alot you solved my problem.

[ijulie]

Thanks again