Forms with Sub-forms

This behaviour applies to forms that use linked Oracle ODBC data sources.

After filling out a new "master" form and proceeding to a field on a sub-form, the word #DELETED appears in all master form fields.

Explanation:

This behavior occurs under the following conditions:

1. The master form's data source is a linked Oracle table, or a QueryDef based on a linked table.

2. The underlying table implements Access Auto-Number functionality using Oracle sequences and triggers.

3. The field that is the recipient of the next sequence value is the master field in a master/child sub-form relationship.

Scriptoria generates Oracle sequences that are incremented by triggers that retrieve the associated sequence's NextVal value.  When MS-Access sends and insert command to the Oracle server, the sequence trigger fires,  but the generated NextVal is not visible to the MS-Access form. MS-Access attempts to retrieve the record it inserted, but cannot determine uniqueness. MS-Access therefore assumes the record was deleted by another user and fills all form fields with #deleted.

There are a number of ways to work-around this problem.

Workaround-1:

Retrieve the sequence's next value using VBA:

In order to implement MS-Access Auto Number functionality in Oracle, Scriptoria generates sequences, and triggers that retrieve the next sequence value into the associated Oracle column.

This solution requires that the generated trigger be disabled. The application must retrieve the sequence NextVal using VBA code.

The following example applies to the Orders form in the Northwind sample database after using Scriptoria's "Generate New Application" facility.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim dbOra As Database, qdOra As QueryDef, rsOra As Recordset

Set dbOra = CurrentDb

Set qdOra = dbOra.CreateQueryDef("")

qdOra.Connect = "ODBC;DSN=YourDataDource"

qdOra.SQL = "SELECT NORTHWIND.SEQ_ORDERS.NextVal FROM DUAL"

Set rsOra = qdOra.OpenRecordset

OrderID = rsOra(0)

End Sub

Workaround-2:

Create a new unique field:

This technique does not require that the sequence trigger be disabled.

Step-1:

Add a new 35 character text field with a unique index to the Access table.

Step-2:

Use an update query to set the new field values equal to the Auto Number field values.

Using the Northwind Orders table as an example:

UPDATE Orders SET Orders.OraTimestamp = [OrderID];

The following example applies to the Orders form in the Northwind sample database after using Scriptoria's "Generate New Application" facility.

Private Sub Form_BeforeInsert(Cancel As Integer)

Dim dbOra As Database, qdOra As QueryDef, rsOra As Recordset

Set dbOra = CurrentDb

Set qdOra = dbOra.CreateQueryDef("")

qdOra.Connect = "ODBC;DSN=YourDataDource"

qdOra.SQL = "SELECT TO_CHAR(SYSTIMESTAMP) FROM DUAL"

Set rsOra = qdOra.OpenRecordset

OraTimestamp = rsOra(0)

End Sub

Work-around-3:

Using Access commands only:

The field in the underlying table or query that uses the sequence must contain an Order By clause (Sorted).

Place a facsimile to the following BASIC code In the master form's After Insert Event Procedure:

Private Sub Form_AfterInsert()

Me.Requery

Me.Recordset.MoveLast

End Sub

---------