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(at)cpsgroup(dot)com]
Sent: Thursday, May 27, 1999 11:28 AM
To: JT Kirkpatrick; 'pgsql-sql(at)hub(dot)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