RE: [SQL] select nextval. . .

ah hah!  all close and control features are disabled -- only a "next 
screen" button will get you out.  this process i am describing is for 
adding a new service record to the system.  each successive form only 
allows you to get out with "next screen" button.  certain fields in the 
first form cannot be left null, so if you "next screen" you'll get a slap 
on the wrist from postgres and can't move forward until you populate those 
fields.  i suppose if you entered the "create new service call" process by 
mistake you can type junk in the notnull fields (which are identified on 
the form), keep hitting next screen (only two more of them), and the final 
screen will detect zero$$$ (assuming you left the prices empty!) and will 
allow you to click a button to CANCEL the new service record.  not exactly 
suave programming, but we are in a crunch and i am taxing my abilities 
anyway!  even if the user filled in junk in the beginning and gave the 
record a dollar value, it'd be caught the next day in the daily procedures 
of the business. . .  it'd look awfully suspicious if we were trying to 
repair a computer for asldkjfasldfjasdfj!

jt

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

What happens if I go to the screen and decide not to input anything.  Just 
a
thought.-DEJ

> -----Original Message-----
> From:    JT Kirkpatrick [SMTP:jt-kirkpatrick@mpsllc.com]
> Sent:    Thursday, May 27, 1999 10:43 AM
> To:    'Jackson, DeJuan'; 'pgsql-sql@hub.org'
> Subject:    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