Received: from bjke.com (firewall-user@ganymede.bjke.com [144.210.8.38]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA16893 for ; Thu, 27 May 1999 12:00:13 -0400 (EDT) (envelope-from djackson@cpsgroup.com) Received: by bjke.com; id LAA26340; Thu, 27 May 1999 11:00:12 -0500 (CDT) Received: from unknown(144.210.12.10) by gauntlet.bjke.com via smap (4.1) id xma026161; Thu, 27 May 99 10:59:33 -0500 Received: by cpsmail with Internet Mail Service (5.5.2448.0) id ; Thu, 27 May 1999 10:59:48 -0500 Message-ID: From: "Jackson, DeJuan" To: JT Kirkpatrick , "Jackson, DeJuan" , "'pgsql-sql@hub.org'" Subject: RE: [SQL] select nextval. . . Date: Thu, 27 May 1999 10:59:47 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain 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