pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: JT Kirkpatrick <jt-kirkpatrick@mpsllc.com>
To: 'Jackson, DeJuan' <djackson@cpsgroup.com>
To: 'pgsql-sql@hub.org' <pgsql-sql@hub.org>
Subject: RE: [SQL] select nextval. . .
Date: Thu, 27 May 1999 11:43:15 -0400
Message-ID: <01BEA836.1C553880.jt-kirkpatrick@mpsllc.com> (raw)

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




view thread (5+ messages)  latest in thread

Message-ID: <01BEA836.1C553880.jt-kirkpatrick@mpsllc.com>
Permalink:  ../01BEA836.1C553880.jt-kirkpatrick@mpsllc.com/
Also on:    postgresql.org/message-id/01BEA836.1C553880.jt-kirkpatrick@mpsllc.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: jt-kirkpatrick@mpsllc.com, djackson@cpsgroup.com, pgsql-sql@hub.org
  Subject: Re: [SQL] select nextval. . .
  In-Reply-To: <01BEA836.1C553880.jt-kirkpatrick@mpsllc.com>
  References: <01BEA836.1C553880.jt-kirkpatrick@mpsllc.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox