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...

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