pasting records in datasheet-form: pass-through called twice ?

[steph]

Hi all,

I have an Access2002 form in in datasheet view. Column "CMP" is locked
to user input, instead it is filled automatically by this function:

===
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Form_BeforeInsert_Err
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("q_compoundname", dbOpenDynaset)
rst.MoveFirst
CMP = rst![compoundname]
rst.Close

Form_BeforeInsert_Exit:
Exit Sub

Form_BeforeInsert_Err:
MsgBox Error$
Resume Form_BeforeInsert_Exit

End Sub
===

"q_compoundname " is a pass-through query to an oracle database, that
returns a value based on an oracle sequence like 'A0001', 'A0002',
'A0003', etc.

When inserting records manually, this works fine.

Nevertheless, there's a problem when pasting records from excel: then
the counter is increased by 2 with any row like 'A0004', 'A0006',
'A0008', etc. To me this indicates that the pass-throug query is called
twice per row.

It's vital that the numbers are increased only by 1, also if rows are
inserted from excel, which happens quite often.

Why does it make a difference, when rows are copied from excel? Is
there anything I can do to change the current behaviour of my form?

Thanks for any input,
Stephan

[Steve Jorgensen]

On 20 Dec 2005 08:20:12 -0800, "steph" <st*******@gmx.net> wrote:

Hi all,

I have an Access2002 form in in datasheet view. Column "CMP" is locked
to user input, instead it is filled automatically by this function:

===
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Form_BeforeInsert_Err
Dim db As Database
Dim rst As Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset("q_compoundname", dbOpenDynaset)
rst.MoveFirst
CMP = rst![compoundname]
rst.Close

Form_BeforeInsert_Exit:
Exit Sub

Form_BeforeInsert_Err:
MsgBox Error$
Resume Form_BeforeInsert_Exit

End Sub
===

"q_compoundname " is a pass-through query to an oracle database, that
returns a value based on an oracle sequence like 'A0001', 'A0002',
'A0003', etc.

When inserting records manually, this works fine.

Nevertheless, there's a problem when pasting records from excel: then
the counter is increased by 2 with any row like 'A0004', 'A0006',
'A0008', etc. To me this indicates that the pass-throug query is called
twice per row.

It's vital that the numbers are increased only by 1, also if rows are
inserted from excel, which happens quite often.

Why does it make a difference, when rows are copied from excel? Is
there anything I can do to change the current behaviour of my form?

Thanks for any input,
Stephan

I don't know why for sure, but any code that depends on how many times an
Access event should fire is generally unreliable. You could try an Oracle
trigger instead of an Access event, but the real "right" way to do this is to
have data entry batch tables, and generate the sequence numbers when the batch
is posted to the main table.

[steph]

Hi Steve,

Could you describe to me the scenario with batch tables in more detail?
How would you post to the main table - via an append-query on the batch
table?

Thanks,
Stephan