how to get oracle sequence value at form

[newaccess]

Hi Friends,



I have a form that used to input data. there are a column as primary key at table and value comes from oracle sequences.

the query works in database. However I got a message as run-time error 3078. the Microsoft access database engine cannot fine the input table or query'DUAL'. make sure it exists and that its name is spelled correctly. Click debug. cursor focus on
Set rs1 = CurrentDb.OpenRecordset(strsql)

Thanks for help. I use access97 in window 7.

newaccess
-------org code
Private Sub Form_Open(Cancel As Integer)
Dim dbExercise As DAO.Database
Dim strid As Long
Dim strsql As String
Dim rs1 As DAO.Recordset
Set dbExercise = CurrentDb

strsql2 = "SELECT req_seq.NEXTVAL AS MaxInv FROM DUAL;"
Set rs1 = CurrentDb.OpenRecordset(strsql)
If rs1.BOF = False And rs1.EOF = False Then

[June7]

You want to select next value of field req_seq from table DUAL? Never seen a sql like this in Access.

Google: oracle nextval microsoft access

Review http://www.techonthenet.com/access/q...ssthrough2.php

[newaccess]

Super Moderator ,
Thanks for your information and works well!

New access man

[newaccess]

Super Moderator,

I know we need to manual generated a form by test to add data and assign value to a field. we MUST do this way?
I auto created a form based on a table by access that save lots of time during manual generated form .
Do we have any way to assign this value to a form field?
I try to assign id value as beow
Private Sub Form_Open(Cancel As Integer)
Dim dbExercise As DAO.Database
Dim strid As Long
Dim strsql As String
Dim rs1 As DAO.Recordset
Set dbExercise = CurrentDb
Dim Msg, Style, Title, Help, Ctxt, Response, MyString


MsgBox AssignNextVal()

Me.CNN.Caption = AssignNextVal()

With rs1
rs1("REQ_ID").Value = AssignNextVal()
End With

---
thanks
new access

[June7]

"I know we need to manual generated a form by test to add data and assign value to a field. we MUST do this way?" I don't really understand question.

What is wrong with the code - error message, wrong results, nothing happens?

Try the form Current event instead of Open.

I don't know much about working with Oracle database. I have only set links to Oracle tables, for read only, no write.

If the form is bound to table then don't see need for recordset, just populate the field by reference to the form:

If IsNull(Me.Req_ID) Then Me.Req_ID = AssignNextVal()

Why setting the caption property of a label?

[newaccess]

It works. Great help! Super moderator!

Happy New year