RE: [SQL] select nextval. . .

well, i did just that!  i set up a form with a single textbox.  i set the 
form's recordsource to a pass-through query defined as "select 
nextval('sequencename') from sequencename" --  the "from sequencename" 
isn't necessary in psql, but msaccess requires a "from" statement in all 
selects.  the returnrecords property of the query must be set to yes.  it 
named the returning field "nextval" by itself.  so, in the single field on 
the form, i set the controlsource to "nextval".  now when you open the 
form, it selects the next value and presents it to you!  fairly cool!  i 
really needed to assign that value to a defined field in other tables.  so 
what i do in VBA, in the on-open event of any form that contains the 
defined table field which i will assign the next sequence value to, is open 
the nextval form acHidden, capture the value of the "nextval" control into 
a variable, close the nextval form, then assign me!fieldname = 
nextval_variable_name.  works great!  kind of like using duct tape to fix 
things -- but can't we all appreciate the number of times duct tape has 
saved us???

jt

-----Original Message-----
From:    Jackson, DeJuan [SMTP:djackson@cpsgroup.com]
Sent:    Thursday, May 27, 1999 11:28 AM
To:    JT Kirkpatrick; 'pgsql-sql@hub.org'
Subject:    RE: [SQL] select nextval. . .

> i am connecting to a postgres 6.4.2 database with msaccess97.  in that
> postgres database i have a sequence defined.  in access i have a form,
> with
> only one textbox -- when i open that form i'd like to populate that
> textbox
> with the next value in that sequence.  i can get the next value from psql 
> (select nextval('sequencename');), but i can't seem to figure out how to
> get that nextval from within access97 into a control on a form.  i can
> design a pass-through query that inserts the nextval into a database, but 
> that isn't what i want to do (it also exposes some risk of non-uniqueness 
> that the sequence helps to ensure).  got any ideas??
>
> jt
>
You're going to have to implement this all on your own.  As far as I know
Access doesn't have a way to start a transaction with a form, therefore
sequences lose a lot of their benefits.

A viable alternative might be to grab the generated sequence number after
the insert, and display it then.-DEJ