Reiro wrote:
> ok ... im using the foll: code....
>
>
> Function AssignNextval() As Long
>
> Dim db As Database
> Dim LPassThrough As QueryDef
> Dim Lrs As dao.Recordset
> Dim LSQL As String
>
> On Error GoTo Err_Execute
>
> Set db = CurrentDb()
>
> 'Create a temporary passthrough query to retrieve the NextVal from
> an Oracle sequence
> Set LPassThrough = db.CreateQueryDef("qryTemp")
>
> 'Use {Microsoft ODBC for Oracle} ODBC connection
> LPassThrough.Connect =
> "ODBC;DSN=cssmam_main;UID=mam4;PWD=mam4;SERVER =jhb-cssmamapp"
> LPassThrough.SQL = "Select access_asset.nextval as NV From Dual"
> LPassThrough.ReturnsRecords = True
>
> Set Lrs = LPassThrough.OpenRecordset(dbOpenSnapshot)
>
> 'Retrieve NextVal from Oracle sequence
> If Lrs.EOF = False Then
> AssignNextval = Lrs("NV")
> Else
> AssignNextval = 0
> End If
>
> 'Remove query definition when done
> CurrentDb.QueryDefs.Delete "qryTemp"
>
> Exit Function
>
> Err_Execute:
>
> 'Remove query definition when done
> CurrentDb.QueryDefs.Delete "qryTemp"
>
> 'Return 0 if an error occurred
> AssignNextval = 0
>
> End Function
>
>
> nw im tryin to assign this value to a textbox on my access form
>
> text12 = AssignNextval ()..... says function not allowed ......
>
> and if go to the textbox > properties > data > default value =
> AssignNextval () .. on form load i get #NAME?
>
>
> how can i know assign this value to a text box...

Don't do this in whatever front-end product you are using .. do
this in the database and if you need to know the value use the
INSERT statement's RETURNING clause.

Demos in Morgan's Library at www.psoug.org under SEQUENCE and
INSERT.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org